Why would I NOT use the SQL Server option "optimize for ad hoc workloads"? I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

Why is there even an option to "optimize for ad hoc workloads"? Shouldn't this always be on? Whether the developers are using ad-hoc SQL or not, why would you not have this option enabled on every instance that supports it (SQL 2008+), thereby reducing cache bloat?

The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions.
Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!

Kalen Delaney tells an interesting anecdote that she asked one of her Microsoft engineer friends whether there would be circumstances where it would not be appropriate to enable this. He comes back several days later to say - imagine an application that has a LOT of different queries, and each query runs exactly twice in total. Then it might be inappropriate. Suffice to say there's not many apps like that!
If the majority of your queries are executed more than once (not exactly twice); it would likely be inappropriate. The general rule would be to turn it if there are many one-time-use adhoc queries on the database; however, there are still not many apps like that.


Is there any way I can speed up this large full-table query? I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 million rows out from a table of 13 million rows (the other 9 million records are older than 2019), and it is returning all of the columns, of which there are 101 (a mix of datetime, varchar, and int columns). It has two indexes, a clustered one on its primary key interaction_id, and an unclustered index on interaction_date which is a datetime column that is the main filter. This is the query:

  SELECT *
  FROM [Sales].[dbo].[Interaction]
  WHERE
  year(Interaction_date) >= 2019

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself? Before I go into an ETL processes or fight back on the group that needs this query (they are a Hadoop sqooping team who insist they need to sqoop all of these records all the time with all of the columns), I want to see if I can make it easier on people by doing something on my end as the DBA.

The query plan by default ignores my non-clustered index on the interaction_date column and still does a full clustered index scan. So I then tried forcing it to use it by including WITH (INDEX(IX_Interaction_Interaction_Date)) in the select.

This forces it into the query plan startign with an index scan of the non-clustered index, with estimated rows 4 million but estimated rows to be read as all 13 million. Then after a short time it spends the rest of the execution on the key lookup of the primary clustered index.

But ultimately, it doesn't seem to speed up the query at all.

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself?

Yes.  First make the predicate sargable.

SELECT * FROM
[Sales].[dbo].[Interaction]
WHERE Interaction_date >= '20190101'

And then consider partitioning, or a filtered index with included columns.  But even if you have an index that can support this query as a simple seek+scan, sending all the columns to the client takes time.


Order of WHERE IN result for PostgreSQL If I use the following selection

SELECT * FROM users WHERE id IN (3,1,2)

Can I expect the result to be in the same order like the provided ids?

Short answer: No.

The only way to guarantee the order of rows returned is through the "order by" clause.

You have little or no control over how the database chooses to "get at" any of your data, and, therefore, the order in which it gets to those records in doing so.

You might get lucky and things might come out in the order you want ...   but don't rely on it.


Get the job scripts out of a MSDB Backup Long Story Short... my Database server died a month ago. I restored everything but the MSDB as the tape backup failed. I reconstructed some jobs but some essential ones are missing. I finally found the MSDB backup in an old partition, but here's the gist... it isn't a .BAK file but the actual database and log. I already created a new Database in my instance and attached the MSDB (with different name obviously, MSDBJobs) but I have no idea whatsoever on how can I extract the jobs from this database. I don't want to replace the current ones as some are new and essential as well. Thanks for any help provided :)

Perhap simplest would be to do the following:

Install a new instance of SQL Server.
Shut down the SQL Server, delete the msdb data and log files.
Copy the old msdb data and log files to the same location as the files you deleted.
Bring up the new instance of SQL Server and script out the jobs.  (You can use SSMS Object Explorer Details to select all jobs and create one massive script, or do them one at a time.)
Uninstall the new instance.

There are also scripts, including this powershell script, but they use methods that talk to msdb:
http://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/


"Invalid Protection Option" In the following error message, the reason given is "Invalid Protection Option".  What does that indicate?

  SQL Server detected a logical consistency-based I/O error: invalid protection option

For the purposes of this question, I don't need to know anything about "how to run DBCC", or "check for corruption".  I got that.  I'm just curious about the "root cause" piece, and what could possibly cause this flavor of the logical consistency-based I/O error.

What does that indicate?

It indicates that invalid page flags for protection are set, mostly likely caused by something in the hardware, Windows I/O stack, or 3rd party software. In supported SQL Server versions there are three options for page protections:

None
Torn Page
Checksum

Imagine reading a page from disk and doing basic sanity checks against it. One check might be, let's check to make sure the page protection options are set appropriately. If there are three options, having a 4th value set or having multiple options set would be invalid.

  I'm just curious about the "root cause" piece, and what could possibly cause this flavor of the logical consistency-based I/O error.

It indicates that something scribbled the data on disk or the data being read from the disk into memory. The root cause for on-disk corruption is extremely hard to get if it isn't reproducible, so at best it's a bad piece of hardware or software in the I/O stack. Worst is it's another application not playing nice and doing whatever it wants to files on that server.


How to check if a combination of columns exists in another table in SQL Server? This is how I would express my query in MySQL or Oracle:

SELECT t1.c1, t2.c1, t1.c2, t2.c2, ...
FROM t1, t2
WHERE (t1.c1, t2.c1) NOT IN (SELECT c1, c2 FROM t3)

How can I express the same query in SQL Server?

I figured I could concatenate them as strings but it cannot be the best way to do it.

I could not find a way to do it nicely in SQL Server.

I cannot join them once more because each table contains millions of records.

Example tables so that it is easier to imagine the problem:

t1:

c1 c2 c3
--------
1  x  A
2  y  B
3  z  C

t2:

c1 c2 c3
--------
1  m  D
2  n  E
3  t  F
4  v  G

t3:

c1 c2 c4
--------
1  2  aa
1  4  bb
2  3  cc

A join in SQL Server is not automatically implemented as a nested loop. For example, a hash join can be used to implement the NOT IN. This means that the query will not necessarily "end up in a number of tuples over 10^18" even if each of the three tables being joined contains over 10^6 rows.

For example, here is a query plan on a reduced data volume that shows that the largest number of rows at any point in the query plan is proportional to the the size of t1 times the size of t2, but is independent of the size of t3.

Even so, there is no getting around the fact that a query that selects 10 trillion rows (presumably to aggregate them, or to insert them into a columnstore table?) is going to take quite some time to execute. Here's the full script, along with further analysis:

Create sample data

DROP TABLE IF EXISTS #t1, #t2, #t3, #batchMode, #results
GO

CREATE TABLE #t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #t2 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #t3 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX CI CLUSTERED (c1, c2))
CREATE TABLE #batchMode (dummy INT NOT NULL)
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON #batchMode
GO

DECLARE @numRows INT = 32000
INSERT INTO #t1 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 33) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 57) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

INSERT INTO #t2 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 17) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 74) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

INSERT INTO #t3 (c1, c2)
SELECT TOP(@numRows)
    ABS(CRYPT_GEN_RANDOM(8) % 33) AS c1,
    ABS(CRYPT_GEN_RANDOM(8) % 17) AS c2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO

Execute the query

By executing the query with different row counts, we can see that query performances increases quadratically (N^2) with the row count of t1 and t2, but is not significantly impacted by the row count of t3. We can therefore extrapolate the execution time with the full 16 trillion row cross product to be about 217 hours.

-- Perform your query, using aggregates to avoid writing billions of rows to console
-- 4K rows / table      CPU time = 13906 ms,  elapsed time = 1510 ms.
-- 8K rows / table      CPU time = 54563 ms,  elapsed time = 4548 ms.
-- 16K rows / table     CPU time = 192032 ms,  elapsed time = 13683 ms.
-- 32K rows / table     CPU time = 757688 ms,  elapsed time = 50754 ms.
-- Since the time scales quadratically once we have enough rows to efficiently utilize
-- all threads, we can predict 4MM rows / table with the following query:
--      SELECT POWER(4000000 / 32000, 2) * (50. / 3600)
-- 4MM rows / table     Estimated elapsed time = 217 hours
SELECT COUNT_BIG(*), SUM(1.0 * t1.c1 * t2.c1 * t1.c2 * t2.c2)
FROM #t1 t1
CROSS JOIN #t2 t2
WHERE NOT EXISTS (
    SELECT *
    FROM #t3 t3
    WHERE t3.c1 = t1.c1
        AND t3.c2 = t2.c2
)
    /* Enable batch mode hash join to avoid repartition streams on billions of rows
        This reduces elapsed time from 77 seconds to 50 seconds at 32K rows / table */
    AND NOT EXISTS (SELECT * FROM #batchMode WHERE 0=1)
OPTION (MAXDOP 16)
GO

Replace loop join with batch mode hash join

Because this query is very CPU intensive, it is natural to consider whether the majority of the work — the loop join to build out the cross-product of rows between t1 and t2 — can be replaced with an operator that supports batch mode execution. One way to do this is to re-implement the cross join as an equijoin on a single column where all rows in each table have the same value.

This reduces the projected time by about a factor of 10, to about 20 hours to filter and aggregate the 16 trillion intermediate rows generated by the cross product.

-- Add a column that will always have the value 1 to each table, enabling an
-- as hash match implementation of a cross join by matching on this column
ALTER TABLE #t1 ADD one TINYINT NOT NULL DEFAULT (1)
ALTER TABLE #t2 ADD one TINYINT NOT NULL DEFAULT (1)
GO

-- 32K rows / table     CPU time = 61112 ms,    elapsed time = 5507 ms.
-- 64K rows / table     CPU time = 274955 ms,   elapsed time = 19695 ms.
-- Since the time scales quadratically once we have enough rows to efficiently utilize
-- all threads, we can predict 4MM rows / table with the following query:
--      SELECT POWER(4000000 / 64000, 2) * (19. / 3600)
-- 4MM rows / table     Estimated elapsed time = 20 hours
SELECT COUNT_BIG(*), SUM(1.0 * t1.c1 * t2.c1 * t1.c2 * t2.c2)
FROM #t1 t1
JOIN #t2 t2
    /* Add an equijoin logically equivalent to 1=1 in order to enable batch mode
        hash join to replace a loop join for this "cross join" */
    ON t2.one = t1.one
WHERE NOT EXISTS (
    SELECT *
    FROM #t3 t3
    WHERE t3.c1 = t1.c1
        AND t3.c2 = t2.c2
)
    /* Enable batch mode hash join  */
    AND NOT EXISTS (SELECT * FROM #batchMode WHERE 0=1)
OPTION (MAXDOP 16)
GO

Here is the corresponding query plan with 32K rows / table:


Intermittent "access denied" (1045) message from MySQL I'm importing a fairly large (100GB+) database from MySQL to PostgreSQL using pgloader. After a seemingly random amount of time the process dies with the following error:

An unhandled error condition has been signalled:
   Failed to connect to mysql at "localhost" (port 3306) as user "USER": MySQL Error [1045]: "Access denied for user 'USER'@'localhost' (using password: YES)"

That user has access to the database both before and after I get the error, so something goofy is going on. Sometimes it happens fairly early in migration. Sometimes it's near the end. Sometimes it happens while copying a table with lots of rows. Sometimes it happens on small tables.

The migration tool opens a new TCP connection for each table, so on a hunch I tried bumping the nofile limit for the mysql user up to 4096. My next attempt completed successfully, but subsequent tests have all failed.

My tests are failing on Ubuntu 14.04 running MySQL 5.5.41. Running the same process on OS X with MySQL 5.6.23 works correctly.

I can't find any errors logged anywhere (mysql error log, syslog or dmesg).

I've experienced problems like this before. It seems to happen when you don't exactly follow the instructions for creating users. There are normally two solutions - perform the operation as root. The other solution is to (re)create your user carefully - but if this is one-off data loading step, then root should be fine.


Effiiency of multiple OR predicates versus a single range Say I have a SQL statement:

SELECT *
 FROM MyTable
WHERE KeyColumn1 = 1 OR
      KeyColumn1 = 2 OR
      KeyColumn1 =3 OR
      ...
      KeyColumn1 = 10

And another:

SELECT *
 FROM MyTable
WHERE KeyColumn >= 1 AND
      KeyColumn <= 10

Are they actually different and which one would be more efficient?

For 10 values where you have no index, this is such a micro-optimization that you shouldn't worry about it.  The database has to read all the rows, and those reads are going to be less efficient that doing a few comparison operations.

The first can more efficiently be written as:

where KeyColumn in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

This should have the same efficiency as the or version for most database engines.

The better question is which way of describing these values better matches the business problem?  You want the code to be easily understood and maintainable.  Use a list if that makes sense.  Use a range if that makes sense.

If you had hundreds of values, then definitely go with the second approach.  It is cleaner and easier to understand and write and should be faster.

How you write the query does affect optimization, and that in turn depends on the database engine.  Some databases are smarter about optimization than others.  Most of the effect would be on the use of indexes and the effect that other filters in the where clause have on index usage.


Strange upper() behaviour for dateranges in PostgreSQL I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query:

select  upper(('[2005-12-01,2005-12-04]')::daterange)

It returns 2005-12-05 instead of 2005-12-04.

The following query excludes the upper bound by specifying ) instead of ]:

select  upper(('[2005-12-01,2005-12-04)')::daterange)

It returns 2005-12-04.

Isn't the upper() function supposed to return the upper bound limit of a given range? If so, by specifying [2005-12-01,2005-12-04] as the range, it should return the upper bound which is 2005-12-04. It returns 2005-12-05 instead, which is very odd to me.

Any explanation on why this happens? I only know that '[2005-12-01,2005-12-04]'::daterange is translated by PostgreSQL to '[2005-12-01,2005-12-05)'::daterange, but this doesn't explain why 2005-12-05 should be the upper bound when I have explicitly specified 2005-12-04 as the upper bound.

'[2005-11-01,2005-12-04]' is just another string literal for a date range to represent the canonical form '[2005-11-01,2005-12-05)'. The documentation:

  The built-in range types int4range, int8range, and daterange all use
  a canonical form that includes the lower bound and excludes the upper
  bound; that is, [).

Bold emphasis mine.

Else you would have two equal values and upper() would still return a different result, which wouldn't be acceptable.


Return a specific value based on the min between 2 dates in SQL Server I have 2 tables :

CREATE TABLE #Table1(Name varchar(10), Date date, Type varchar(10));
INSERT INTO #TABLE1
VALUES
('aa',    '2018-09-10',  'Client'),
('bb',    '2018-10-12',  'Client'),
('cc',    '2018-10-16',  'Client'),
('dd',    '2018-11-12',  'Client'),
('ee',    '2018-10-15',  'Client'),
('ff',    '2018-11-17',  'Client'),
('gg',    '2018-12-12',  'Client'),
('tt',    '2018-11-11',  'Server'),
('kk',    '2018-12-15',  'Server');

CREATE TABLE #Table2(ID INT, Type varchar(10), DateCreated date);
INSERT INTO #Table2
VALUES
(1,'Client', '2018-11-25'),
(2,'Client', '2018-10-25'),
(3,'Client', '2018-11-17'),
(4,'Client', '2018-09-25'),
(5,'Client', '2018-11-10'),
(6,'Client', '2018-12-05'),
(7,'Client', '2018-11-02'),
(8,'Server', '2018-11-19'),
(9,'Server', '2018-12-10');

Expected result :

ID  Type    Date Created    Name
1   Client  2018-11-25      ff
2   Client  2018-10-25      cc
3   Client  2018-11-17      ff
4   Client  2018-09-25      aa
5   Client  2018-11-10      cc
6   Client  2018-12-05      ff
7   Client  2018-11-02      cc
8   Server  2018-11-19      tt
9   Server  2018-12-10      tt

So basically I want for each ID from Table 2 the name from Table 1 based on the rule : If the type is the same , I want to return the name when the difference between Date Created and Date is min .

For example, for ID 6 , the query should return the name ff because the min difference between 05.12.2018 and any date from Table 1 is 18 . ( Date 17.11.2018 )

Date Created >= Date as a rule .

I've tried to create a SQL query but without results .

Can you please help me with this ?

Given the sample data you provided (in a more regional-friendly format):

CREATE TABLE #Table1(name char(2),[date] date,type char(6));

INSERT #Table1 VALUES
('aa','20180910','Client'),('bb','20181012','Client'),('cc','20181016','Client'),
('dd','20181112','Client'),('ee','20181015','Client'),('ff','20181117','Client'),
('gg','20181212','Client'),('tt','20181111','Server'),('kk','20181215','Server');

CREATE TABLE #Table2(ID int,type char(6),[Date Created] date);

INSERT #Table2 VALUES
(1,'Client','20181125'),(2,'Client','20181025'),(3,'Client','20181117'),
(4,'Client','20180925'),(5,'Client','20181110'),(6,'Client','20181205'),
(7,'Client','20181102'),(8,'Server','20181119'),(9,'Server','20181210');
GO

The following query:

;WITH x AS
(
  SELECT t2.ID, t2.type, t2.[Date Created], t1.name,
    rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.date DESC)
    FROM #Table1 AS t1
    INNER JOIN #Table2 AS t2
    ON t1.type = t2.type AND t2.[Date Created] >= t1.date
)
SELECT ID, type, [Date Created], name
  FROM x
  WHERE rn = 1
  ORDER BY ID;

Produces these results:

ID  type    Date Created    name
--  ------  ------------    ----
1   Client  2018-11-25      ff
2   Client  2018-10-25      cc
3   Client  2018-11-17      dd
4   Client  2018-09-25      aa
5   Client  2018-11-10      cc
6   Client  2018-12-05      ff
7   Client  2018-11-02      cc
8   Server  2018-11-19      tt
9   Server  2018-12-10      tt

Which differ from your expected results on row 3 and row 5, but I think that's a mistake in the expected results.


How to get a count of how often a name/string appears I have a database of devices owned by users, I want see how many users own more than 2 devices, but it would suffice to just get the total count.
Real query:
Select tblAssetUserRelations.Username As 'Besitzer',
  tblAssets.AssetName As 'Name'
From tblAssets
  Inner Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tsysAssetTypes.AssetTypename = 'Windows' Or
    tsysAssetTypes.AssetTypename = 'Apple Mac')
Group By tblAssetUserRelations.Username,
  tblAssets.AssetName
Order By 'Besitzer',
  'Name'

As an more simple example we can use those tables:
Username         DeviceID
-----------------------------
user1            1
user1            2
user2            3
user3            4
user4            5
user4            6

DeviceID         DeviceName
-----------------------------
1                NB001
2                DEV001
3                NB002
4                DEV002
5                NB003
6                DEV003

Expected Result:
Username         Count
user1            2
user2            1
user3            1
user4            2

Any idea how to get the expected result?
EDIT: wrong naming of column

Not sure what the rules are re 1 user owning the same device multiple times, if this should increase the count remove the distinct from within the count aggregate function
create table #x (Username  varchar(20),DeviceName int)
insert #x (username, devicename) values
('user1',1),
('user1',2),
('user2',3),
('user3',4),
('user4',5),
('user4',6)

select username, count(distinct devicename) as devcount
from #x
group by username


Where are the RETURN TABLE columns in the information schema? Say I have a function

CREATE FUNCTION foo (_id INT) RETURNS TABLE (id INT, my_name TEXT, adt_date DATE) ...

I desire to return

_________________
id      | INT
my_name | TEXT
adt_date| DATE

So far I have the following query:

SELECT *
FROM information_schema.routines AS r
LEFT JOIN information_schema.parameters AS p ON r.specific_name = p.specific_name
ORDER BY r.routine_name
       , p.ordinal_position;

To my chagrin, I was unable to find the column names in this query for RETURN TABLE functions.
What am I missing here?

Not that it probably matters in this case, but I am running PG 9.6

Your query seems correct, so I'm not really sure what your question is? I created a function like:

CREATE OR REPLACE FUNCTION twice (x int)
   RETURNS TABLE (y int, z text)
AS $$
BEGIN
    RETURN QUERY
    SELECT x, 'a' UNION ALL SELECT x, 'a' ;
END; $$
LANGUAGE 'plpgsql';

and asked the catalog for:

SELECT r.routine_name, p.parameter_mode, p.parameter_name, p.data_type
FROM information_schema.routines r
LEFT JOIN information_schema.parameters AS p
    ON r.specific_name = p.specific_name
WHERE r.routine_name = 'twice';

routine_name    parameter_mode  parameter_name  data_type
twice           IN              x               integer
twice           OUT             y               integer
twice           OUT             z               text

Is it something that I did miss in your question?


Database Design for Airline Reservation System I'm just a beginner seeking some answers. I'm creating a very simple database system for an airline company, just for learning purposes. Here are my questions:

How to show relationships for entities that I created?
How can I show normalization in a relationship? (at least third normalization form)

Thanks in advance,
Chitru

It seems you are looking for a tool, that verifies the normalization. There is no such tool (at least I can't think of any since it would be almost impossible do write checks for the noralization).

If you want to see if your schema verifies, use simple logic by observing it. If you are not sure, look at the examples in WIkipedia for 1NF, 2NF and 3NF. Does your model have the same structure as these examples? If yes, than you adhere to normalization.

Your model may look like this (showing only PKs and FKs and example)

flight
-------------------
flight_id (PK)
destination
[....]

passengers
-----------------
passenger_id
surname
lastname
nr_of_seats
[....]

payment
--------------------
payment_id
date
price
[....]

-- Your join table, that lets you connect all the data. I assume that the a passenger may book a flight more than once, so I join them together with a certain flight and payment.
reservation
----------------------
flight_id
passenger_id
payment_id
[....]

If you want tools to visuallize your database, pick one from the list that applies to your DBMS. It lets you check how your entities are connected together.


Reset Column Header Index I uploaded some tables to SQL Server Management Studio. Upon inspecting the tables I've found that the column headers area actually values. Is there any way to bump the headers down into a new row and to create new column headers, or will I have to re-upload all of the tables and remember to select the 'has header' option?

Thanks.

Probably the least complicated option would be to select all your data into a new table with the correct column names, filtering out the row of values containing the header values. From there, you can drop the malformed table, and rename the new one.

Example:

CREATE TABLE dbo.wrongo(wrong INT, [column] INT, names INT);
CREATE TABLE dbo.righto(correct INT, [column] INT, names INT);

INSERT dbo.righto ( correct, [column], names )
SELECT wrong, [column], names
FROM dbo.wrongo
WHERE wrongo.wrong <> 'column name'; --this will throw an error, but you get the idea.

DROP TABLE dbo.wrongo;

EXEC sp_rename 'dbo.righto', 'wrongo', 'OBJECT';


MySQL - Optimize subquery in Select, where and order by Im running a query like below.

SELECT DISTINCT c.`id`
    , c.`job-cat`
    , (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) AS jobCount
FROM `jobscat` c
WHERE (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) > '0'
ORDER BY (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) DESC

from the above query this

SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())

repeated thrice, so my query planner also needs to execute this query 3times.

Since alias names will not work in where, so how can I optimize this query instead of running thrice I want to run only one time.

In order by I can use alias to reduce one step.

Turn it around so that you start with the grouping:

SELECT  c.id, c.`job-cat`, x.jobCount
    FROM
    (
        SELECT  mul_category, COUNT(*) AS jobCount
            FROM  jobs
            WHERE  ending >= CURDATE()
            GROUP BY  mul_category
    ) AS x
    JOIN  jobscat AS c  ON c.id = x.mul_category

Indexes:

jobs:  (mul_category, ending)
jobscat:  (id, `job-cat`)


DB2 10.1 Client throws SQL0552N running a script I am trying to run a sql script on a remote database using DB2 10.1 on AIX 6.1.  I have granted my admin user (Adm101) on the database server SECADM. But when i run my script on the remote server with the db2 client (Client101) I get:

SQL0552N "Client101" does not have the privilege to perform operation "CREATE TABLE". SQLSTATE=42502

Adm101 is in listed in the database but Client101 is not.  How can I create a table on the remote client?

You will need to grant Client101 CONNECT and CREATETAB on database in order to be able to create a table. It may also help to grant IMPLICIT_SCHEMA as well. And depending on the version of your client, you may either need to connect with an admin ID first, or grant BINDADD to your ID so it can bind version specific system packages.

db2 connect to mydb as admin101 using "password"
db2 grant connect,createtab,implicit_schema,bindadd on database to user client101
db2 connect reset

then

db2 connect to mydb as client101 using "other_password"
db2 create table .....


Squeezing Maximum Out Of My Server I have a dedicated server only built to hold MySQL databases.

Currently I have a database that has 173 million records and increasing by 5-15+ million records very day.

My question is what and why I should change in my database configuration to use more resources of my machine.

Machine:

2x Intel E5506 @ 2.14 Ghz
2 x 1000GB SATA 3 RAID 1
24 GB RAM

Software:

CentOS 6.4
MySQL Server 5.5

MySQL configuration file:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 10M
max-connections                = 15000
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 18G

# INNODB MULTITHREADED #
innodb_thread_concurrency      = 0
innodb_concurrency_tickets     = 10
innodb_thread_sleep_delay      = 5000
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_replication_delay       = 5000
innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc          = ON
innodb_doublewrite             = OFF

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

I have noticed that my queries are getting slower.

Some update queries take about 48-50 seconds.

Here's how it looks:

[root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log
# Time: 131113 17:09:48
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 61.013294  Lock_time: 0.000409 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380588;
UPDATE b SET nf=0, lt=0, ud=1384380501, a='Trademarks'
  WHERE parent_id=36019263 AND link=3780235;
# Time: 131113 17:09:49
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 18.145112  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380589;
SELECT id FROM b WHERE parent_id=62921670 AND link=4808881;
[root@db-1 ~]#

To start with, more interesting than the number of records is the size on disk, which we can then compare to your available memory.

Consider the following settings:

innodb_flush_log_at_trx_commit = 1(2)?

This variable appears twice in your cnf file. You probably meant to give it the value 2, which make for less I/O. But you may loose 1 second's worth of transactions upon failure.

You also have:

innodb_doublewrite             = OFF

Which is risky. True, it will make for less I/O, but may also lead to corruption upon failure. I only set this on servers I can spare and do not mind losing.

Set

query-cache-type               = 0

Enough has been written on the query cache that I'll not repeat here; it is an old technology and does not scale to today's machines.

Do yourself a favour and set

long_query_time = 1

Since the 10 second default is way too high to catch the bad guys.

I know nothing about the usage of your DB, but I'm guessing here that

max-connections                = 15000

is waaaay out of proportion. Will you really have 15000 concurrent connections? If you will, then a 18G for innodb_buffer_pool_size is way too high. Connections have their own memory.

All the above off the top of my head. Certainly need more information about your use case.


How to identify queries that cause high Logical I/O? I need some help to identify SQL queries that could suddenly increase the SQL Server Logical I/O.
Can someone please help to provide some advice? This was from SQL Server 2017

Not enough rep to comment, so posting here.  Below query should get you started:
SELECT
      qs.total_logical_reads
    , qs.total_logical_writes
    , qs.total_physical_reads
    , qs.last_logical_reads
    , qs.last_logical_writes
    , ISNULL(OBJECT_NAME(st.objectid), 'Ad-hoc Query') [Proc Name]
    , st.text
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
ORDER BY (qs.total_logical_reads + qs.total_logical_writes) DESC

Note - the totals are cumulative over time, so careful of the "gotchas" of queries which have been using little IO each execution but have been executed frequently over longer period of time versus queries which just started using more IO and/or executing more frequently. Check out more info about sys.dm_exec_query_stats and other potentially useful queries on the MS doc.


ORA-04031: unable to allocate bytes of shared memory ("","","","") I am receiving this error in a development database.
It happens on some specific queries (simple inserts and deletes via .NET application).
The database is used by one user, and error happens regardless for how long the database was running.

The only memory related parameter which is set is: MEMORY_TARGET=1.2G

Oracle 11.2 x64 Standard Edition One
Windows Server 2008 R2

Your application is probably not using bind variables, which has caused the SGA to become fragmented (it fills up with multiple copies of SQL that use literals instead of binds).

You can temporarily fix the problem by flushing the shared pool with:

alter system flush shared_pool;

There's a workaround you can try to stop it happening in the future.

Turn on cursor sharing, then bounce the database:

ALTER SYSTEM SET cursor_sharing='SIMILAR' SCOPE=BOTH;

The only real way of fixing this is to rewrite your application so that it uses bind variables.


SQL design for hierarchical structure where parent unit should not exist without subunits I'm new to SQL so I wasn't sure what to even search for this besides looking at hierarchical structures which don't seem to be exactly my use case.

I'm building an app where a Post contains multiple Pages, each containing multiple Videos and Images. I currently have a table for a Post, a Page, a Video, and an Image where a Page contains a reference to its post id and a Video or Image each contain a reference to their page id. I thought this would work, but I need to insert the Post object in order to get its id to store in each Page object, but a Post object should not exist without pages. And yet a Page should not exist without belonging to a Post object. So I'm not sure how to structure these tables.

Are you saying that you have troubles with FOREIGN KEYs.  If so, don' t use them.

If you are saying that a POST exists briefly without any PAGEs, then put the generation of both the POST and the PAGEs inside a transaction (BEGIN...COMMIT).


Kill all sessions for ONE user in MS SQL So we have a bad external application that connects to the database, executes a stored procedure and gets an answer.

It never releases the database connection.

I need a script that either kills all of the sessions for one user
or
I need a script that kills all of the sessions that have been inactive for x hours for a particular database

Anyone have something like this?

There's not really a reliable way to pin sessions to a specific database (sysprocesses has this, but it relies on an active query's database context, and dm_exec_sessions has authenticating_database_id, but this relies on the connection string, login's default database, etc).

That said, this seems to meet all of your other requirements, attempting to kill all sessions using app_name, connecting as login_name, and who haven't issued a request in the last 5 hours:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'KILL ' + CONVERT(VARCHAR(11), session_id) + N';'
FROM sys.dm_exec_sessions
  WHERE [program_name] = N'app_name'
  AND login_name = N'login_name'
  AND last_request_start_time < DATEADD(HOUR, -5, SYSDATETIME());

EXEC sys.sp_executesql @sql;

If all the app does is call that one stored procedure and never does anything else at all, you could perhaps look at DBCC INPUTBUFFER for each spid, but that becomes very tedious very quickly.


getting error when i use merge statement I am using sql 2008 r2. when i use merge statement i am gettig error
when i execute following code:

merge products as trgt
using updatedproducts as src
      on (trgt.productid = src.productid)
when matched and trgt.productname <> src.productname or trgt.rate <> src.rate then
     update set trgt.productname = src.productname,
     trgt.rate = src.rate
when not matched by trgt then
     insert (productid, productname, rate) values (src.productid, src.productname, src.rate)
when not matched by src then
     delete output $action,
     deleted.productid as trgtproductid,
     deleted.productname as trgtproductname,
     deleted.rate as trgtrate,
     inserted.productid as srcproductid,
     inserted.productname as srcproductname,
     inserted.rate as srcrate;

select @@rowcount;
go

I am getting error like this:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Why am i getting this error?

Try using the keywords TARGET and SOURCE.

MERGE products AS target
  USING updatedproducts AS source
  ON (target.productid = source.productid)
  WHEN MATCHED and target.productname <> source.productname OR
    target.rate <> source.rate
    THEN UPDATE
     SET
      target.productname = source.productname
     ,target.rate = source.rate
  WHEN NOT MATCHED BY TARGET
    THEN INSERT
      (productid
      ,productname
      ,rate)
     VALUES
      (source.productid
      ,source.productname
      ,source.rate)
  WHEN NOT MATCHED BY SOURCE
    THEN DELETE
  OUTPUT
    $action
   ,deleted.productid AS trgtproductid
   ,deleted.productname AS trgtproductname
   ,deleted.rate AS trgtrate
   ,inserted.productid AS srcproductid
   ,inserted.productname AS srcproductname
   ,inserted.rate AS srcrate;
SELECT
  @@rowcount;

Should work like a charm


Database schema for inventory management for multiple people I am setting up a Postgres database that keeps track of items that entities in my system own. Each incoming datum says that Entity Q has N of Item X.

My first instinct is to have a separate table for each entity (multi-tenant). But I have read a number of posts advising against that due to scalability concerns.

What is the most effective way to design a database that stores a many-to-many relationship along with quantities? Thanks.

Additional information:

My information is coming through in JSON format from an API. For example:

{
    "data": [
        {
            "entity": "John",
            "item": "apple",
            "quantity": "5"
        },
        {
            "entity": "Mary",
            "item": "apple",
            "quantity": "2"
        },
        {
            "entity": "John",
            "item": "orange",
            "quantity": "6"
        }
    ]
}

My system must be able to tell each entity what items they have and how many (John has 5 apples and 6 oranges). It does not need to show which entities own any particular item (John and Mary both own apples).

Since each entity is independent I think it might make sense to keep all information in separate tables based on the type of entity. The main concern with this is that many entity tables might contain the same item (such as apples). But I do not think there is a way to combine these duplicates because the quantity of each item is mandatory.

I hope this clarifies some of the uncertainties surrounding my problem. Thank you for your support.

I suggest this data model:

CREATE TABLE entity (
   entity_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE item (
   item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE inventory (
   entity_id bigint NOT NULL REFERENCES entity (entity_id),
   item_id bigint NOT NULL REFERENCES item (item_id),
   qualtity integer NOT NULL CHECK (quantity > 0),
   PRIMARY KEY (entity_id, item_id)
)

CREATE INDEX ON inventory (item_id);

If you know that the entity and item names can never change and are unique, you can use the name as natural primary key.

Whenever you store a new datum, you do it as follows:

INSERT INTO inventory (entity_id, item_id, quantity)
   VALUES (42, 123, 12)
ON CONFLICT DO UPDATE
   SET quantity = inventory.quantity + 12;

That will allow efficient searches and joins.

Don't create multiple tables. If the tables will become large, or you need an efficient way to delete old items, you could use partitioning. How that is best done depends on your queries and the number of distinct entities.


SSRS --> .xlsx file --> SQL ETL -- "External table not in expected format" error, but only when files have not been opened Long story short, I've had to use a workaround to importing data directly from SharePoint due to SSIS drivers not working with it.  My current workaround is almost good enough, but it still has some issues.

I started out with a regularly running report from SSRS that dumps into Excel 2003 files.  Turns out sometimes data completely gets left off of certain columns when using the SQL Import Wizard, so I switched to .xlsx files.  Now the data, when the import actually works, shows up, but there's a unique quirk with this in the .xlsx files where the import wizard throws the "External table not in expected format error" UNLESS the .xlsx files are manually opened, then immediately saved and closed.  After that, the import works without issue.

What could be causing this particular quirk, and is there any way to systematically get around it?  If this were a one-time deal, I wouldn't be taking issue, but this data will have to be regularly refreshed.

If it helps... .

the server this is on is a 2012 R2 Datacenter
running SQL 2014
no Microsoft office programs are installed on this server, just drivers
SSRS is version 2012 (on a different server)

There are two installations for Microsoft Office Access database engine; 2007 and 2010, which I needed to install to get the Excel --> SQL push to even work in the first place.

Here are the possible reasons and solutions I can think of:

The Excel file instance may be getting opened in protected mode. Opening, saving, and closing them is solving this issue temporarily. To fix it permanently, you need to add the Excel file location as a trusted one in the Trust Center.

To do this, you need to go to excel>>file>>options>>Trust center>>trust center settings>>Trusted locations. and add the location of your Excel file there.
Another thing that you can try is changing the file block settings in the same options window for Excel 2007, and later file types according to your needs.
The last reason that I can think of is Excel is unable to identify the type of value being inserted in the columns. This problem is resolved by entering some default sample values in the first row for each column. This workaround helps Excel in deciding what kind of value is getting entered and most of the time fixes the format issue.

For example:

column1_has_to_get_integer_value then insert any integer value in first row.
column2_has_to_get_string_value then insert any string value in first row.


sql join with laravel query builder I have a transactions table with the following field:

Transactions
ID | amount | branch_id | supplier_id
1  | 1,200  |    2      |  1

Subsidiary
ID  | Name      | Type
1   | supplier1 | 0
2   | branch_2  | 1

I want to display this in my view with the id value properly so I joined the following table (I'm using laravel query builder)

$transactions = DB::table('transactions_table')
->leftJoin('subsidiary_table','transactions_table.supplier_id','=','subsidiary_table.id')
->get(array('transactions_table.id',
'transactions_table.amount',
'subsidiary_table.name as branch',
'subsidiary_table.name as supplier'));

I'm confused on how can I get it to display like:

Display View
ID  |   Ammount  |  Supplier  | Branch
1   |   1,200    |  Supplier1 | branch_2

Thanks for clarifying..

I don't know if you want to put it in laravel, but, here's the query:

SELECT
 t.ID,
 t.amount,
 s.Name as Supplier,
 sb.Name as Branch
FROM Transactions as t
JOIN Subsidiary as s ON (s.ID=t.supplier_id)
JOIN Subsidiary as sb ON (sb.ID=t.branch_id)
WHERE t.ID = 1;

Try it.

PD: You can change the JOIN syntax for  LEFT JOIN if you want to return the t.ID = 1 record from Transactions even if one column don't match in Subsidiary's JOINs.


Update postgres json value by other attribute I'm using PostgreSQL 9.6 and
I got the following table:
Cars
ID          Name        Data
 1           abc        {"color":"red","wheels":4,"my-value":false}
 2           def        {"color":"blue","wheels":8}
 3           xyz        {"color":"yellow","my-value":true}

I want to extend the Data JSON object by adding another boolean attribute called "new-attr" which should get the exact value of "my-value" of the JSON field.
for example, if "my-value" in ID 1 is false, then "new-attr" will be false.
And if not set, do not add it at all (as in number 2).
I tried the following but it didn't help:
UPDATE cars SET data=jsonb_set(data,'{new-attr}',data->'my-value')

I expect to get the following JSON :
 ID          Name        Data
1           abc        {"color":"red","wheels":4,"my-value":false,"new-attr":false} // same value
2           def        {"color":"blue","wheels":8} // no change
3           xyz        {"color":"yellow","my-value":true,"new-attr":true} // same value true

Any suggestions ?

The concatenation operator || will do that:
update the_table
  set data = data || jsonb_build_object('new-attr', data -> 'my-value')
where data ? 'my-value'; --<< only update rows that contain the key

The ? operator tests if the key is present in the JSON value.

If your column is defined as json (rather than jsonb which it should be), you probably need to cast it, to make the || operator work properly: data::jsonb || ...


How to avoid listing FIELD/IN twice? I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?

Try using the FIELD function's return value and make sure it is not zero:

SELECT * FROM
(SELECT *,field(foo,'this','that','bar','baz') fieldfoo FROM t) A
WHERE fieldfoo > 0;


What is the actual behavior of compatibility level 80? Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected.

As far as I understand compatibility modes, it is about the availability and support of certain language structures between the various versions of SQL Server.

It does not affect the inner workings of the database engine version. It would try to prevent use of features and constructs that were not yet available in earlier versions.

I just created a new database with compat level 80 in SQL Server 2008 R2. Created a table with a single int column and populated it with a few rows.

Then executed a select statement with a row_number() function.

My thought was, since the row_number function was only introduced in 2005, this would throw an error in compat 80 mode.

But to my surprise this worked fine. Then, surely, the compat rules are only evaluated once you 'save something'. So I created a stored proc for my row_number statement.

The stored proc creation went fine and I can perfectly execute it and obtain results.

Could someone help me to better understand the working of compatibility mode? My understanding is obviously flawed.

From the docs:

  Sets certain database behaviors to be compatible with the specified version of SQL Server.
  ...

  Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

In my interpretation, compatibility mode is about behavior and parsing of syntax, not for things like the parser saying, "Hey, you can't use ROW_NUMBER()!" Sometimes the lower compatibility level allows you to continue getting away with syntax no longer supported, and sometimes it prevents you from using new syntax constructs. The documentation lists several explicit examples, but here are a few demonstrations:

Passing built-in functions as function arguments

This code works in compatibility level 90+:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

But in 80 it yields:

  Msg 102, Level 15, State 1Incorrect syntax near '('.

The specific problem here is that in 80 you aren't allowed to pass a built-in function into a function. If you want to stay in 80 compatibility mode, you can work around this by saying:

DECLARE @db_id INT = DB_ID();

SELECT *
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

Passing a table type to a table-valued function

Similar to the above, you can get a syntax error when using a TVP and trying to pass it to a table-valued function. This works in modern compat levels:

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

However, change the compatibility level to 80, and run the last three lines again; you get this error message:

  Msg 137, Level 16, State 1, Line 19
  Must declare the scalar variable "@foo".

Not really any good workaround off the top of my head, other than upgrading the compat level or getting the results a different way.

Using qualified column names in APPLY

In 90 compatibility mode and up, you can do this without problem:

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:

  Msg 102, Level 15, State 1Incorrect syntax near '.'.

ORDER BY an alias that happens to match a column name

Consider this query:

SELECT name = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.name;

In 80 compatibility mode, the results are as follows:

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

In 90 compatibility mode, the results are quite different:

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

The reason? In 80 compatibility mode, the table prefix is ignored entirely, so it is ordering by the expression defined by the alias in the SELECT list. In newer compatibility levels, the table prefix is considered, so SQL Server will actually use that column in the table (if it is found). If the ORDER BY alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:

SELECT myname = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.myname;

The result is ordered by the myname expression in 80, because again the table prefix is ignored, but in 90 it generates this error message:

  Msg 207, Level 16, State 1, Line 3
  Invalid column name 'myname'.

This is all explained as well in the documentation:

  When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.
  For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.
  SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
  When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.

ORDER BY something not in the SELECT list

In 90 compatibility mode you can't do this:

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

Result:

  Msg 104, Level 16, State 1
  ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

In 80, though, you can still use this syntax.

Old, icky outer joins

80 mode also allows you to use the old, deprecated outer join syntax (*=/=*):

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:

  Msg 4147, Level 15, State 1
  The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

In SQL Server 2012, this is no longer valid syntax at all, and yields the following:

  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near '*='.

Of course in SQL Server 2012 you can no longer work around this problem using compatibility level, since 80 is no longer supported. If you upgrade a database in 80 compat mode (by in-place upgrade, detach/attach, backup/restore, log shipping, mirroring, etc.) it will automatically be upgraded to 90 for you.

Table hints without WITH

In 80 compat mode, you can use the following and the table hint will be observed:

SELECT * FROM dbo.whatever NOLOCK;

In 90+, that NOLOCK is no longer a table hint, it's an alias. Otherwise, this would work:

SELECT * FROM dbo.whatever AS w NOLOCK;

But it doesn't:

  Msg 1018, Level 15, State 1
  Incorrect syntax near 'NOLOCK'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Now, to prove that the behavior is not observed in the first example when in 90 compat mode, use AdventureWorks (making sure it's in a higher compat level) and run the following:

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

This one is particularly problematic because the behavior changes without an error message or even an error. And it's also something that the upgrade advisor and other tools might not even spot, since for all it knows, that's a table alias.

Conversions involving new date/time types

The new date/time types introduced in SQL Server 2008 (e.g. date and datetime2) support a much larger range than the original datetime and smalldatetime). Explicit conversions of values outside the supported range will fail no matter what the compatibility level, for example:

SELECT CONVERT(SMALLDATETIME, '00010101');

Yields:

  Msg 242, Level 16, State 3The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

However, implicit conversions will work themselves out in the newer compatibility levels. For example this will work in 100+:

SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');

But in 80 (and also in 90), it yields a similar error as above:

  Msg 242, Level 16, State 3The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Redundant FOR clauses in triggers

This is an obscure scenario that came up here. In 80 compatibility mode, this will succeed:

CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;

In 90 compatibility and higher, this no longer parses, and instead you get the following error message:

  Msg 1034, Level 15, State 1, Procedure tx
  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

PIVOT/UNPIVOT

Some forms of syntax won't work under 80 (but work just fine in 90+):

SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;

This yields:

  Msg 156, Level 15, State 1
  Incorrect syntax near the keyword 'for'.

For some workarounds, including CROSS APPLY, see these answers.

New built-in functions

Try using new functions like TRY_CONVERT() in a database with compatibility level < 110. They are simply not recognized there at all.

SELECT TRY_CONVERT(INT, 1);

Result:

  Msg 195, Level 15, State 10
  'TRY_CONVERT' is not a recognized built-in function name.

Recommendation

Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.


Order of composite key for best lookup performance I have a table with a composite key made up of two fields: A and B.

A is like a user ID or machine ID, it is globally unique. B has only a few possible values (less than 5). The composite key will look like (user1 + B1), (user2 + B1), (user3 + B1), (user1 + B2) etc. I don't want to split the data into a few tables by B since the number of rows has not reached that amount yet.

For optimal lookup performance, what should be the order of the fields in the primary index? AB or BA?

For a query that is doing an equality seek on both columns it doesn't matter. You'd need to look at your other queries to see how they would benefit from either possibility.

An index on (A,B) could also serve for an equality seek on A but not an equality seek on B for example.

Also if you had ORDER BY A,B on a query then an index on A,B could avoid a sort.

You say you have no such queries and all are strict equality using both columns.

As a tiebreaker you might consider whichever leading column would make the most useful histogram but for the query pattern you describe where neither A or B are ever queried except together to look up a key in an unique index neither will be useful.

So in your case it doesn't matter.

BTW: There is a persistent belief that putting the most selective column first is required.

This doesn't make any difference here. Either way SQL Server will be able to seek on both columns. Contrary to the StackOverflow answer linked in the comments there is no big difference in efficiency either way (I guess there might be a minor benefit in CPU time by checking the column most likely to differ first and thus potentially avoiding the need to do a comparison for the second key column in the composite key but even if true this would be a micro optimisation)

Sometimes this will make sense for other queries in your workload though. E.g if there are queries against both A and B individually and the index wasn't covering for either of them but one of the columns was selective enough to make a plan with lookups worthwhile then putting the most selective first would make sense in that instance.


Switch MySQL lag slave to new master and keep lagging (5.1) We have the following scenario:

Set of slaves replicate from the master, separate standby master (a.k.a slave with binlogs) replicates from the same master; lag slave, powered by pt-slave-delay, replicates from the same source.

We also utilise mha4mysql to switch slaves from one master to another in case of need.

All would be dandy, if not for lag slaves.

Problem

Let's say lag slave is running 12 hours behind master.
Switch lag slave from Master to Standby Master in such a way that failover occurs immediately (i.e. CHANGE MASTER is executed on lag slave the same time as on regular slaves) and that lag slave keeps lagging 12 hours.

Issues

While I know that slave keeps track of master binlog position in its own relay log, I can calculate master position to 12 hours in the past, however, as far as I understand it Standby Master (aka slave with binlogs) has no relation between own relay logs and own binlogs.

Question

Is there a reliable way to calculate position of the Standby Master we need to switch to (discarding the rest of relay logs on lag slave, obviously) and restart replication from 12 hours behind, but on the new master?

NOTE we are running MySQL 5.1, so 5.5 (or 5.6 with GTIDs) aren't a solution for now unfortunately.

Thank you.

DISCLAIMER : Not a user of mha4mysql.

SUGGESTION #1

Hang the Lag Slave off from the Standby Server. How?

Step 01: Stop all Writes to the Master
Step 02: Run SHOW MASTER STATUS; on the StandByMaster
Step 03: Run mysqlbinlog against the binlog from Step 02
Step 04: Acquire timestamp from given position
Step 05: Subtract 43200 sec (12 hrs) from the Timestamp acquired
Step 06: Locate position just before timestamp acquired
Step 07: Using StandByMaster Host IP, Log File and Position, run this on Lag Slave

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='...',MASTER_LOG_FILE='...',MASTER_LOG_POS=...;
START SLAVE;

All manual steps. I'm sorry.

SUGGESTION #2

Setup the Lag Slave under one of the Slaves PERMANENTLY. You'll never have worry about moving the Lag Slave between Masters again.

SUGGESTION #3

Setup the Lag Slave under a Blackhole Slave. You'll never have worry about moving the Lag Slave between Masters again.

I hope these suggestions helped or at least got you thinking.


Benefits of using prepared statements in stored procedures? Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)?

Does the engine have any way to keep the prepared statement in memory or is it a wash?

If the answer is depending upon environment, I interested in mysql with innodb.

So yes as you have mentioned about SQL injection the other advantage is what you guessed.
Quoting from documentation:

Using prepared statements with placeholders for parameter values has the following benefits:

Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of
almost-identical statements, with only changes to literal or variable
values in clauses such as WHERE for queries and deletes, SET for
updates, and VALUES for inserts.

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

So yes it does cache the prepared statements and routines.


Postgres How to securely initialize the database? I'm initializing a Postgres DB and would like to know what is the best way to do it.
Objectives:

We would like to use default database (postgres) unless there's a strong reason not to do.
We would like to use default schema (public) unless there's a strong reason not to do.
We would like to apply the Principle of Least Privilege.
No one (apart from those created below) should be able to anything in the DB.

One thing I know, public is public, fix it:
REVOKE ALL ON DATABASE postgres FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

Is it enough? Did I miss anything? Do I need to revoke anything else?
I read somewhere, creating some object automatically grants some privilege to PUBLIC (sorry for too many somes, I hardly remember the exact information). Should I be worried about this?

And then I create some roles and grants appropriate privileges:
-- Creating DB admin user to be used only by Flyway
CREATE USER db_admin WITH PASSWORD passwd CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE postgres TO db_admin;
GRANT ALL ON SCHEMA public TO db_admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO db_admin;

-- Creating DB application users with read/write permissions
CREATE ROLE db_writers;
GRANT CONNECT ON DATABASE postgres TO db_writers;
GRANT USAGE ON SCHEMA public TO db_writers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;

-- Must run it as db_admin
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_writers;

CREATE USER db_writer WITH PASSWORD passwd IN ROLE db_writers;

-- Creating DB application users with read-only permissions
CREATE ROLE db_readers;
GRANT CONNECT ON DATABASE postgres TO db_readers;
GRANT USAGE ON SCHEMA public TO db_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO db_readers;

ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON TABLES TO db_readers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON SEQUENCES TO db_readers;

CREATE USER db_reader WITH PASSWORD passwd IN ROLE db_readers;

Any comment on the above?
I couldn't make it working with db_admins role and db_admin user. Everytime db_admin creates some object, I have to change the object owner to db_admins. I don't like it. (If there's a better way to do it, it'd be awesome. If not, we're good with one user without any role.)

Your SQL script looks good, except that you shouldn't use

CREATE USER ... PASSWORD '...';

to give a user a password. The problem is that the password goes across the line in clear text and might end up in the server log.

Either hash the password on the client side and send it that way, or use psql's \password command to change the password interactively.

You should not use the postgres database for user data. It is intended for administrative purposes only (a place to connect if you want to run CREATE DATABASE or DROP DATABASE).

You can use the public schema; the only problem I see is that if you create an extension, the extension's objects end up in the same schema as the application objects, which I believe is not nice. I prefer to create a schema for the application and leave public for extension owned objects.

I am not sure what you mean when you say that you cannot get it to work with db_admins. I assume that you want to have a group of administrators so that each of them can ALTER and DROP the objects the others created.

That is not possible, because only the object owner (and a superuser) can do that. If you want several administrative users, you could proceed as follows:

Only db_admins (a NOLOGIN role) can create objects in the schema.
The administrative users are created with NOINHERIT and belong to db_admins.
Whenever an administrative user wants to create or modify an object, he or she has to first run:

SET ROLE db_admins;

This is a bit cumbersome, but a solution similar to not allowing remote logins as root on UNIX and using su to become root instead.


Join multiple tables without duplicated results and without UNION in PostgreSQL Lets assume we have two tables A and B in PostgreSQL:
A

title
first_group_id
second_group_id

My Title
1
2

B

group_id
element_name

1
First

1
Second

1
Third

2
Fourth

2
Fifth

I want to get the following result:

A.title
B0.element_name
B1.element_name

My Title
First

My Title
Second

My Title
Third

My Title

Fourth

My Title

Fifth

If I query something like the following - I will get 6 rows instead of 5 cause all rows from B0 will be mixed with all rows from B1 and the number of result rows will grow in progression if B0 or B1 have more rows:
SELECT A.title, B0.element_name, B1.element_name
LEFT JOIN B B0 ON A.first_group_id = B0.group_id
LEFT JOIN B B1 ON A.second_group_id = B1.group_id
FROM A;

A.title
B0.element_name
B1.element_name

My Title
First
Fourth

My Title
First
Fifth

My Title
Second
Fourth

My Title
Second
Fifth

My Title
Third
Fourth

My Title
Third
Fifth

I can achieve the desired behavior by using UNION but I wonder if there is a more efficient way to do that? Especially when it's required to make more than two JOINs in the same manner.

One neat option to avoid querying the tables twice is to unpivot A first.
You need some CASE expressions because you also want to split out element_name back into different columns.

Not so good at Postgres, I may have the syntax slightly off

SELECT
    A.title,
    CASE WHEN A.first_group_id  = v.group_id THEN B.element_name END AS element_name0,
    CASE WHEN A.second_group_id = v.group_id THEN B.element_name END AS element_name1
FROM A
INNER JOIN LATERAL (VALUES
    (A.first_group_id),
    (A.second_group_id)
) AS v(group_id)
LEFT JOIN B ON v.group_id = B.group_id;


How to make inet_server_addr() return localhost in spite of ::1/128 How to make inet_server_addr() return an name as IPv4?

@cavila's answer in comments also answers the question "Why does select inet_server_addr() || '' return a result like 192.168.44.195/32?"

inet is a data type. To suppress the number of bits and get a text IP like '192.168.51.195', use select(host(inet_server_addr())).


how to identify the transactions that are causing the transaction log to grow? I have a database of 20 GB which insists in having its transaction log over 7GB.

when I used this script to find out the size of the biggest objects in that database, I see they are relatively small.

I have been using the default trace to see when this transaction log has been autogrown, but I found none.

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

I have also tried some scripts from here, to check what transactions are filling the log, but could not find any.

I believe there must be some very long transactions going on at the same time, or at least one of more long transactions being executed.

How can I check for these (long transactions in the database)?

In LIVE this is a full recovery mode database, part of alwayson.
In TEST this is a simple recovery mode database, but because all the jobs are there, the log grows to 7GB all the same.

Try this extended event (change the filename path accordingly).  I've used it in the past to help me track down what was causing unexpected growth in my data and log files.

CREATE EVENT SESSION [DB Size Tracking] ON SERVER
ADD EVENT sqlserver.database_file_size_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\DB Size Tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

You'll need to shrink your log on the TEST server, then let the jobs run so that it grows again.  This extended event will then log the SQL text of what causes the autogrowth event.


HA and Automatic Failover with PostgreSQL The most popular third-party tools are pgpool and regmgr.

pgpool can do HA and automatic failover, regmgr can also do replication and automatic failover. Some articles show using both of them to do in the cluster. Why? What's the best practices go to the goal?

pgPool is a client tool that can reroute client connections to the surviving server aftet the primary has died. However, without further activity, that surviving server will be a streaming replication standby, that is a read-only server.

You also need some software that can reliably detect that the primary server is down and promote a standby server. Such a clusterware is an integral component of a high-availability setup.

Probably Repmgr can serve that purpose; typically, Patroni is used.


MS SQL 2014 AlwaysOn High Availibiliity, Error 15023, not about to connect on failover I setup my first AlwaysOn Cluster following the directions here, with some help from the documentation available on bretozar.com. When I started to test the Listener using the UDL process here I wasn't able to connect with any of the user accounts which were built while building the cluster.

I then signed into the current Primary cluster server (server-A) and created a local test user. I was able to get a Successful Connection while accessing though the Availability Group Listener. However When I failed the group over to (server-B) I can no longer connect to the database.

I didn't see the test user in the list of users for that database on server B, now the primary, and when I try to make it again I get the 15023 Error "User already exists." I didn't have any success with the recommended fix

EXEC sp_change_users_login 'Auto_Fix', 'myUser'

The Test-users is also configured with:
Server Roles = public
User Mapping = Always_On_DB
Securables = Server_A (Server-B is not listed as an option, and the Availability Cluster does not save is it is selected.

I can re-add the Test-user to Server-B, I can access all other databases. The 15023 error appears when I try to add the Always_on_DB to the user properties.

Any help would be appreciated, as right now the cluster only allows access when the primary server is on Server-A.

It's likely that your Test-User account has different SIDS on the primary and replica servers.  The best way to resolve this is drop the Test User on the Secondary, generate a create user statement from the primary with the proper SID (e.g. sp_revlogin, dbatools.io, or another tool of your choice) and then run that generated CREATE USER statment against the secondary.  This will create a user that has the same SID on both replicas so you won't get this error going forward.

Another option, and one that I would recommend instead, is to convert this database to a Partially Contained Database so that all SQL Logins you create with the database are automatically transferred over to any replicas you setup.  This is a very handy feature (if the limitations don't adversely affect you) for databases in Availability Groups and will help minimize the administrations headaches that come with them.


How to get readonly access to mysql files via g+w permission 750 or 640? The data-dir itself has permissions 700 thereby limiting access to the mysql user.

I want other folks who I add to the mysql group to be able to read binlogs and download the *.frm, *.MYI, *.MYD files to their local computer.

My first thought is to run chmod g+rx data-dir.

The database directories and table/binlog files within data-dir have permissions with 770 / 660. So I have to chmod g-w on all of those to prevent accidental tampering or removal of live data.

Unfortunately, as new binlogs are created, and the create database or create table command is used, the g+w permission is automatically added. How can I change this functionality?

Well, I found online that the environment variable UMASK and UMASK_DIR must be set, regardless of what the command umask will output.

One must add export UMASK=0640 and export UMASK=0750 to the top of their startup script, immediately before calling mysqld_safe.

The leading zero is important to switch to the octal numbering system.

The first thing I tried was 0027, but that produced files with permissions u=rw,g=w,o=rwx so that is what prompted me to invert the permissions in the environment variable to 0750 (and 0640) to produce the desired results.

I hope my self-answer will help a future visitor. :-)


Fetch nested results as JSON PostgreSQL 9.2 Consider the following tables

Device
--------
id
name
type
--------

components
--------
id INT
type VARCHAR
--------

Manufacturers
-------------
id INT
name VARCHAR
country VARCHAR
-------------

Device_components
-----------------
deviceid REFERENCES Devices(id)
componentid REFERENCES Components(id)
-----------------

Component_Manufacturers
-----------------------
componentid REFERENCES Components(id)
manufacturerid REFERENCES Manufacturers(id)
-----------------------

I want to query the database to return something like this:

{
 "id": 1,
  "name": "phone",
  "components": [
    {
      "id": 1,
      "type": "screen",
      "manufacturers": [
       {
         "id": 1,
          "name": "a",
          "country": "Germany"
        }
      ]
   },
   {
     "id": 2,
      "type": "keyboard",
      "manufacturers": [
        {
         "id": 1,
          "name": "a",
          "country": "UK"
        }
     ]
   }
  ]
}

So far I'm selecting from each table individually and then assemble the JSON object in my application.

Here's some sample data:

CREATE TABLE IF NOT EXISTS Devices
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Components
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Manufacturers
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL,
    COUNTRY VARCHAR(40)
);

CREATE TABLE IF NOT EXISTS Device_components
(
DeviceID INT REFERENCES Devices(ID),
ComponentID INT REFERENCES Components(ID)
);

CREATE TABLE IF NOT EXISTS Component_manufacturers
(
ComponentID INT REFERENCES Components(ID),
ManufacturerID INT REFERENCES Manufacturers(ID)
);

INSERT INTO Devices (NAME) VALUES ('phone');
INSERT INTO Devices (NAME) VALUES ('tablet');
INSERT INTO Devices (NAME) VALUES ('pc');

INSERT INTO Components (NAME) VALUES ('mouse');
INSERT INTO Components (NAME) VALUES ('camera');
INSERT INTO Components (NAME) VALUES ('screen');

INSERT INTO Manufacturers (NAME,Country) VALUES ('foo','france');
INSERT INTO Manufacturers (NAME,Country) VALUES ('bar','spain');
INSERT INTO Manufacturers (NAME,Country) VALUES ('baz','germany');

INSERT INTO Device_components VALUES (1,2);
INSERT INTO Device_components VALUES (1,3);
INSERT INTO Device_components VALUES (2,2);
INSERT INTO Device_components VALUES (2,3);
INSERT INTO Device_components VALUES (3,1);
INSERT INTO Device_components VALUES (3,2);
INSERT INTO Device_components VALUES (3,3);

INSERT INTO Component_manufacturers VALUES (1,1);
INSERT INTO Component_manufacturers VALUES (1,2);
INSERT INTO Component_manufacturers VALUES (1,3);
INSERT INTO Component_manufacturers VALUES (2,2);
INSERT INTO Component_manufacturers VALUES (3,3);

Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.

It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the record pseudo-type.

Here's what I came up with:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,
        d.name,
        json_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT
            c.id,
            c.name,
            json_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
        GROUP BY c.id
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

The general idea here is, at every level of object nesting where you're producing an array of objects, use json_agg in a group_by. The json_agg function implicitly calls row_to_json to transform a rowtype into json. In the SELECT clause specify aliases for the synthetic columns so the json key names are correct when the row is fed into the outer level.

As the outer level isn't aggregated, use row_to_json over a subquery instead of using json_agg. If you want a single json result instead of a set of json rows, you can change row_to_json to json_agg in the outer level.

I've only tested on 9.3, as that's what I have installed and sqlfiddle seems to be having some issues.

Update: Unfortunately json_agg doesn't exist on 9.2, it was added in 9.3. However, this is a simple enough case that you can just use array_agg instead, so this should work in 9.2:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,
        d.name,
        array_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT
            c.id,
            c.name,
            array_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
        GROUP BY c.id
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

Ideally PostgreSQL would support this sort of query more naturally, without the subqueries, but I think we'd need a new join type, or at least some functions to use with lateral queries. It all plans out into a civilised query plan anyway (though it'd be better with suitable indexes):

                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on r  (cost=1050.26..1282.94 rows=720 width=106)
   ->  GroupAggregate  (cost=1050.26..1273.94 rows=720 width=188)
         ->  Merge Join  (cost=1050.26..1226.42 rows=7704 width=188)
               Merge Cond: (d.id = dc.deviceid)
               ->  Index Scan using devices_pkey on devices d  (cost=0.15..58.95 rows=720 width=82)
               ->  Sort  (cost=1050.11..1069.37 rows=7704 width=110)
                     Sort Key: dc.deviceid
                     ->  Merge Join  (cost=403.16..552.77 rows=7704 width=110)
                           Merge Cond: (c_row.id = dc.componentid)
                           ->  Subquery Scan on c_row  (cost=253.38..285.63 rows=720 width=110)
                                 ->  GroupAggregate  (cost=253.38..278.43 rows=720 width=286)
                                       ->  Sort  (cost=253.38..258.73 rows=2140 width=286)
                                             Sort Key: c.id
                                             ->  Hash Join  (cost=44.75..135.00 rows=2140 width=286)
                                                   Hash Cond: (cm.manufacturerid = m.id)
                                                   ->  Hash Join  (cost=26.20..87.03 rows=2140 width=86)
                                                         Hash Cond: (cm.componentid = c.id)
                                                         ->  Seq Scan on component_manufacturers cm  (cost=0.00..31.40 rows=2140 width=8)
                                                         ->  Hash  (cost=17.20..17.20 rows=720 width=82)
                                                               ->  Seq Scan on components c  (cost=0.00..17.20 rows=720 width=82)
                                                   ->  Hash  (cost=13.80..13.80 rows=380 width=208)
                                                         ->  Seq Scan on manufacturers m  (cost=0.00..13.80 rows=380 width=208)
                           ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
                                 Sort Key: dc.componentid
                                 ->  Seq Scan on device_components dc  (cost=0.00..31.40 rows=2140 width=8)
(25 rows)


SQL Server Management Studio - Cannot connect - Method not found SqlConnectionInfo.set_IsActiveDirectoryUniversalAuth I have a fresh installation of Windows 10, on which Microsoft Visual Studio 2015 was installed (therefore, a default database was installed too). I installed SQL Server Management Studio 2016 (v13.0.16106.4) but could not connect with the following error message:

Cannot connect to MYSERVER\SQLEXPRESS.
Method not found: 'Void Microsoft.SqlServer.Management.Common.SqlConnectionInfo.set_IsActiveDirectoryUniversalAuth(Boolean)'. (ConnectionDlg)

I've since tried to:

Install SQL Server 2016
Uninstall both SQL Server 2016 and the one coming with Visual Studio 2015 (following http://intellitect.com/sql-server-2008-install-nightmare/)
Once everything was cleaned and after a reboot, install SQL Server 2016 with default settings
Repair Microsoft SQL Server Management Studio

Note that regardless of what I try to connect to (including typing junk in the server text box), I have the same message, which leads me to believe that the error happens before SSMS actually tries to connect to the server.
I'm out of ideas; it seems like a DLL file may be missing, but which one and why, I cannot say.
The only "useful" reference I have found is this technet forum post which refers to a "patch", but I can't find where this "patch" could be found, if it exists.
I can connect with sqlcmd -S mymachine\SQLEXPRESS; I can also run queries like SELECT * from sys.databases successfully.

Note that this answer will allow some operations, but you'll still have a broken environment.

IMPORTANT: This allows connecting to a database and running queries, but you'll still get errors inside SSMS!

I'm not saying it's pretty, but I can now use SQL Server Express 2016.

Copy the file Microsoft.SqlServer.ConnectionInfo.dll from the folder:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio

to this folder (I strongly suggest you backup the file first, then you can overwrite the file):

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\13.0.0.0__89845dcd8080cc91

Why does it work?

Note that a Microsoft engineer would better answer this question, but here's my theory: for some reason, a different release of the same version of the Microsoft.SqlServer.ConnectionInfo assembly has been released publicly (e.g. as part of Visual Studio and SQL Server Express release). The one in the GAC did not contain the SqlConnectionInfo.IsActiveDirectoryUniversalAuth property, but SQL Server Management Studio Express expects it.

What we are doing here is just replacing the version in the GAC with the version that ships with SSMS Express, so that it loads this one instead. This will affect everything that depends on that dll file, but hopefully there's no breaking changes in an assembly that has the same version number...

How did I find this?

I found which dll file the class was specified in by searching the intertubes. I then used sysinternal's procmon to look at which version of that dll was in fact loaded. I searched my computer for other potential versions of that file, and simply tried disabling versions until I had a different error. I then tried replacing it with other versions in the SSMS Express installation folder, and it worked. So here we are!

Update

It's not solving everything though... here's an error I have when right-clicking on a database and selecting property.

Cannot show requested dialog. (SqlMgmt)
Method not found: 'Microsoft.SqlServer.Management.Common.DatabaseEngineEdition Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_DatabaseEngineEdition()'. (SqlManagerUI)`

I have tried installing everything in the GAC without success either: ls "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\*.d
ll" | % { &"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe" /if $_.FullName }


How to create XML file by SQL query? I have to create an XML File using sql

 <externalCodes>
  <externalCode>
   <system>CALYPSO</system>
   <extCode>cptname</extCode>
  </externalCode>
 <externalCode>
   <system>CMS-GDP</system>
   <extCode>cptname</extCode>
 </externalCode>
 <externalCode>
   <system>Manual</system>
   <extCode>cptname</extCode>
 </externalCode>

My SQL is as follows

(SELECT system, extCode from(Select 'CALYPSO' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') a
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from(Select 'CMS-GDP' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') b
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from( Select 'MANUAL' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') c
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode"

My output comes like below:

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </externalCodes>

But the expected output is as below

   <externalCodes>
     <externalCode>
       <system>CALYPSO</system>
       <extCode>Mon</extCode>
     </externalCode>
    <externalCode>
       <system>CMS-GDP</system>
       <extCode>Mon</extCode>
     </externalCode>
     <externalCode>
       <system>MANUAL</system>
       <extCode>Mon</extCode>
     </externalCode>
    </externalCodes>

I don't have your data source, so I created a cte with some records in it

;with cte
AS
(
       Select 'CALYPSO' AS 'system', 'Mon' as cptname
       union all Select 'CMS-GDP' , 'Mon'
       union all Select 'MANUAL' , 'Mon'
)
SELECT system, cptname as extCode
from
   cte
FOR XML PATH('externalCode'), TYPE, ELEMENTS ,ROOT('externalCodes')

output for it:

<externalCodes>
   <externalCode>
      <system>CALYPSO</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>CMS-GDP</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>MANUAL</system>
      <extCode>Mon</extCode>
   </externalCode>
</externalCodes>


why would rebuild index reduce btree leaf numbers Rebuilding an index might reduce the number of leaf nodes by about 20%
  - 30%. The most you can possibly expect from this reduction is 20%-30% for very expensive operations like a FULL INDEX SCAN. The typical
  INDEX UNIQUE SCAN gain of an index rebuild is 0%-2% because the depth
  of the index is not reduced by the rebuild.

In my understanding , the btree structure/shape is almost uniqe. why would rebuild the btree reduce leaf numbers ?

is there any uncertainty around the btree build process ?

Myth: Indexes Can Degenerate

A living index often accumulates empty spaces in the leaf nodes — e.g. due to deletes. Rebuilding the index will make sure all leafs are filled up to the configured threshold.

Note that I wrote it might reduce the number of leaf nodes. If have only inserts on this table, you won't even get this benefit.

Also keep in mind that the overall context of this article is an argument against rebuilding indexes without really good reason.


Numerical data with lots of NULLs: 6NF or document database? I have a source of numerical data mostly comprised of sensor readings, physical quantities and flags. Each associated with a datetime.

|  id  |        timetag           |   sensor1   |      sensor2     |  flag1  |  ...  |
--------------------------------------------------------------------------------------
| 943  | 2016-12-08 10:27:00.000  |  1.2323523  |  -23123.5346234  |    0    |  ...  |
| 944  | 2016-12-08 10:27:00.038  |     NULL    |     163.6151345  |    1    |  ...  |
| 945  | 2016-12-08 10:27:02.000  |  1.2477801  |       NULL       |    3    |  ...  |
                                       ...

The data has the following characteristics:

There are hundreds of fields, and new unexpected fields may pop up at any time. We need to record those too.
For every row, only a small subset (<10%) of the fields are available.
Sometimes a field is available but corrupted, i.e. NaN, and we need to record that.
Data is written once, updated almost never and read relatively often.
We need to do range queries on the data, both on the date and on the actual numbers.
We don't want to lose any data.
It doesn't need to be a distributed system.

I'm wondering what the best way to store these data would be.

Solution 1: One big table

A single huge table would be a bad idea, because it would be full of NULLS and inconvenient. Moreover, it would be impossible to distinguish between "recorded but NaN" NULLs and "not recorded" NULLs.

Solution 2: 6th normal form

The first solution that comes to mind is to use 6NF, creating one table for each field.

This is clean in theory, but

queries would require many many JOINs at the same time;
new tables would have to be created on the fly when a new field arrives for the first time;
fields that are always paired, like for example longitude and latitude would have to be automatically detected and their tables merged for efficiency;
if at some point one of the columns in a "merged" table (e.g. lon in the lon-lat example above) starts to be obtained independently (without lat), to keep the table in 6NF we would have to detect that and automatically split the table into separate tables.

The above makes for a rather complex solution.

Solution 3: Document database

I've been considering PostgreSQL with JSONB to mimic a document database.
This would allow us to only store the data we have in each row, but still have a single table.

However, as detailed in my (unanswered) question on Stack Overflow, JSONB doesn't seem appropriate for the numeric operations we need, like range queries.
On the other hand, NoSQL technologies like MongoDB, in which I have no experience, don't guarantee the consistency we are looking for.

Question

Of the solutions above, which would you favor? Are there any other options I'm missing?

JSONB might be a good option. If the TYPES of your flags and sensors are always the same (let's say they are all floats and ints), you could also use another strategy:

CREATE TABLE tb
    (id           serial primary key,
     timetag      timestamp default now(),
     sensor_nr    integer,
     sensor_value float,
     flag_nr      integer,
     flag_value   integer
    ) ;

If you have real values for sensors 2 and 1000, you would then do:

INSERT INTO
    tb
    (sensor_nr, sensor_value)
VALUES
    (2, 123.456),
    (1000, 0.123) ;

Or, if there are flags:

INSERT INTO
    tb
    (sensor_nr, sensor_value, flag_nr, flag_value)
VALUES
    (1000, 123.456, 1000, 0),
    (2, 234.567, 2, 1) ;

The NaN value would be represented by NULL; and the "no reading" would be represented by just the non-existence of the time/sensor_nr row.

If you need frequent range SELECTs of different types, you would index by timetag; and also by sensor_nr and sensor_value... The insert costs would be relatively high, but the SELECTs could be fast.

If the "new unexpected fields that pop at any type" are of different types (i.e.: you have some co-ordinate pairs (float,float) and not just simple floats) this approach won't be flexible enough. In that occasion, probably JSON(B) and the new indices is probably your best alternative; at the cost of losing (some) type safety.