Any way to update VARBINARY value on certain position? for example, if i have string '012345678', and i need to update position number 4 into another byte/character 'x' for example:
INSERT INTO foo(pk,pos) VALUES('abcdefghi',5)
ON DUPLICATE KEY UPDATE
  pk = CONCAT(
     SUBSTRING(pk,1,VALUES(pos)-1),
     SUBSTRING(VALUES(pk),VALUES(pos),1),
     SUBSTRING(pk,VALUES(pos)+1)
  );

-- simpler example:
SELECT CONCAT(SUBSTRING('abcdefghi',1,5-1),SUBSTRING('123456789',5,1),SUBSTRING('abcdefghi',5+1));
-- abcd5fghi

how to do this in VARBINARY data type?

You can use the INSERT function:
INSERT(String, StartingPosition, CharsToDelete, 'StringToInsert')

So if you wanted to replace one character at position 4 in the string abcdefghi, the expression would go like this:
INSERT('abcdefghi', 4, 1, 'x')

See for yourself in this demo.
The expression would only return a modified string. Use it in an assignment to actually write the result, where necessary.


How to change ANSI_NULLS option for a table? I have a table that mistakenly has ANSI_NULLS option set to OFF. I'd like to set it ON for this table. The table has millions rows of data. Is it possible to change this option without creating a new table with ANSI_NULLS ON, moving all the data into this new table and renaming this table. Currently downtime for using this solution makes it impractical. Is there another way?

As far as my quick and dirty tests show (using ALTER TABLE and ALTER INDEX), no

The setting is captured at CREATE time.


SQL Server: Frequent Login timeout expired in Linked server I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local server. Linked server has been established successfully. The query used in Stored procedure looks like below.

SELECT ID,Col1,Col2,Col3,Col4,Col5 into #temp from [111.111.111.111].ProdServer.dbo.SourceTable

Insert into Localtable(ID,Col1,Col2,Col3,Col4,Col5)
SELECT ID,Col1,Col2,Col3,Col4,Col5
FROM #temp where ID NOT IN (select ID from Localtable)

The job execution sometimes takes 25-30 secs which will successfully executed. Sometimes it takes 48-50 seconds which results in failure. Please refer failure message below and other details in above screenshot.

Failure Message:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 53)  OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.

More Details

The table is locked by nobody and no queries run on it.
In Linked Server Properties, both Query Timeout and Connection Timeout
has been set to 0.
In Server properties remote query Timeout is also set to 600 Secs.

As last option I tried to change Connection Timeout Settings from its default value 15 to 6000 or higher. I could not achieve it. Please suggest solution to execute my scheduled jobs successfully even it takes 48 minutes or slight more.

Right Click on server > Click on properties > Click view connection properties on left pane under connection - to see Connection Timeout Property

1 new observation:
The job is failing if it takes 48 seconds or more to run.

I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query

USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

The setting takes effect immediately without restarting the server.

Please refer Configure the remote query timeout Server Configuration Option and sp_configure.


SQL Server 2012 Licensing on a VMware Infrastructure I am looking to license SQL Server 2012 Standard on my VMware Vsphere server farm. I currently have 3 hosts in the farm with the following spec:

2x 6core Xeon CPU (12 cores total)
128GB RAM

According to the documentation released by Microsoft I would need to license each core on the host. This documentation does not clearly explain if I need to license every core on every host on my server farm. It also appears I will need SA to allow me to vMotion the servers across the farm.

Can someone please clarify how many core licenses I will need to correctly license my farm? And confirm that I also need SA to use vMotion.

Thanks

Excerpts from  Microsoft Licensing Guide

How to License Individual Virtual Machines Using the Per Core Licensing Model
*Similar to the Per Core licensing model in physical OSEs, all virtual cores (v-cores) supporting virtual OSEs that
are running instances of SQL Server 2012 software must be licensed accordingly.
To license individual VMs using the Per Core model, customers must purchase a core license for each v-core (or virtual processor, virtual CPU, virtual thread) allocated to the VM, subject to a four core license minimum
per VM. For licensing purposes, a v-core maps to a hardware thread. When licensing individual VMs, core factors do not apply.
Note: Licensing individual VMs is the only licensing option available for SQL Server 2012 Standard Edition
customers who are running the software in a virtualized environment under the Per Core model.*

Licensing is tricky, I would encourage you contact Microsoft to get true answer

You should call 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist, and you can get more detail information from them. Outside North Ameriac you can use the Guide to Worldwide Microsoft Licensing Sites to find contact information in your regional MS location.


Access 2007 Work in a separate file using ACCDE Have been working in an Access 2007 application for over 3 weeks and it should be ready now. Anyway, I know (and tried) that "Make ACCDE" can actually split the raw data from the application itself so you can run it using the Access RunTime.

The question is... can I deploy my app for general use and modify a separate copy of the forms/views inside the application to deploy it when it´s ready without touching the data?

Yes, you can (and you should!!) split your application into two Access databases:

A backend containing all the tables.
Put this somewhere on a central network share.
A frontend containing code, forms, records and so on...basically everything except the actual tables (which are in the backend).
You can link the tables from the backend here, and each user should have his own copy of the frontend on his local machine.
It's quite easy to deploy the frontend in a way that it auto-updates each time a user runs it.

If you split your app this way, you can just deploy it now and your users can start using it.
Later, you can modify the forms and reports (or add new ones) and just deploy a new version of the frontend to your users, without them even really noticing it.


Fastest way to add new column in MySQL What is the fastest way to add a new column to an existing table in MySQL?

I have tried ALTER TABLE tbl ADD COLUMN ..., but that takes quite a while since there are many rows out there.

That is the fastest way in terms of getting the job done. There is no way around it directly as you have to rearrange the table structure for the new column.

If you need to keep read access to the data while the change is made but can live without write access during that time you could:

Create a fresh table with the extra column
Lock the table from updates (set all users to not have the needed permissions for instance)
Copy the data from old to new
Rename the old table to something else
Rename the new table to the old name
Drop the old table

There will still be no access at all between steps 4 and 5, and the whole process will take far longer, and of course you don't have insert/update ability during the process, but the complete outage (including reads) is only steps 4 and 5 not the whole process. As noted by ypercube in the comments, if the affected table has any foreign keys directed toward it these will need modifying and that will cause locks on the affected tables, also when creating the table (or before renaming it) make sure you include any indexes and constraints. Also any triggers operating on the old table will need to be added too (after the data has been copied).

A hacky workaround would be to put the new column(s) in a new table and just join that in to any query that needs them negating the need to modify exiting structures at all, but this means you have to be careful to update both tables correctly when needed and the LEFT OUTER JOIN to pick up the extra values is both more work for you (and other developers on the project now and in future) and more work that the query planner/runner have to do in order to process your queries, so there are going to be performance differences.

It should go without saying: if you intend to try any of the above, as with any structural change (in fact any change!), do so in a testing environment first before touching production databases.

tl;dr: No, there isn't really a quicker way.


what better way to multiple ON conditions I've got a table called Customers. And a table Users. (MySQL 5.3)

Customers:

customerID|customerName|accountManagerID|attendantID
123       |Google      |456             |789
124       |Apple       |457             |890

Users:

userID|userName
456   |Peter Norton
457   |Paul Allen
789   |Bill Gates
890   |Steve Jobs

I would like to be able to get the following result:

ID |Customer|Manager     |Attendant
123|Google  |Peter Norton|Bill Gates

I can get by using the following query:

SELECT customerID AS 'ID'
    ,customerName AS 'Customer'
    ,u1.accountManagerID AS 'Manager'
    ,u2.attendantID AS 'Attendant'
FROM Customers c
LEFT JOIN Users u1 ON (u1.customerID = c.customerID)
LEFT JOIN Users u2 ON (u2.customerID = c.customerID)

But I do think horrible do it this way, moreover, I consider 'little performatic'. Can someone point me to the right direction? Thanks!

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing horrible or "unperformatic" about it.

Things that need addressing:

Syntax issues:

use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
there is noLEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have  columns from multiple tables with same name, it helps avoiding them.
don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

Logic issues:

use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

SELECT c.customerID        AS ID
     , c.customerName      AS Customer
     , u1.accountManagerID AS Manager
     , u2.attendantID      AS Attendant
FROM Customers c
    LEFT JOIN Users u1 ON u1.userID = c.accountManagerID
    LEFT JOIN Users u2 ON u2.userID = c.attendantID ;


Replacing a PHP loop with a MySQL Query I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site.

The table structure is:

CREATE TABLE `user_hashes` (
  `user_id` int(11) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `hash_datetime` datetime NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The purpose of this is to allow me to track which user accounts are used by the same person, or at least from the same computer.

I need to write a query that accepts a user_id and finds all other user_id's with matching hashes.

So far my query is as follows:

SELECT DISTINCT users.*
FROM user_hashes AS a
LEFT JOIN user_hashes AS b ON a.hash = b.hash
LEFT JOIN users on b.user_id = users.id
WHERE a.user_id = ?
ORDER BY users.id

This works for all hashes matched to the user_id in the where clause but it is possible to also match against hashes related to the matched user_id's?

I could write a loop in PHP that keep running queries against the list of matched users until there are no more unique matches left but is there a nice SQL way to achieve the same result?

Sure, just use a subquery

select distinct u.*
from
  user_hashes uh
  inner join users u on uh.user_id = u.id
WHERE
  uh.hash IN
  (
    select hash
    from user_hashes buh
    where user_id = ?
  )


How to install two mysql instance on same machine I want to run two MySQL instances on the same machine. The reason behind this is, I will be needing two databases with entirely different configurations.

The specifics of the configuration of the both the DB are as follows,

Fault tolerant, high-intensity IOPS, RAID 10.
Highly available, Low-intensity IOPS, RAID 1.
This two instance will have separate HA policies.

Currently, I am only installing once and running the first instance using systemdctl whereas I am running the second instance manually using separate cnf files.

So now, how can I install and run two separate instances of MySQL on same machine?

DO I need to create separate instances of the MySQL-server binaries for both instances? And do I need to install MySQL-server RPM for a second time into an alternate folder?

If yes, then how? I also want to understand the implication of using the single binary for running two instances. Does it affect the other instance in any way during recovery process or any other db intensive task?

My mysql version would be 5.6.34

I have gone through this link(https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-multiple-mysql-instances), for installation of multiple instances on single machine but this support is enabled from 5.7.7 version whereas I am using 5.6.34.

You must have separate data trees and my.cnf files and Ports.  Your client(s) must pick the instance via the Port number.  That's about all you need to know.

You may use different binaries.

You may use VMs or Docker or ...  These may confuse the issues of Ports, binaries, etc, since they chop up your machine is ways that sort of give you separate logical machines.

There are several questions on this forum relating to this, and they discuss details for what to do before 5.7.6.


Using the value of a JSON object that is stored in a PostgreSQL JSON array I have a table that looks like this (simplified):

Jobs
-------------------------------
ID | Details
-------------------------------
1  | { 'tasks': [] }
2  | { 'tasks': [{'id': '1', 'due_on': '2015-06-8'}, {'id': '2', 'due_on': '2015-06-09'}] }
3  | { 'tasks': [{'id': '3','due_on': '2015-06-8'}] }

I am trying to query for two different things, both based on the due_on date in the subtasks:

The job id of all jobs that have a task due on a specific date
The tasks that are due on a specific date along with their job id

With a normalized structure these would obviously be easy queries. In this case, the data is structured as a JSON object for a very specific reason that's out of this question's scope. Let's just go with it.

I have been experimenting with the JSON functions and the array functions but I just can't seem to get the query right.

For example, SELECT * FROM Jobs WHERE Details->'tasks'->>'due_on' = '2015-06-08' just throws a cannot extract field from a non-object error (for pretty clear reasons).

Alternatively, I could store the data as a tasks object instead of an array. If the above would be much easier / faster / better then I'll refactor.

Any help would be much appreciated. Thank you.

You can use json_array_elements to turn a JSON array into a rowset:

SELECT  "ID" as JobId
,       t->>'id' as TaskId
FROM    Table1 j
CROSS JOIN
        json_array_elements("Details"->'tasks') t
WHERE   cast(t->>'due_on' as date) = '2015-06-08';

The cast(... as date) works around the 2015-06-8 date which misses a leading 0 before the day.  If your format is always YYYYMMDD, you can omit the cast.


Mysql: order by field or select union? As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause

select * from test where id in (3,1,2)

Unfortunatelly for me, that's not true. The results comes in id asc. order.

I have read that the easiest way to get what I want is using order by field

select * from test where id in (3,1,2) order by field(id, 3,1,2)

But I also read that is not ver efficient for queries with lots of ids in the sorting.

So, Do you think that this query below would performance better?

select * from test where id=3 union select * from test where id=1 union select * from test where id=2

If you need a specific order, you should specify it with the ORDER BY clause.

The most efficient way to perform an order-by is to ORDER BY somefield.

If you need those records sorted as 3,1,2, then perhaps you should add a field sortorder that is ordered the way you want so you can simply do ORDER BY sortorder.


Is there a recommended order to create a DV model and star schema? I want to create and implement a Data Vault Model. In the presentation layer, I want to create a View based on a star schema. Now I would like to know if there is a certain order in creating the models in practice?
(Is is a commonplace to create first a Data Vault model and then the star schema, or vice versa?) I noticed that I think very "dimensional" when start to make a concept of the requirements.

Data Vault is an architecture and methodology to store data historically, sometimes holding hundreds of entities which can, but don't need to be interconnected.

Star Schema is an architecture to provide fast querying performance for specific questions, usually holding a few entities which are interconnected.

Without getting any more specific in both methods, I think you might have misunderstood Data Vault. It might be wise to inform yourself more about Data Vault, especially where it might lie in a typical Data Warehouse. Since this is not concerning the original question, I will not go into detail.

I have seen it like this:

Data Vault is being used as a core storage system. All the data at all the time. No transformation is being made.
Star Schema is build upon Data Vault Raw / Business Vault. Can be views, can be persistent tables. All calculations / transformations will be done here.


Unable to start DB console service I'm using Oracle 11g database in a Windows environment. I've installed Oracle database software only, but now I'm in need of enterprise manager. Initially I tried to start them using the below commands.

C:\>set ORACLE_UNQNAME=testdb

C:\>emctl status dbconsole

This didn't work as my service for DB console was not up. Then, I tried starting the service which showed up the following error.

Can anyone please help me in resolving this error?

Make sure you're running the CMD window "As Administrator".
You will also need to set the ORACLE_SID variable.

 set ORACLE_SID=MyOracleSID
 emctl start dbconsole

It should provide a more detailed error message at this point, if it does fail.


Single Identity column and composite key, which to make primary? I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index.  However I need a single column unique identifier for my framework to work with the table. Both keys are completely unique, and I'll need to make a clustered, unique index on the original, composite key.

Should I keep the composite key as a clustered primary key, or should I use the single column identifier as a non-clustered primary key? Though my framework requires a single identifying column I don't actually have to specify it as the primary key in the DB layer, so I'm not sure which is preferable.

I'm using SQL Server 2005 and the single column identifier will be an IDENTITY.

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.


Is it safe to use pt-online-schema-change in a multimaster environment? I have 2 MySQL servers with row-based replication between them. Both of them are masters and slaves for each other (active-active master-master setup).

If I understand it correctly pt-osc creates triggers to catch any changes while running. But from what I know triggers are not fired in a row-based replication environment. So I guess pt-osc is not able to catch changes made on the second master during the change, is it?

EDIT: While doing some tests and I saw that pt-osc was creating triggers on both masters which would cover changes from both sides. Still I'm quite unsure if I can safely do online changes in this environment.

The logic is more complex.  (Caveat:  This is not a definitive Answer to the Question, just more insight.)

Generally (probably in this case), the triggers should be only on the one machine.
RBR replicates the effect of inserts/etc, not the actual statements.  That is what is needed on the Slave (aka other Master).
Are you 'writing' to both Masters?  If so, that is a recipe for extra problems; I recommend you stop writing to the other Master while running pt-osc.

Also look into gh-ost, which uses the binlog in tricky ways.  (Obviously, you have the binlog turned on.)


Changing a column datatype from INT to BIGINT I have a large, partitioned table with a field in that needs to upgraded from INT to BIGINT.

My intention is to detach each partition, make the change and then re-attach, using something like this:

ALTER TABLE testTable DETACH PARTITION testTable_201908; -- Should be very quick
ALTER TABLE testTable_201908 ALTER COLUMN recordId TYPE bigint;  -- likely to take ages!
ALTER TABLE testTable_201908 ADD CONSTRAINT con_201908 CHECK ( loadedTime >= DATE '2019-08-01' AND loadedTime < DATE '2019-08-01' ); -- Likely to take ages.
ALTER TABLE testTable ATTACH PARTITION testTable_201908 FOR VALUES FROM ('2019-08-01') TO ('2008-09-01' ); -- Should be quick with the constraint in place.
ALTER TABLE testTable_201908 DROP CONSTRAINT con_201908; -- Should be quick

The table is really big (about 100 millions records per partition), dozens of partitions. I've done some testing, DETACH PARTITION is really quick, ALTER COLUMN recordId TYPE bigint takes ages (but I don't think there's anything I can do about this)

Then I have a choice, if I just re-attach the partition to the parent table (without a constraint) then that takes ages whilst the database checks integrity (at also seems to lock-up the parent table for the duration). OR, I can add a constraint (takes hours) and then re-attach to the parent (takes seconds only)

Is there a way I can force the re-attachment of the partition without checking integrity and without requiring a constraint? After-all, I know the partition was valid before I detached it, and I know I haven't altered any of the relevant data?

You have to change all partitions at once, because the column data type of the partitioned table has to be the same as the column data type in the partitions.

One option is to add a new bigint column, fill it with values from the old column and then drop the old column.

This will take a while, but requires only short ACCESS EXCLUSIVE locks on the table.


Knowing if Oracle account has been locked? We have an Oracle that locks up randomly; we get error ORA-28000: the account is locked.

Is there some log that will tell me if an oracle account was locked throughout the day? Right now the Oracle account works fine.

I don't have admin rights in this database. We have Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

Thanks.

If you don't have admin rights you probably won't be able to troubleshoot the issue without your DBAs.
Some hints for troubleshooting (again, with your DBAs)

If the user is locked after some time (and not multiple times a day) the PASSWORD_LIFE_TIME in the (default) profile for your user might be the culprit.
If the user is locked randomly and you don't have something fancy like auditing enabled, you can look at your listener.log and check if there are connections from hosts you  aren't aware of. Often some web service or workstation with old credentials caused user locking at my place.

If you don't have any access to the server's log files and/or the admin views this issue is hard to resolve. Team up with your DBAs


Server 2012 Linked Server From An Instance I have 3 SQL Server 2012 Instances, 2 on SiteA on the same server SiteAServer.  I have 1 on SiteB on server SiteBServer.

SiteADefaultInstance, SiteASecondInstance

SiteBDefaultInstance

SiteBDefaultInstance can create a linked server to
SiteADefaultInstance and SiteASecondInstance.
SiteADefaultInstance can create a linked server to
SiteBDefaultInstance.
SiteASecondInstance CANNOT create a linked server to
SiteBDefaultInstance.

I get the error

  "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON"

I am setting the security to be made using the current context.

I have ran setSPN for SiteAServer and SiteBServer.  You don't have to change the commands if you have more than one instance do you?
I am in the role sysadmin.

I have never experienced where it only worked one way before.  Any suggestions where to look?

Comments were very useful in leading to the solution.  After running SQL Server Kerberos Configuration Manager I saw that while the first was on 1433 for SiteADefaultInstance and the second instance of the database was listening on a dynamic port for SiteASecondInstance.

Went into SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SiteASecondInstance > TCP/IP and change IPALL TcpPort to a different port, say 9999.

SetSPN -s "MSSQLSvc/SiteAServer:9999" "user"

After this could create a linked server.


Query of multi-valued field I have 2 tables:

Contacts
Helpers

The Helper field (which is linked to the Helper table) in Contacts is a multi-valued field so more than one helper can be selected and displayed in a report.
Each Helper is linked to more than 1 Contact.
I'm trying to create a query selecting all contacts for one helper, and display all helpers for the resulting contacts.  I think I am off-base with multi-valued fields, but cannot find another solution.
It is in Access 2010.  Tables do not have multi-value fields.  They are:
Contacts
ContactID
Surname
Email

Helper
HelperID
Name

This is a typical situation in which you have to create three tables: Helpers, Contacts, and Help_Contact, using the third table to represent associations between the elements of the first two, by storing the foreign keys for the first two, without any multivalued field in those.

That is, you should have something like:

Helpers(h_primary_key, other_fields_only_relevant_to_helpers)
Contacts(c_primary_key, other_fields_only_relevant_to_contacts)
Help_contact(h_primary_key,c_primary_key)

So, to display information for contacts or helpers associated to another element, you could use a join:

SELECT relevant_info
FROM Helpers h, Contacts c, Help_contact hc
WHERE hc.h_primary_key = h.h_primary_key AND hc.c_primary_key = c.c_primary_key AND other_conditions_on_helper_or_contact_or_both


"SELECT *" why is it an antipattern On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be looking over a detail that someone else with better understanding of the issue noticed.

So here is my question, why people say that select * is an antipattern.

The two reasons that I find the most compelling not to use SELECT * in SQL Server are

Memory Grants
Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the 'fullness' of the column. So for a VARCHAR 100, it's 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;

SELECT TOP 1000 DisplayName, u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

But the memory it nearly doubles.

DisplayName:

DisplayName, Location:

This gets much worse with SELECT *, asking for 8.2 GB of memory:

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users
    ON dbo.Users
(
    CreationDate ASC,
    Reputation ASC,
    Id ASC
 );

And I have this query, with a WHERE clause that matches the index, but doesn't cover/include all the columns the query is selecting...

SELECT u.*,
       p.Id AS [PostId]
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE  u.CreationDate > '20171001'
       AND u.Reputation > 100
       AND p.PostTypeId = 1
ORDER BY u.Id

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

CX:

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 4165 ms.

NC:

 SQL Server Execution Times:
   CPU time = 1623 ms,  elapsed time = 875 ms.


Can I get the union of the a Postgres fulltext index? I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible?

To clarify:

If I have the following table:

create table "test" (id integer, thing tsvector);

I then make a GIST index on it:

create index thing_index on test using gist (thing);

Then some data:

insert into test (id, thing)
values (1, 'one'),(2, 'two'), (3, 'three'), (4, 'one'), (5, 'two');

The index thing_index is going contain the following mapping:

'one' => {1, 4}
'two' => {2, 5}
'three' => {3}

I want to obtain the following response from the index:

'one',
'two',
'three'

Maybe even with rankings:

'one' => 2
'two' => 2
'three' => 1

I know I can do this myself by scanning and building my own index, but I want to get it out of Postgres if possible.

If I understand your question correctly and it isnt at all clear, you are trying to pull back information as far as what id's are connected with a value.  I don't think you can just pull it from the index in PostgreSQL because the index will not contain visibility info, and so you will have a LOT of random IO and waiting for platters to turn.

The query for your test case is:

select thing, array_agg(id) from test group by thing;

Assuming you are on a version high enough to have array_agg.

In my system (9.1) this gives me:

chris=> select thing, array_agg(id) from test group by thing;
   thing  | array_agg
 ---------+-----------
  'one'   | {1,4}
  'two'   | {2,5}
  'three' | {3}
 (3 rows)

that's what you are looking for, right?


How do you declare a set-returning-function to only be allowed in the FROM clause? I was just reading this answer by Erwin Brandstetter. Informative as usual, it makes note that (multi-parameter) unnest is only allowed in the FROM clause. I had never tried it in the SELECT clause so I'm not that shocked, but I never saw function that could control for execution context: FROM vs SELECT set-returning-functions.

Docs for unnest(anyarray, anyarray [, ...]) (the version with multiple parameters)

  expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause; see Section 7.2.1.4

How does one declare their set returning function to be FROM-clause only, or is this only permissible with unnest?

I am afraid that's only possible with unnest() after all.

unnest() with multiple parameters is a special Postgres feature, that's internally rewritten into multiple unnest() calls that are combined more reasonably than parallel unnest() in the SELECT list would be, i.e. in a sane way if the number of elements does not match.

The behavior was finally sanitized with Postgres 10:

What is the expected behaviour for multiple set-returning functions in select clause?

See:

EXPLAIN (VERBOSE) SELECT *
FROM   unnest('{a,b}'::text[], '{1,2}'::text[]);

Function Scan on unnest  (cost=0.01..1.00 rows=100 width=64)
  Output: unnest, unnest_1
  Function Call: unnest('{a,b}'::text[]), unnest('{1,2}'::text[])

It's a special use case for the ROWS FROM construct introduced with the same Postgres version 9.4. The manual:

  The special table function UNNEST may be called with any number of
  array parameters, and it returns a corresponding number of columns, as
  if UNNEST (Section 9.18) had been called on each parameter separately
  and combined using the ROWS FROM construct.

Bold emphasis mine.

If you try unnest() with multiple parameters in the SELECT list, you get:

  ERROR:  function unnest(text[], text[]) does not exist

In fact, there is no unnest() function with multiple parameters registered in the system:

SELECT proname, proargtypes, proargtypes[0]::regtype
FROM   pg_proc
WHERE  proname = 'unnest';

 proname | proargtypes | proargtypes
---------+-------------+-------------
 unnest  | 2277        | anyarray
 unnest  | 3614        | tsvector

That would be confusing if you were not aware of the first part of my answer.


Setting up binary replication between two PostgreSQL instances I have two virtual machines: dbrepa (-.175) and dbrepb (-.191).
Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone.
I need to have the databases on dbrepb be read-only copies of those on dbrepa.

And so, I set the appropriate settings of dbrepa/postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3 # Aside, I assume '3' is arbitrary

and dbrepa/pg_hba.conf (paying attention to spacing):

host   replication   all   192.168.108.191/32   trust

and dbrepb/postgresql.conf:

hot_standby = on

and dbrepb/recovery.conf (a new file):

standby_mode = 'on'
primary_conninfo = 'host=192.168.108.175'

Each database is empty, but the following command was run on both:

[root@dbrep-]# service postgresql-9.2 initdb

Now, since there are no databases, I don't need to worry if they are out of sync (since there is no data to be out of sync).
I start the master:

[root@dbrepa]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [  OK  ]

and I start the standby:

[root@dbrepb]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [FAILED]

Examining the log files of each:

dbrepa/data/pg_log/-.log is empty.

dbrepb/data/pg_log/-.log:

LOG:  entering standby mode
WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG:  startup process (PID 4320) exited with exit code 1
LOG:  aborting startup due to startup process failure

but when I double-check dbrepa/data/postgres.conf, wal_level is clearly set to hot_standby.

What else could be going wrong?

Your last two steps are in the wrong order.

The appropriate steps says

  _ 5. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connect to the
  primary server, that's OK.)
  _6. Start the master.

You are supposed to start the master after the standby.

Try reversing everything as follows:

[root@dbrepa]# service postgresql-9.2 stop
[root@dbrepb]# service postgresql-9.2 stop
rsync everything from Master (except the pg_xlog and config files (Your Step 4)) to Standby
[root@dbrepb]# service postgresql-9.2 start
[root@dbrepa]# service postgresql-9.2 start

Give it a Try !!!


Howto setup LogShipping properly I want to use logshipping to transfer large databases (400-700 GB size) from Server (and storage) A to Server (and storage) B.

I tried this several times and it worked. But I have questions about some details:

While using LogShipping, the only Log-Backups that can be taken are the backups that the logshipping process creates. So other backup software or jobs have to be disabled in advance.

In case I setup log shipping, the initial fullbackup that is required, independantly wether I take and restore it manually in advance of the logshipping setup or let the logshipping setup create and restore it for me, will take a while, at least one hour or more, to be finished, and additional time to be restored on the secondary. I have already tried both options with success for smaller dbs.

My question is: If I set up logshipping for a large db and need to stay able to do a point in time recovery, what will happen in the time between taking the initial full backup and the first log backup taken after the restore of the full backup has completed? Is there something like a gap in backup? Before the setup of log shipping is completed, I guess there is no backup configured at all, so will I lose thios period of time? Or will the transactionlog just keep growing until the full-backup-restore part has completed and the first log backup happens?

You can keep taking log backups on the primary server during full backup and restore to secondary. When you set up log shipping restore full with no recovery and all the log backups (taken during full backup restore) with no recovery.
When you are caught up you initialize log shipping.


Pentaho 4.4 Database connectivity issue I am getting the following in one of my transformations. I am trying to extract data from SQL server and pushing to mongodb.

2014/04/03 13:11:10 - EMPLOYEE TABLE Input.0 - linenr 700000
2014/04/03 13:11:49 - Database lookup.0 - linenr 700000
2014/04/03 13:12:28 - Select values.0 - linenr 700000
2014/04/03 13:14:26 - EMPLOYEE TABLE Input.0 - linenr 750000
2014/04/03 13:15:05 - Database lookup.0 - linenr 750000
2014/04/03 13:15:47 - Select values.0 - linenr 750000
2014/04/03 13:17:43 - EMPLOYEE TABLE Input.0 - linenr 800000
2014/04/03 13:18:21 - Database lookup.0 - linenr 800000
2014/04/03 13:18:59 - Select values.0 - linenr 800000
2014/04/03 13:20:55 - EMPLOYEE TABLE Input.0 - linenr 850000
2014/04/03 13:21:34 - Database lookup.0 - linenr 850000
2014/04/03 13:22:15 - Select values.0 - linenr 850000
2014/04/03 13:24:12 - EMPLOYEE TABLE Input.0 - linenr 900000
2014/04/03 13:24:51 - Database lookup.0 - linenr 900000
2014/04/03 13:25:30 - Select values.0 - linenr 900000
2014/04/03 13:27:31 - EMPLOYEE TABLE Input.0 - linenr 950000
2014/04/03 13:28:10 - Database lookup.0 - linenr 950000
2014/04/03 13:28:52 - Select values.0 - linenr 950000
2014/04/03 13:30:53 - EMPLOYEE TABLE Input.0 - linenr 1000000
2014/04/03 13:31:33 - Database lookup.0 - linenr 1000000
2014/04/03 13:32:11 - Select values.0 - linenr 1000000
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - Couldn't get row from result set
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - I/O Error: Connection reset
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 -
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 -

and at the transformation level am getting the following error

I am getting

  2014/04/03 13:33:43 - TASK COLLECTION - Invalid state, the Statement object is closed.  2014/04/03 13:33:43 - TASK COLLECTION -
  ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by
  buildguy) : Errors detected! 2014/04/03 13:33:43 - TASK COLLECTION -
  ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by
  buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
  2014/04/03 13:33:43 - TASK COLLECTION - Error cancelling statement
  2014/04/03 13:33:43 - TASK COLLECTION - Invalid state, the Statement
  object is closed. 2014/04/03 13:33:43 - TASK COLLECTION - 2014/04/03
  13:33:43 - TASK COLLECTION - at
  org.pentaho.di.core.database.Database.cancelStatement(Database.java:664)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.core.database.Database.cancelQuery(Database.java:646)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:303)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.Trans.stopAll(Trans.java:1729) 2014/04/03
  13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.step.BaseStep.stopAll(BaseStep.java:2605)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.step.RunThread.run(RunThread.java:95) 2014/04/03
  13:33:43 - TASK COLLECTION - at java.lang.Thread.run(Thread.java:722)
  2014/04/03 13:33:43 - TASK COLLECTION - Caused by:
  java.sql.SQLException: Invalid state, the Statement object is closed.
  2014/04/03 13:33:43 - TASK COLLECTION - at
  net.sourceforge.jtds.jdbc.JtdsStatement.checkOpen(JtdsStatement.java:220)

Now How should I proceed with this issue. I am very much new to these things. Any guidance would be much appreciated

Based on your timestamps, the 2nd section of errors are probably caused by your connection reset error above.

As for the connection reset error, it is probably being caused by some limit configured in pentaho or your database server. Is there anything helpful in your database logs?

If its not something obvious on the db logs... I haven't had too much experience with pentaho, can you provide the job that is failing in a text format? And just checking, how many rows are in your table?


does LIMIT clause mean constant memory/storage in a PostgreSQL query? When a LIMIT clause is used in a query with a very large number of records, e.g.

SELECT *
    FROM my_tab
    ORDER BY column1
    LIMIT 1 OFFSET 9

, does PostgreSQL always use a (roughly) constant amount of memory (and/or storage) during the query (1 row in the example)?

The documentation doesn't say much about the underlying mechanism or resource consumption.

Definitely not. With an order by clause, resource usage can vary between both extremes depending on whether or not a sort needs to be done or an index can be used.

Other factors play a major or minor role too:

querying from a base table, a view or a foreign table
width of the table
data types involved
size of the table / indexes involved
whether the table(s) are partitioned or not and whether partition elimination can be applied for the query
whether a serial or a parallel plan is used (which depends on the structure of the query, the indexes, the partitioning (or not), the DBMS version and possibly other factors)
resource consumption settings in postgresql.conf, like work_mem and replacement_sort_tuples (see Postgres docs: Resource Consumption for details.)


PostgreSQL stored procedure to return rows or empty set I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do

RETURN QUERY EXECUTE 'some dynamic query'

And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to return them, but if it returns only < 10 rows, I don't want to return anything (empty set of ct_custom_type).

I tried:

RETURN QUERY EXECUTE 'some dynamic query'

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable < 10 THEN

    # I don't know what to do here or how to accomplish this

END IF;

If I do RETURN QUERY SELECT 0, 0, ''::text; in the IF block (because ct_custom_type is a composite type of (integer, integer, text), it just adds this 'empty row' to previous query result, but I don't want to return anything in this case, I can do RETURN;, but it will return that previous result and I want to discard it.

I have it like this:

EXECUTE 'dynamic query';

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable >= 10 THEN
    RETURN QUERY EXECUTE 'dynamic query';
END IF;

and it works, but I didn't want to do this query two times.

You can do something along the following lines:

test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
$body$
BEGIN
    CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
    SELECT a
    FROM generate_series(1, cnt) t(a);

    IF (SELECT count(1) FROM tmp_container) > 5
    THEN
        RETURN QUERY SELECT a FROM tmp_container;
    END IF;
END;
$body$
LANGUAGE plpgsql;

test=> SELECT * FROM temptabl(4);
 temptabl
----------
(0 rows)

test=> SELECT * FROM temptabl(6);
 temptabl
----------
        1
        2
        3
        4
        5
        6
(6 rows)

This way you have to perform your original query only once.  All other statements work on the temporary table.


Finding the right pattern for creating indexes,redesign query structure I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables.

Because the logic of the initial query was to bring data based on some conditions, not all the joins were needed if a certain condition is satisfied.

For example : The query has 5 different conditions, each condition satisfies a certain scenario where only some table joins are required.

INITIAL QUERY EXAMPLE :

Select
  -- fields
From table1 t1
Join table 2 t2 ON t1.id = t2.id
...
Join table n tn+1 ON .....
Where
(t1.field1 = 1 and t1.field2 = 0) AND (
(t2.type = 1 and somefield = value and somefield2 = value ... somefield n+1 = value )
OR (t2.type = 2 and (somefield3 = value or somefield4=value or .... somefield n+1 = value) )
.......
OR (t2.type = n and (somefield n...n+1 = value)))

Note: The fields on which the conditions are written are never changed, the conditions are exactly as those written in the example above.

My thoughts are to redesign the structure of the query by splitting it into multiple queries joined together with UNION ALL. By doing this, I would get rid of the entire amount of joins for each condition.

I'm not sure if this could bring any real benefits mentioning the fact that in some cases when a certain condition is being satisfied , some of the table joins are on null values because the type of the conditions makes the query to bring a certain data which doesn't have the data the join needs.

The real questions would be :

Splitting the query into UNIONS would be better/faster design ?
Which is the pattern in the query for making an INDEX? If so, how should I create the index, which fields from the given example?

Without knowing anything about the data, if we assume your JOIN's are on related data, a UNION isn't what you want. The related data will be presented disjointed on separate result rows.
There are no hard-and-fast rules on index design; the right design is the one that increases performance on your queries without consuming excessive resources. As a rule of thumb, however, you want an index that contains your JOIN, WHERE, and ORDER BY columns. INCLUDE the columns you are SELECTing. But before you create the index, be sure that another index can't do the trick with an added column or two.

Start there, and see how performant the query is. Use SET STATISTICS TIME ON and SET STATISTICS IO ON and run with "Include Actual Execution Plan" enabled while you tinker. I actually like to do this before I create any indexes so I have a baseline to compare my "improved" query with.

Also, be sure that 'optional' tables are LEFT JOIN.


How to log logon logoff and user activity on mySQL server for audit? My boss wants to have some kind of log on the server running mysql which would log user activity.

Kind of like:

User blah logged in
User blah ran some query
User blah logedd off
User asdf logged on
..and so on

I did some research and found out about triggers:
How to log successful login attempts to a database?

Also, is that information logged into the mysql general log?

More info:

mysql is installed on a server running Redhat
Installed mysql version is: 5.0.91

I'm not a DBA, so please go easy on me!
Thanx in advance!

If you are willing to upgrade to  MySQL 5.5, there is a way to store the general log as a MyISAM table. I have written about this before in past articles:

Feb 24, 2011 : Audit logins on MySQL database
Jun 30, 2011 : How do I output MySQL logs to syslog?
Feb 11, 2012 : MySQL general log

Since you are using MySQL 5.0.91, you cannot configure the general log to be stored as a MyISAM table. You can only do so as a text file. Nevertheless, there have been other contributors in the DBA StackExchange that have suggested very good ideas for the text file version of the general log in your quest to audit DB Logins for MySQL 5.0. These suggestions may be more what you are looking for:

Aug 26, 2011 : How do I output MySQL logs to syslog? from @JoelHanger
Nov 16, 2011 : Log MySQL DB changing queries and users from @DTest


Used Memory not freed up after a SQL BULK Insert / BCP Export I have created a very basic SQL table as following

CREATE TABLE [dbo].[TickData](
[Date] [varchar](12) NULL,
[Time] [varchar](12) NOT NULL,
[Symbol] [varchar](12) NOT NULL,
[Side] [varchar](2) NOT NULL,
[Depth] [varchar](2) NOT NULL,
[Quote] [varchar](12) NOT NULL,
[Size] [varchar](18) NOT NULL
    ) ON [PRIMARY]

I then performed a 3 Gig Bulk Insert

    BULK
    INSERT TickData
    FROM
    'C:\SUMO.csv'
    GO

Then RAM usage for SQL server went Skyrocking, eating up ~30Go of RAM :

I prefer to think this is an abnormal behavior and that action can be taken to avoid this.

EDIT :
Ok, This seems to be the default behavior. Fair enough.
However, why isn't memory freed up long after the Bulk Insert is finished ?

A couple of extra-considerations :
As of the comments concerning SQL server freeing the memory when it is "told to" by the OS, my hands-on experience on a 24-Core 32 Gb Xeon Server proves this to be inexact : Once a Memory-Voracious BCP extract is over I have a pool of .Net Instances of my data processing application that need to process the extracted data, and they are left choking/fighting to share the remaining memory to try to perform their jobs, which take faaaaar longer that when SQL Server is turned off and memory is available for all applications to share. I have to stop the SQL Server Agent to make everything go smoothly and prevent Apps from crashing for Articiallt caused OutOfMemmroy Exception.
As to artificial Brutal Memory Capping/Limitation, if Free Memory is available, why not use it ? Ideally it would rather be dinamically set to adapt to what is available rather than just being forcibly limited "randomly". But I guess this is by-design, so case closed on this last point.

It is normal behaviour for SQL Server to allocate as much memory as it can into it's buffer pool. Databases work best with lots of buffer. If you want to change the behaviour, you can set the 'max server memory' setting. Some good background reading on that is here.


Viewing execution plans for queries with parameters I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.)
I would like to display its execution plan and attempt to tune it by indexing or rewriting some parts of it.
I cannot just copy/paste it into SSMS because there are numerous parameters of many data types. I don't even know what are the values for these parameters.

Is there a way to quickly identify the execution plan of this query? Maybe by querying some DMVs? I got the query text from monitoring software which must have done it.

SELECT 2                                 AS f1,
       T3.RECID                          AS f2,
       T4.RECID                          AS f3,
       T4.GENERALJOURNALACCOUNTENTRY     AS f4,
       T4.LEDGERDIMENSION                AS f5,
       Sum(T6.TRANSACTIONCURRENCYAMOUNT) AS f6,
       T6.TRANSACTIONCURRENCY            AS f7,
       T6.MONETARYAMOUNT                 AS f8,
       Sum(T7.ACCOUNTINGCURRENCYAMOUNT)  AS f9,
       N'aaa'                            AS DATAAREAID,
       1                                 AS RECVERSION,
       5637144576                        AS PARTITION,
       IDENTITY(bigint, 1, 1)            AS RECID
INTO   [##ax_tmp_tim99_151_7623]
FROM   SUBLEDGERJOURNALENTRY T1
       CROSS JOIN ACCOUNTINGDISTRIBUTION T2
       CROSS JOIN TAXTRANS T3
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T4
       CROSS JOIN ACCOUNTINGDISTRIBUTION T5
       CROSS JOIN ACCOUNTINGDISTRIBUTION T6
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRYDISTRIBUTION T7
WHERE  ( ( T1.PARTITION = @P1 )
         AND ( ( ( ( T1.TRANSFERID = @P2 )
                   AND ( T1.LEDGER = @P3 ) )
                 AND ( T1.TYPE <> @P4 ) )
               AND ( T1.TYPE <> @P5 ) ) )
       AND ( ( T2.PARTITION = @P6 )
             AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )
                   AND ( ( ( T2.MONETARYAMOUNT <> @P7 )
                           AND ( T2.MONETARYAMOUNT <> @P8 ) )
                         AND ( T2.MONETARYAMOUNT <> @P9 ) ) ) )
       AND ( ( ( T3.PARTITION = @P10 )
               AND ( T3.DATAAREAID = @P11 ) )
             AND ( T3.SOURCEDOCUMENTLINE = T2.SOURCEDOCUMENTLINE ) )
       AND ( ( T4.PARTITION = @P12 )
             AND ( ( ( ( T4.SUBLEDGERJOURNALENTRY = T1.RECID )
                       AND ( T4.POSTINGTYPE <> @P13 ) )
                     AND ( T4.POSTINGTYPE <> @P14 ) )
                   AND ( T4.POSTINGTYPE <> @P15 ) ) )
       AND ( ( T5.PARTITION = @P16 )
             AND ( T5.RECID = T2.PARENTDISTRIBUTION ) )
       AND ( ( T6.PARTITION = @P17 )
             AND ( ( ( T6.SOURCEDOCUMENTLINE = T5.SOURCEDOCUMENTLINE )
                     AND ( T6.PARENTDISTRIBUTION = T5.RECID ) )
                   AND ( ( ( T6.MONETARYAMOUNT = @P18 )
                            OR ( T6.MONETARYAMOUNT = @P19 ) )
                          OR ( T6.MONETARYAMOUNT = @P20 ) ) ) )
       AND ( ( T7.PARTITION = @P21 )
             AND ( ( ( T7.SUBLEDGERJOURNALACCOUNTENTRY = T4.RECID )
                     AND ( T7.ACCOUNTINGDISTRIBUTION = T6.RECID ) )
                   AND ( ( ( T7.ACCOUNTINGCURRENCYAMOUNT >= @P22 )
                           AND ( T6.TRANSACTIONCURRENCYAMOUNT >= @P23 ) )
                          OR ( ( T7.ACCOUNTINGCURRENCYAMOUNT < @P24 )
                               AND ( T6.TRANSACTIONCURRENCYAMOUNT < @P25 ) ) ) ) )
GROUP  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT
ORDER  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT

You can use this

 select st.text, qp.query_plan,  qs.plan_handle,
        total_worker_time/execution_count AS [Avg CPU Time]
 FROM sys.dm_exec_query_stats AS qs
    cross apply sys.dm_exec_sql_text(qs.plan_handle) st
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

if you are using sql server 2008 and above you can configure Extended Events as well to see plans etc.


Hierarchy of Employee "names" with CTE: Cannot create a correct version I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have .

And this is my current version: . As you can see there are duplicates and i have no idea how to get rid of them. Futhermore the structure is wrong. I already tried to look for solutions like here but was unable to come up with solution.
CTE query doesn't print the right structure?

My Employee table looks like this http://sqlfiddle.com/#!6/428d2/2

Here is my SQL query I use to create the correct name column: http://sqlfiddle.com/#!6/bf4c1/4

I use MsSQL 2012, so feel free to use newest fuctions etc. Any help appreciated.

If you specify the root node in the CTE, it works ok.

WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
             [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
        UNION ALL

        SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
             Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss]
     )

SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

SQL Fiddle here.


String Pooling for MySQL So I'm having this table that logs all errors happened in our production code. It looks like this:

id  | date | type         | message       | file         | line
----+------+--------------+---------------+--------------+-----------
INT | DATE | VARCHAR(128) | VARCHAR(4096) | VARCHAR(255) | SMALLINT

Now, in most cases errors will be reported via a simple HTTP-API, issued from the client's server running our software. Naturally, when something happens, it happens often. So most errors get logged between 10 and 200 times, in rare cases up to 1k times.

So I end up with many records having the same value for the message field (and for type and file as well). Since I could not find any details via Google, I'm now wondering whether it would be wise to manually store the strings in another table like this:

hash        | string
------------+----------
VARCHAR(32) | VARCHAR(4096)

Assuming I would hash the values of the three fields with MD5, I could shrink their space usage down to VARCHAR(34), using the scheme

l$I'm a literal value.             for fields < 32 chars
h$md5(field)                       for fields > 32 chars

I did some benchmarking and it seems to save about 15 to 20% from my error table. To handle the concurrency with transaction, my tables use InnoDB. What is the difference between MySQL VARCHAR and TEXT data types? points out that InnoDB will store string values always outside the normal table, but is it pooling or storing each string for itself?

Additionally, I plan on using PostgreSQL, too. rolleyes :-)

Any suggestions? Is it wise to go the extra mile and do string pooling?

Small starting clarification: the article you linked to on InnoDB text/blob storage is a little out of date with MySQL 5.5, and the barracuda row format.  This article is more up to date.

On to your question:

In the schema you have, each row will be less than ~8K, so you can guarantee across both antelope and barracuda row formats that all data will be stored in-line - not requiring external/overflow pages.

If you were to require overflow pages, they are never de-duplicated (which is what I would probably describe your 'pooling' mechanism as).  Even worse than they are never de-duplicated, they are never shared...

If you could have a record too big to fit inline (~8K limit), each text/blob that needs to be moved out will take a minimum of a 16K page to itself.


MySQL table locks solution -> InnoDb / Partitions I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).

It's mainly caused due many heavy inserts. My current thoughts to optimize:

Changing to Innodb - to avoid table level lock. In the past I've had bad experience with Innodb, possibly due to bad configuration. If you would recommend this option, please advise regarding configuration.
Adding partitions - we're running on one server, and we don't have different mounts in the HDs, so we're just avoiding locks in such a solution. Currently the table isn't 'key-value', but it can be done (normalization to different table).

Which would you recommend / other options?

This is the SHOW CREATE TABLE

CREATE TABLE `tbl_name` (
 `var0` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `var1` int(11) NOT NULL,
 `var2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var7` tinyint(1) NOT NULL,
 `var8` int(11) NOT NULL,
 `var9` int(11) NOT NULL,
 `var10` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var11` int(11) NOT NULL,
 ...
 `var22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`var0`),
 UNIQUE KEY `var1` (`var1`),
 KEY `var2` (`var2`,`var3`),
 KEY `var4` (`var4`),
 KEY `var5` (`var5`)
) ENGINE=MyISAM AUTO_INCREMENT=13932545 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This is the SHOW TABLE STATUS

Name      Engine  Version Row_format  Rows      Avg_row_length  Data_length     Max_data_length  Index_length    Data_free    Auto_increment   Create_time           Update_time             Check_time              Collation         Checksum        Create_options  Comment
tbl_name  MyISAM  10      Dynamic     13931652  2605            36293518012     281474976710655  1318508544      0            13933203         2012-07-31 13:25:26   2012-08-26 14:44:44     2012-08-20 23:15:31     utf8_unicode_ci   NULL

key_buffer_size ?  RAM size?

On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.

var0, being an AUTO_INCREMENT, will be "appended to" the end.  Essentially no disk hits for it.  I can't tell about the other 4 indexes.  Let's say they are all very random.  This implies that each row inserted will need 4 disk hits.  On normal drives that means about 25 rows inserted per second.

Solutions...

RAID striping (0,5,6,10).  That will give you a factor of improvement.

SSDs.  $$$

PARTITIONing.  This may help.  It may also help with some SELECTs.  To discuss further, please reveal some of the semantics of the indexed fields.  (dates?  md5s?  names?)  Also provide some of the SELECTs, so we can optimize them at the same time.  The "partition key" must be part of any UNIQUE key.  This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.

Nothing (yet) says whether the Engine will make any difference.


How to benchmark query parsing & planning in Postgres? I'm trying to benchmark the query planning and parsing steps in PG:

However, it seems that the query planner is using some sort of cache - only the first execution of EXPLAIN ANALYZE seems to report a high value in the Planning time section. Subsequently, it is less than 1ms. How do I turn this off?
The docs clearly state that the parsing time is not taken into account by EXPLAIN ANALYZE. Is there any other command/tool that will report the time it took to parse a query?

  The Planning time shown by EXPLAIN ANALYZE is the time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting.

It is not caching the actual prepared plans (unless you are using prepared statements).  But it does cache all the metadata it needs to look up in order to come up with the plan.  So the original planning time includes the time it took to read in all of that metadata.

If you wish to defeat the caching for experimental purposes, the simplest way would be to simply close psql and re-open it between every experiment.

For instrumentation, You can time each step of the parse-plan-execute process using log_statement_stats or its kin.  You can also set client_min_messages = log so that those reported statistics show up directly on the psql screen, rather than having to go fetch them from the server log file.  You will need to be a superuser to set either of those.


Optional variable/parameter in SSIS I have a SSIS package which has the following:

Data Flow Task

OLE DB Source (select * from table1 where DateCol <= ISNULL(@DateVar,DateCol ))
OLE DB Destination

DateVar variable

And I need to have a possibility to set value for DateVar or leave it empty or NULL (by default).
So, it could be two cases: DateVar have a value and DateVar have no value

Is it possible in SSIS?

If so, how can I do this?

Thank you in advance!

You may be interested in a standard approach to solve this problem. It is something called short circuit. To illustrate it, here's the code:

DECLARE @chooseDate datetime2
--SET @chooseDate = '1966-04-08'
SET @chooseDate = NULL
SELECT *
FROM DimCustomer
WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

This way you can explicitly set variable to NULL, do not set variable to anything, or specify a value.

The reason it is called short circuit is that based on the implementation of ANSI SQL this line of code:

WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

has a possibility to evaluate only the first part of the expression (the IS NULL part) thus excluding the need to evaluate the second part, which makes the code slightly faster.


How to pass a column name using string_split - ms sql server? I'm trying to pass column names using a string split because I need the values from that table to join with another table.

I've already achieved this in Postgresql where I get the values from these columns:

   SELECT unnest(array["column_name1","column_name2","column_name3" AS "amount"
   FROM table1

The unnest in combination with the quotes around the column names returns an array to a set of row for every value.

Amount
307579.00
25579.00
212871.00

Now I want to do the same in MS Server:

   DECLARE @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
   SELECT "value" from string_split(@amount,'/')

But this only gives me the literal string:

Amount
column_name1
column_name2
column_name3

I've been trying and searching how to pass an elemant that will read as a column name and shows the values in one column like the first example, maybe I shouldn't use the string splitter etc. Kinda stuck on this..

You'll need to use dynamic SQL to implement your logic, by constructing a whole new SELECT query.

For example:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' FROM table1'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT [column_name1], [column_name2], [column_name3] FROM table1

In order to get all the values under a single column, you'll need to "UnPivot" the data. There are two main ways to do so. One is by a standard UNPIVOT command, like so:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' ) AS unpvt'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( [column_name1], [column_name2], [column_name3]) AS unpvt

Another way is to use CROSS APPLY with a Values Constructor, like this:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + '), (', N'SELECT v.amount FROM table1 CROSS APPLY(VALUES(') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N')) AS v(amount)'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT v.amount FROM table1 CROSS APPLY(VALUES([column_name1]), ([column_name2]), ([column_name3])) AS v(amount)


Table Filtering Using Big List Briefly, I am trying to filter a table using conditions plus a list of items in Oracle. In detail,
I have a reporting table which has millons of records. I would like to filter it using external lists which might include more than 1000 lines of fields from the record table. (Obviously, I especially said 1000, because in clause in Oracle has the limit of 1000 items). So my question is; How can I effectively filter a huge table using another big list.

How can I effectively filter a huge table using another big list?

Just JOIN the big table to list.
Just make sure your list is a table format:

External Table
Global Temporary Table
APEX Collection (for APEX apps)
an SQL collection (variable)

PL/SQL collection (18c+)


What is the "proper" way to design a database architecture when entry counts belogning to different users may vary wildly? To give a context; we are going rewrite our application in Django framework. The database that is being used by the old system at the moment designed very poorly, without any respect to methodology of DB design, so we decided to re-design the database architecture as well.

One table, that we've been having trouble was something akin to "Items". Different users have different items, and the item amount varies wildly between users, so holding everything in shared tables seems like a bad idea. We thought asssigning private db processes to every single user (like, every user will have their own sqlite process, invisible from others), but I'm skeptical about this idea too as I've never seen or heard something like this in practice. It also feels like we are breaking a layer of abstraction for something we are not sure of.

My experience with such situations are very dim, so every bit of advice-help will be much appreciated.

No, if the items share common characteristics you should put them in the same table and define an index over the UserID.


How to design relationships for variant data? Help me define the table relationships for data with multiple structures. I need to track leads. Leads come from multiple sources. Each lead source has its own data structure. I need advice representing this in a relational database.

Each record in the TABLE "Products Sold List" (defined below) has a lead source. The leads can come from Organizations, Doctors, Company Office, and Company Employees. Here are the different types of data that constitute a lead.

TABLE Organization:
   - Name -- TEXT

TABLE Doctor:
   - NPI# -- INTEGER (unique for each doctor. I planned on using this as primary key)
   - FirstName -- TEXT
   - LastName -- TEXT

TABLE Company Office:
   - Location -- TEXT

TABLE Company Employee:
   - Department
      -- one of {Sales, Technician, QA}. Note that the employee is not tied to an office.
         Office leads are leads that come from misc office staff at the various locations.
   - FirstName
   - LastName

Here is TABLE Products Sold List:
   - [misc fields]
   - PrescribingPhysicianNPI > many-to-one > Doctor.NPI
      -- "doctor that prescribed the product for the patient"
   - SalesRep > many-to-one > [Company Employee].PrimaryKey
      -- "Sales Rep credited for the sale. Needed for commissions calculation"
   - LeadSource > ???

Misc information:

The company offers incentives to leads (affiliate commission) and incentives to sales staff (sales commission). Leads coming from Organizations and Company Offices don't get paid affiliate commission. I am considering merging TABLE Company Office into TABLE organization and using "Office -- " as the data to track different offices (i.e.: Organization.Name = "Office -- Cincinnati"). Bad idea? Issues?

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2


Oracle - PFILE or SPFILE when instance is down This may be a stupid question, but I can't find any information out there on this.
Is there a way to tell if the database uses a pfile or spfile when the instance is down?
For example if an Oracle database went down that I was not familiar with and had no documentation on, and I was tasked with getting it back up and running. How would I know if it used a PFILe or SPFILE? Startup vs Startup pfile=
Thanks in advance

It does not really matter, but spfile is favored because it provides better functionality.
When an instance is down, it does not use any of them.
You may check the alert log for the previous startups, the pfile/spfile location is displayed there after each startup.
If the database is registered in Grid Infrastructure as a database resource, the spfile location is typically (but not mandatorily) specified there (srvctl config database -d database_name).
By the way, most databases use SPFILE.


Inconsistent behavior of aggregate functions about whether to return NULL on empty input? On an empty input table, PostgreSQL (12.x) is returning NULL on some aggregate functions like array_agg() but normal values for others like count(), as shown below:

=> SELECT count(*) IS NULL FROM (SELECT 1 a) e WHERE a > 1;
 ?column?
----------
 f
(1 row)

=> SELECT array_agg(a) IS NULL FROM (SELECT 1 a) t WHERE a > 1;
 ?column?
----------
 t
(1 row)

Why does not array_agg() return a normal value (i.e., an empty array) in case of empty input?

This is somewhat disturbing as it's difficult to remember which ones can return NULL.

Related to this, what are the aggregates that return NULL on empties?

I can't speak from any experience but SQL Server, but I'll assume that this applies to most/all SQL language implementations:

COUNT(*) is counting rows. If there are zero rows to count, zero is the response.

Most other aggregate functions depend upon values contained in fields (MIN, MAX, SUM, etc). If there are no values to aggregate (either because no records are in the dataset or only NULL values in the field(s) involved), NULL is the response.


How to handle master changed during mongorestore? I have a very large dump data(18GB) need to restore. I tried to use mongorestore to restore the data to a replica set. But the restore process canceled because of error restoring from orders.bson: insertion error: not master. I think this is because the master get changed in the replica set. Is there a way for me to restore the data and stop changing primary on the replica set roles?

Yes and no. You need to check from log file, why primary status was moved to other node, because it's not happened without reason.

You can always change your rs.conf() so, one node have higher priority than other, so it stays primary as long as it can.

But in this situation you still need to check from mongod.log file reason why replica sets nodes voted that move primary status was necessary.


Searching multiple tables in SQL This question will involve some C# code but I hope that is fine, in the end it is the SQL that is the issue. (To see short question look at end of post)

So I have a site, where you can add a site in to a category as well as a subcategory. Now I want to search the entire database using the same searchbox, redirecting to the same page.If the search finds a category I want the page to display a list of sites in that category. And if I search for a site I want to show a profile of that site.

So what does this mean? Well, it means I need a way to tell if the result (from the search) is coming from my site-table, my category-table or my subcategory-table.

So far, I have the following SQL:

"SELECT url, 'Web' As Type FROM Website WHERE (url=@search)
UNION ALL
SELECT category, 'Cat' As Type FROM Category WHERE (category=@search)";

Now this does search both tables, and return a type of "Web" if it comes from the Website-table or a type of "Cat" if it comes from the category table. However, the result always is under column "url", even if the result comes from the category-table.

C# that works:

using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("url"));
        }
    }

C# that does not work:

 using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("category"));
        }
    }

As above, you can see I only get the Type when I add "url" to the reader. Which I understand, in a UNION ALL it selects the first column as the name.

What I need is some way to do this so that I can select ALL (*) from my tables with one query and then be able to know what table it came from. Something like this (pseudocode):

if(search is from Website-table){
DisplayWebsitet();
}
else if(search is from Category-table){
DisplayCategory();
}
else{
DisplaySubCategory();
}

Is there a way to do this using SQL? Is there for example some if/else function I could use? There should be since at for example www.imdb.com you can search for both actor and movie. Or do I need to run 3 different queries? something I really want to avoid doing.

EDIT:
Here is something that kind of gives a result I want but only works inside SQL-studio. I cant use this in C# for some reason. Maybe if I made it a stored procedure and loaded it from C#?

Scenario - If someone searches for "www.facebook.com":

SELECT Category.*, 'Cat' As Type FROM Category WHERE category='www.facebook.com'

SELECT SubCategory.*, 'Sub' As Type FROM SubCategory WHERE subCategory='www.facebook.com'

SELECT Website.*, 'web' As Type FROM Website WHERE url='www.facebook.com'

This return 2 empty results and 1 result (from website table) with all the corresponding information to "www.facebook.com".
If I save this as a stored procedure, can I access the type from C#? Is it a good way to do this?

A quick way to resolve your issue would be simply to return a Null column in each of the select statements.

SELECT
    url, NULL AS Category, NULL AS SubCategory,'Web' As Type FROM Website WHERE (url='www.facebook.com')
UNION ALL
SELECT
     NULL, category, NULL AS SubCategory, 'Cat' As Type FROM Category WHERE (category='www.facebook.com')
UNION ALL
SELECT
    NULL, NULL, subCategory, 'Sub' As Type FROM SubCategory WHERE (subCategory='www.facebook.com')

This generate the following output which should match your requirement of output

       url       | Category | SubCategory  | Type
www.facebook.com |  NULL    |    NULL      |  Web


SQL tracking number of Selects of a databases table row Hello I'm trying to create a way in which I could track the number of times a certain column in a database table is selected. Is there something I could implement like a trigger that could increment a count column on another table that is associated with the selected row? Trying to figure out which rows in my database tables are used the most.

Thanks, any help is appreciated.

For SQL Server, you could set up an Audit as described in the documentation: SQL Server Audit (Database Engine).

  All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

See also Auditing SELECT statements in SQL Server 2008 by Sreekar Mankala.


Order after rollup - oracle 11g I am using rollup() function in group by expression to show the result query,
but my output like this:

Year    Month        AmountOfMoney
====    =====        =============
1998    March        130.00
1998    April        450.00
1988    -            580.00
1999    January      120.00
1999    April        550.00
1999    -            670.00
-       -            1250.00

how to make it order by total, then years, then (year,month)

Year    Month        AmountOfMoney
====    =====        =============
-       -            1250.00
1988    -            580.00
1999    -            670.00
1998    March        130.00
1998    April        450.00
1999    January      120.00
1999    April        550.00

I'm not a ORACLE user but I've found an interesting article about it:

Oracle SQL GROUP BY – The Complete Guide

Scrolling down till the GROUPING Function you'll find and example on how use GROUP BY with ROLLUP:

Quoted from the article

  There are several SQL functions related to grouping that can help you
  identify and work with groups.

  Earlier in our examples, we noticed that subtotals had values of NULL
  for the columns that weren’t being considered in the group.

  How can we tell which values are subtotals and which values are NULL
  because of the underlying data?

  This is what the GROUPING function can help with.

  The GROUPING function takes a single column as a parameter. The output
  is either 1 or 0:

  It returns 1 if the column is being used as part of a subtotal and is
  showing NULL. It returns 0 if the underlying value is NULL or any
  other value.

  You can also order by these columns, to have the subtotals at the
  start or the end, instead of in the middle of the results.

According to this you could use the next syntax to produce the expected result:

SELECT
    Year,
    Month,
    SUM(Amount) as AmoutOfMone
FROM
    YourTable
GROUP BY ROLLUP (Year, Month)
ORDER BY
    GROUPING(Year) DESC,
    GROUPING(Month) DESC,
    Year ASC,
    Month ASC;

Due this question has a previous question here, I've used my fiddle to add this new information and it works properly.

/* aggregated sales by year-month */
SELECT
    T.CALENDAR_YEAR AS Year,
    T.CALENDAR_MONTH_DESC AS Month,
    SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
    SALES S
JOIN
    TIMES T
    ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = 1
GROUP BY ROLLUP (T.CALENDAR_YEAR, T.CALENDAR_MONTH_DESC)
ORDER BY GROUPING (T.CALENDAR_YEAR) DESC,
         GROUPING (T.CALENDAR_MONTH_DESC) DESC,
         CALENDAR_YEAR ASC,
         CALENDAR_MONTH_DESC ASC;

YEAR | MONTH | AMOUNTOFMONEY
---: | ----: | ------------:
null |  null |           450
2018 |  null |           450
2018 |     1 |           100
2018 |     2 |           200
2018 |     3 |           150

db<>fiddle here


Best practices for sql server log shipping method We have about 20 databases, with no DR config yet.
Most of them are SQL Server 2016 with Failover cluster Availability group
What's the best method for a log shipping?

Log shipping directly between DC site to DR site
Log shipping through a fileshare server

Imo, 2 is much more risks in case of file share server are accessed by someone else.

I supposing you are using Alwayson AG with a SQL server enterprise edition (because you are in a business critical production environment). Why don't you add the DR instance as a node of the alwayson AG cluster? It's free and included in the license you already own.
Instead if you are using basic availability group, you are limited to one copy.
So you have to look up for something like log shipping.
Log shipping is based on a share that plays as a repository for tlog backup between primary and distare recovery site.
After saying that, you have to evaluate that the source environment is composed by two nodes that have to use the same repository for LS tlog backups. So a network path is more friendly and reliable.
The risk of having someone to disrupt the share you created is something you have to handle with security and rights in your environment; it should not prevent smart architectural choises.


How far does one table, one query go? I'm doing a MySQL to Cassandra conversion. The fundamentals of the dataset are a master table of size ~10^7 rows to which several subsidiary tables are linked by foreign key. The subsidiary tables are up to size 10^10 rows.

The master table is really a set of seven columns, and it could be interpreted as a seven element partition key into which each 'row' in a subsidiary table could be assigned. These seven columns account for 99% of what is used with the WHERE clause for all SELECTS from the MySQL database. So far so good for conversion to the Cassandra schema.

Each of the seven columns in the master table has no more than 10,000 possible values, so each would seem to be a good partition key on its own. From reading about Cassandra database setup best practices, many people suggest a one table, one query approach. In this case, it would indicate that I should make seven tables, each using a single column of the master table as its primary key.

This would end up replicating many columns of data seven times over each. That this is the optimum strategy is something that my RDBMS wired brain can't seem to accept. For optimum read performance, should I replicate all columns of data into seven tables, one for each indexed column for searching?

The “one table per query” thing is a misnomer - the real practical advice is to figure out what you need to read, and then store data to serve that purpose

Sometimes that means fully denormalizing your data, duplicating columns between tables

Sometimes that means lookup tables that act as manual indices - whether you denormalize or use a lookup table depends on which is easier for you, the extra query or the extra storage cost. That’s a personal decision.

10,000 items in a partition is probably fine unless their large blobs (try not to hit 100mb/partition), but do try to take advantage of clustering for queries where you’ll fetch multiple rows.


Why is the sum() of zero numbers not zero? For the following query

SELECT sum(c)
FROM (SELECT 1 c WHERE false) t;

I thought it takes the sum of zero numbers, and therefore should return 0. (Similarly, I expect, e.g. an array aggregate function to return an empty array on zero elements).

Yet, it is returning a NULL (using PostgreSQL 11).

=> SELECT sum(c) FROM (SELECT 1 c WHERE false) t;
 sum
-----

(1 row)

This doesn't make sense to me logically.

Why is the sum of zero numbers (for any numeric type) not zero? Is this NULL returning behavior standard?

Or maybe I am missing something. Any reason one has to make the sum NULL in some corner cases?

This is a requirement from the SQL standard. I'm most familiar with the 1992 standard. Quoting from it, with emphasis mine:

  General Rules

  1) Case:

  a) If COUNT(*) is specified, then the result is the cardinality
       of T.

  6.5

  b) Otherwise, let TX be the single-column table that is the
       result of applying the  to each row of T
       and eliminating null values. If one or more null values are
       eliminated, then a completion condition is raised: warning-
       null value eliminated in set function.

  2) If DISTINCT is specified, then let TXA be the result of elimi-
  nating redundant duplicate values from TX. Otherwise, let TXA be
  TX.

  Case:

  a) If the  COUNT is specified, then the
       result is the cardinality of TXA.

  b) If AVG, MAX, MIN, or SUM is specified, then

  Case:

  i) If TXA is empty, then the result is the null value.

In short, NULLs are always excluded from the result of SUM, MAX, MIN, and AVG. I can't tell you exactly why this is, other than I think it's useful to be able to distinguish between a sum of a single value of 0 and a sum of an empty set.


Unable to connect to MySQL cluster database I am unable to connect to mysql cluster database after starting my management node, data node, mysql node. I made the necessary changes by adding the loadbalance in the dbURL. But I am not able to access the connection. The mysqld port is 1186. Please assist.

String dbURL = "jdbc:mysql:loadbalance://localhost/bank";
String dbDriver = "com.mysql.jdbc.Driver";
private Connection dbCon;
public boolean connect() throws ClassNotFoundException, SQLException {
        Class.forName(dbDriver);

        // login credentials to your MySQL server
        dbCon = DriverManager.getConnection(dbURL, "root", "");
        return true;
    }

The error I am getting when I try to log in is:

  javax.servlet.ServletException:
  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
  Communications link failure

How do I test MySQL cluster database? I managed to login using the following in command prompt. Please assist.

  mysql -h 127.0.0.1 -P5000 -u root

It looks like your MySQLD is running at port 5000, from the working mysql command line.
  The server listening on port 1186 is probably the cluster management server (ndb_mgmd).  It has its own client (ndb_mgm).  You cannot connect to it using the mysql client.  Similarly, the MySQL JDBC driver cannot connect to it.
Frazer


SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still? I know there are several posts about stripping the time portion from a datetime column. And everyone suggests that the DATEADD and DATEDIFF method is the best for performance and individuals should not adopt the process of cast/converting date values to varchar. So I was wanting to utilize the functions below but I don't understand why the result I receive still has the time value associated to it though the time is all zeroes?

To provide more information as to why I want to understand this result. I have a stored procedure that I wish to specify the following:

DECLARE @DateShippedBegin DATETIME
DECLARE @DateShippedEnd DATETIME
SET @DateShippedBegin = dateadd(dd, datediff(dd, 0, @DateShipped), 0)
SET @DateShippedEnd = dateadd(dd, datediff(dd, 0, @DateShipped) + 1, 0)

WHERE O.date_shipped >= @DateShippedBegin and O.date_shipped < @DateShippedEnd

But when I execute the procedure with a basic date format I receive no results so trying to understand the best concept to adopt for my procedure.

Select DATEADD(dd, DATEDIFF(dd, 0, Getdate()), 0) from orders where order_no =
'1247401'

Example of begginning date value:

2017-05-010 08:40:18.287

Example of query result:

2017-05-10 00:00:00.000

A datetime column always includes a time part. If you set a datetime column to '2017-05-10', it'll show up as 2017-05-10 00:00:00.

When you want to compare "dates only" with datetime columns, you set the time part to midnight on both dates. This is generally referred to as "removing" the time part.

If you want to eliminate the time part from your output, there are various options. You can build a date in whatever format you wish by pulling the various fields out with DATEPART. Most commonly, however, I've seen people convert the date to a string and drop the time portion:

SELECT CONVERT(varchar(10), GETDATE(), 120);

Note that CONVERT is being used here for formatting the date value for output, not for changing it for comparison purposes. I'd still do that as you did in your code.


Complex MySQL Query returning almost all the correct rows I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work correctly.

I've gotten this far with my query:

SELECT DISTINCT o.orderid,i.invoiceid,i.subinvoicenumber,stockingorderid AS oistockingorderid,

IF(stockingorderid > 0, 0, po.purchaseorderid) AS purchaseorderid,subponumber,po.paidvia,dropshipfee,i.taxstate,

COALESCE(
    (SELECT mfrname
        FROM cs_products.tblstockingorders so
        WHERE so.stockingorderid=oistockingorderid),
    po.mfrname)
AS source,

(i.shipping + i.surcharge +
    (SELECT SUM(additionalshipping * quantity) AS additionalshipping
        FROM cs_products.tblorderitems
        WHERE invoiceid=i.invoiceid))
AS shipping,

CAST(IF(o.paymentmethod=2, CONCAT('Check: ', i.checknumber),o.paymentmethod) AS CHAR) AS invoicepaidvia,

COALESCE(
    (SELECT (SUM(cost * quantity) * IF(so.mfrdiscount > 0, 1 - so.mfrdiscount, 1)) AS cost
        FROM cs_products.tblorderitems oi
        JOIN cs_products.tblallocations a ON oi.orderitemid=a.orderitemid
        JOIN cs_products.tblstockingorders so ON a.stockingorderid=so.stockingorderid
        WHERE a.stockingorderid=oistockingorderid),
    (SELECT (SUM(cost * quantity) * IF(po.mfrdiscount > 0, 1 - po.mfrdiscount, 1)) AS cost
        FROM cs_products.tblorderitems
        WHERE purchaseorderid=po.purchaseorderid))
    AS grosscost,

(SELECT SUM(price * quantity)
    FROM cs_products.tblorderitems
    WHERE invoiceid=i.invoiceid)
AS grossprice

FROM cs_products.tblorders o
    JOIN cs_products.tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN cs_products.tblpurchaseorders po ON o.orderid=po.orderid AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN cs_products.tblallocations a ON a.orderid=o.orderid

This gets me 95% of the way there. As you can see, for OrderId 1287, it shows two records with stockingorderids and none with a purchaseorderid even though a purchase order has been attributed to 1287. What I'm expecting to see for the missing row is something like:

1287|276|0|NULL|194|0||0|0|'Quality Fabricators'|357.53|0|781.43|11917.70

Again, don't worry about the calculated columns, I just want the record to appear. I feel like I'm missing the smallest thing, but I don't know what it is.

Thank you!

Something like

SELECT DISTINCT
o.orderid,
i.invoiceid,
i.subinvoicenumber,
NULL AS oistockingorderid,
po.purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    JOIN tblpurchaseorders po
      ON o.orderid=po.orderid
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

UNION

SELECT DISTINCT
o.orderid,
i.invoiceid,
i.subinvoicenumber,
stockingorderid AS oistockingorderid,
NULL AS purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN tblpurchaseorders po
      ON o.orderid=po.orderid
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

The second SELECT is just the same as your original.
In the calculated fields you can omit the COALESCE from grosscost, and use the two halves separately (according to the non-NULL ID).