Leetcode Everyday: 1179. Reformat Department Table. Easy

select id, 
    sum(case when month = 'Jan' then revenue end) as "Jan_Revenue",
    sum(case when month = 'Feb' then revenue end) as "Feb_Revenue",
    sum(case when month = 'Mar' then revenue end) as "Mar_Revenue",    
    sum(case when month = 'Apr' then revenue end) as "Apr_Revenue",    
    sum(case when month = 'May' then revenue end) as "May_Revenue",    
    sum(case when month = 'Jun' then revenue end) as "Jun_Revenue",    
    sum(case when month = 'Jul' then revenue end) as "Jul_Revenue",    
    sum(case when month = 'Aug' then revenue end) as "Aug_Revenue",    
    sum(case when month = 'Sep' then revenue end) as "Sep_Revenue",    
    sum(case when month = 'Oct' then revenue end) as "Oct_Revenue",    
    sum(case when month = 'Nov' then revenue end) as "Nov_Revenue",
    sum(case when month = 'Dec' then revenue end) as "Dec_Revenue"
from department
group by id

Source: https://leetcode.com/problems/reformat-department-table/

Thanks SQL Server! My Bonus is Now Higher* than My Salary

Given these two functions:

create function EmployeeMoolahs()
returns table
as
return
    select
        e.EmployeePersonID, e.Bonus, e.Salary
    from Employee e
go
 
create function EmployeeSummary()
returns table
as
return
    select p.FirstName, p.LastName, em.*
    from Person p
    join EmployeeMoolahs() em on p.PersonID = em.EmployeePersonId
go

The output of this query:

select top 5 * from EmployeeSummary() es order by es.EmployeePersonId




Seeing that it's best to present the salary information first before the bonus, we move the Salary information right after the EmployeePersonID.

alter function EmployeeMoolahs()
returns table
as
return
    select
        e.EmployeePersonID, e.Salary, e.Bonus
    from Employee e
go

Then you go back home, didn't even bother to check the output since it's a trivial fix anyway. On the 15th of the month, you receive your salary and you check your pay slip. You are so delighted that they make your bonus equal to your salary, then your jaw dropped, you don't receive any salary. Upon investigating the cause of that anomaly, you found out the erring query:

select top 5 * from EmployeeSummary() es order by es.EmployeePersonId




It yields a wrong output!


Whoa, holy guacamole! Why I don't have any salary? As if, the Bonus and Salary swapped contents. Yeah right, they really are. The presentation concern you tried to fix, by placing Salary right after EmployeePersonId, has an error. The salary is now slotted to bonus field, and the bonus is now slotted salary field.


Checking if your eyes is fooling you, you tried to explicitly select all the columns:

select top 5 es.FirstName, es.LastName, es.EmployeePersonId, es.Salary, es.Bonus from EmployeeSummary() es order by es.EmployeePersonId

But still, you still don't have any salary.




You also check the function you modified if it returns correct information:

select top 5 * from EmployeeMoolahs() em order by em.EmployeePersonId

However, it has correct output:




Then you are thinking what causes the error on EmployeeSummary function? What makes the query wild? Why it's not returning proper information? Hmm.. wild? Maybe it's the wild…card, there's something wild on the wildcard! Got to remove the wildcard on EmployeeSummary()

create function EmployeeSummary()
returns table
as
return
    select p.FirstName, p.LastName, em.*
    from Person p
    join EmployeeMoolahs() em on p.PersonID = em.EmployeePersonId
go

Change that to explicit form:

alter function EmployeeSummary()
returns table
as
return
    select p.FirstName, p.LastName, em.EmployeePersonId, em.Salary, em.Bonus
    from Person p
    join EmployeeMoolahs() em on p.PersonID = em.EmployeePersonId
go

Then you check the result of this query:

select top 5 * from EmployeeSummary() es order by es.EmployeePersonId

It's correct now!



Trying to check the fix if it is just a fluke, we swap again the order of Bonus and Salary of the source function to their original order:

alter function EmployeeMoolahs()
returns table
as
return
    select
        e.EmployeePersonID, e.Bonus, e.Salary
    from Employee e
go

Re-check again:

select top 5 * from EmployeeSummary() es order by es.EmployeePersonId

Below is the result, the columns' information are still correct! The order of fields won't change as we are selecting from EmployeeSummary which has explicitly selected columns. What is important, the information is always correct, the ordering of columns should be done on EmployeeSummary, not on EmployeeMoolahs.





Let's try to investigate the root cause of the error. If you got wildcard in the query of your function, SQL Server is trying to build a metadata for returned fields of that function, as if SQL Server burns a metadata for the returned fields of the query of your function, albeit invisibly.

create function EmployeeMoolahs()
returns table
-- burn these metadata...
(
    Bonus money,
    Salary money
)
-- ...burn
as
return
    select
        e.EmployeePersonID, e.Bonus, e.Salary
    from Employee e
go
 
create function EmployeeSummary()
returns table
-- burn metadata
(
    FirstName nvarchar(max),
    LastName nvarchar(max),
          -- burn these metadata based on the metadata obtained from EmployeeMoolah's wildcard
    EmployeePersonId int,   -- 1
    Bonus money,            -- 2
    Salary money            -- 3
)
-- ...burn
as
return
    select p.FirstName, p.LastName, em.*
     
          -- At the time this EmployeeSummary function is created, this is the order of fields on
         -- EmployeeMoolah()'s wildcard:
     
          -- 1. EmployeePersonId
          -- 2. Bonus
          -- 3. Salary
     
     
    from Person p
    join EmployeeMoolahs() em on p.PersonID = em.EmployeePersonId
go

In fact that is how other RDBMS does it, i.e. you need to explicitly return the metadata of your returned fields. Wildcard problem can be avoided by application developers using other RDBMS as they can see the wildcard could one day not be slotted properly to the correct column metadata, so they tend to be explicit on selecting each columns. They tend to avoid wildcards on functions. This kind of problem is virtually non-existent to them.


So in SQL Server, if we change the order of the fields on the source function (EmployeeMoolahs) of the dependent function(EmployeeSummary), e.g. swap the position of Bonus and Salary, Salary comes before Bonus on EmployeeMoolahs, the EmployeeSummary's metadata will not be updated accordingly, it will not be automatically updated. That is, EmployeeSummary metadata's position will still be the same, i.e. Bonus still comes before Salary. Hence when we issue a query on wildcard-bearing EmployeeSummary function, the wildcard will fall to the wrong slots.

EmployeeSummary() 's Metadata is not automatically updated     EmployeePersonId    Bonus    Salary
EmployeeMoolah()'s expanded wildcard from its swapped columns  EmployeePersonId    Salary   Bonus

So that's how the wildcard can cause problems on SQL Server. Trying to imagine what fields are good to slot on the salary :P


Think of the damages that can be caused by wrong information slotted by the wildcard. IsHired field goes to IsAdmin, OriginalPrice goes to CurrentPrice, PassingScore goes to Score, etc.


* It can happen, try to copy the problem above :P


Sample data:

create table Person
(
 PersonId int identity(1,1) primary key,
 FirstName nvarchar(max) not null,
 LastName nvarchar(max) not null,
);

create table Employee
(
 EmployeePersonId int not null primary key references Person(PersonId),
 Salary money not null,
 Bonus money not null default(0)
);

insert into Person(FirstName, LastName) values
('John', 'Lennon'),
('Paul', 'McCartney'),
('George', 'Harrison'),
('Ringo', 'Starr');


insert into Employee(EmployeePersonId, Salary, Bonus) values
(1, 60000, 20000),
(2, 60000, 0),
(3, 40000, 0),
(4, 40000, 0);

Happy Computing! ツ

Stored Procedure Name + F12 + Tada!

Repartee is something we think of twenty-four hours too late



Someone made an astute observation that the ap + tab + stored proc name + tab + tada (tada is not included, you have to utter it yourself :p) combo shortcut has a problem on scripting the content, i.e. it strips out the comments on the top of the code.



I should have replied that it's okay to remove those comments, those comments are just a pile of name(s) of the original developer(s) tacked on one after another, too tacky :p Those comments does not in any way helps a new dev on deciphering the intent of the code. Hmm.. but it has a use, it helps others on their witch-hunt on the culprit(s) of erring code heheh



But I would not give an answer that would strip others of their rights to maintain comments on top of the code, any preferences for that matter, cut the drama :P



Without further ado, this is the new shortcut:

exec + stored proc name + F12



RedGate will script back the whole contents of the stored procedure to a new query editor, i.e. RedGate will also include the comments on top of the stored proc.



Other shortcuts:

For view, type: ssf + tab + view name + F12

For function, type: ssf + tab + function name + backspace + F12



An observation, there's no need to put EXEC or SELECT * FROM, just type in the stored proc / view / function name directly, then press F12. Sometimes, RedGate is flaky though, type in EXEC or ssf anyway :-)





Happy Computing! ツ

Let's Just Suck Less

Someone is seeking an advice based on my blog post Why So Fond Of UNION


I don't have the wisdom of a Sage, but here's my piece: Let's just suck less every sprint, every year, it's good enough


The initial step for sucking less is to identify that our code sucks


That advice somehow sucks, but I'll make sure that I’ll be giving more advice and code that sucks less through the passage of time


And let’s not forget: Let's be happy, but not satisfied, my code sucks, your code sucks, suffice to say everyone's code sucks


If we really want to truly build a product that doesn't suck, let's try to build vacuum cleaners ^_^ lol



Happy S... QL writing! ツ

Why so fond of UNION?

I'm wearing my sargchitect hat in our current sprint. Aside from improving the speed of the existing queries by making them sargable and devoid of unintended cartesian-yielding joins, the optimization team need to make sure the queries are properly refactored, easy to maintain, smooth and suave to the eyes, so the developers could have a pleasant time reading and understanding the queries.


I saw some UNION queries that can be made simpler using OR. I don't think it's ok to have a union of the same table, union like that tend to be DRY-violating, tend to have many lines, hence harder to debug and harder to optimize


I'm thinking, why the inordinate fondness for UNION if the query can be made simpler with OR? Last I checked, Microsoft haven't disabled the OR functionality. So let's rejoice to the fact that Microsoft don't have any plans to disable OR functionality for the foreseeable future.




Happy Sarging! Scratch that, Happy SARGing! ツ

Debunking the myth that JOIN is faster than IN

We see now that contrary to the popular opinion, IN / EXISTS queries are not less efficient than a JOIN query in SQL Server. In fact, JOIN queries are less efficient on non-indexed tables, since Semi Join methods allow aggregation and matching against a single hash table, while a JOIN needs to do these two operations in two steps. -- http://explainextended.com/2009/06/16/in-vs-join-vs-exists/

Converted this JOIN (with DISTINCT) … (From 1 minute and 24 seconds) :

INNER JOIN dbo.fn_ssrs_ConstrainID(@LanguageCultureCode,@NeutralLanguageCode,@UserID,@PersonID,@Application,@Module,'PEOPLE','Review') cons
 
ON rpp.PersonID = cons.id

…to IN expression (optimized, down to 43 seconds) :

and rpp.PersonID in
    (select cons.id
    from dbo.fn_ssrs_ConstrainID(@LanguageCultureCode,@NeutralLanguageCode,@UserID,@PersonID,@Application,@Module,'PEOPLE','Review') cons)

Advised the two colleagues to further optimize the query's existence filters by converting all the JOIN+DISTINCT combo to IN. They were able to optimized the original query of 1 minute and 24 seconds down to 1 second.



Using the same technique as above, another colleague and I were able to optimized a stored proc that is very crucial to the web app’s performance, as that stored proc determines the visibility of some buttons on the web page. The original stored proc was 5 seconds slow, on one page there are two buttons that are using the stored proc, that makes the web page to appear only after 10 seconds or so, we were able to tame it down to sub-second response time just by converting some of the JOINs to INs.





Happy Computing! ツ

Debunking the myth that CTE is slow

There are some folks who believe that CTE is slow. I don't know where they got that impression. We might as well say that our SELECT statement is slow because our code is using er.. SELECT statement. And there are some folks who believe CTEs underpinning is a cursor, really, no joking. I'm starting to lose faith in humanity ^_^ lol


For those who believe CTE is made out of cursor, here's how to partner cursor to a CTE:

http://stackoverflow.com/questions/14601407/using-a-cursor-with-a-cte


So if one can define a cursor out of CTE, what is a cursor on CTE? a cursor on cursor? Head-scratching! :D There's a lot of engineering effort that goes into CTE and all some folks can say about CTE is that it is a cursor, this kind of folks is dangerous to our industry. It might be their excuse for not learning CTE and windowing functions, which by the way can be used separately, but commonly seen together as they come out at the same time on most RDBMSes.

As with derived tables, CTEs allow you to create complex queries that would otherwise require the use of temporary tables and cursors, which can be inefficient. CTEs remove the overhead of creating and dropping those temporary tables, and of the INSERT and UPDATE statements required to populate them. This can make for faster execution, though you should always compare all available options when performance is critical -- http://www.blackwasp.co.uk/SQLCTEs.aspx

What we do with CTE is what determines its performance. CTE is nothing but just a programming functionality that makes our query more manageable, it helps when we are simplifying very complex and slow queries; and granted that you are doing the right approach, it aids the developer on making the query performant.


If you have tried programming with C or C++, you might be familiar with macro. Yes, CTE is just like that, a macro. It is expanded to full form when you use it. It will not magically make your query faster nor slower.



CTE is just an inline view:

with product_count_query as
(
    select product_id, product_count = count(*)
    from tbl
    group by product_id
)

select p.*, the_count = isnull(c.product_count, 0)
from product p 
left join product_count_query c -- derive table from CTE
on p.product_id = c.product_id;

CTE is just a shorthand for an explicit view:

create view product_count_query as

    select product_id, product_count = count(*)
    from tbl
    group by product_id;
go


select p.*, the_count = isnull(c.product_count, 0)
from product p 
left join product_count_query c -- derive table from VIEW
on p.product_id = c.product_id;

A view, and a CTE for that matter, don't have their own execution plan, both are ultimately just being expanded and bolted to the main query:

select p.*, the_count = isnull(c.product_count, 0)
from product p
left join
(
    select product_id, product_count = count(*)
    from tbl
    group by product_id
) as c -- derive table from an inline view, er.. a CTE? ;-)
 on p.product_id = c.product_id

All of them, same banana, exactly the same execution plan, same speed.


If you believe CTE is just a cursor, you might as well believe that view is just a cursor too. A darndest and silly belief.


Time to stop spewing the nonsense that CTE is just a cursor, all those three queries have exactly the same execution plan and same speed. I don't know what magic sauce that could give the table-deriving query a performance boost over its CTE cousin query, there's none.


If you believe that there's something inherently slow with CTE, thus you prefer to write your query using table-deriving approach because you believe it is faster than CTE, you are merely doing Cargo Cult Programming. You are depriving yourself of the benefits of abstractions, readability and manageability of CTE offers to the table by having a cult-like belief that CTE is slow.


It's what you do with CTE that makes it slow or fast. If you will use JOIN+DISTINCT combo on a CTE or any form of query abstraction for that matter, expect bad performance and rewrites.


CTE is just one of the vast array of SQL tools at your disposal that can make your daily craft easier. You don't need to use it exclusively, you can mix-and-match it with other SQL tools too.


So what makes a query fast?


Start with the right assumption, it's just a macro:

@crokusek they(CTE) don't get materialized at all AFAIK - they are just a table expression -- http://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table#comment69061_13117

This is what gives CTE a bad name: wrong assumption:

One reason CTE's can be employable for performance reasons is because their (possibly forced) materialization could be more easily parallelized whereby temp tables are generally computed serially before their use. -- http://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table#comment69060_13117

Until MATERIALIZED is supported...

with 
T MATERIALIZED AS
(
    select ROW_NUMBER() over(partition by CustomerCode order by OrderID) as rn, * from test
)
,R(CustomerCode, Rn, OrderId, Qty, RunningTotal) as
(
    select CustomerCode, Rn, OrderID, Qty, Qty
    from t 
    where rn = 1
     
    union all
     
    select t.CustomerCode, t.Rn, t.OrderId, t.Qty, p.RunningTotal + t.Qty
    from t t
    join r p on p.CustomerCode = t.CustomerCode and t.rn = p.rn + 1
     
)
select R.CustomerCode, R.OrderId, R.Qty, R.RunningTotal from r
order by R.CustomerCode, R.OrderId 
option(maxrecursion 0);

...we should mix-and-match CTE with temporary table to give our query a performance boost. That is, there are times we need to materialize the result of a query, e.g., we should materialize the results of a row_numbering query to an actual table(temporary table or variable table will do) first when we wanted to use that row-numbered set to a recursive query, this is fast:

select ROW_NUMBER() over(partition by CustomerCode order by OrderID) as rn, * into #xxx 
from test;
 
with 
T AS
(
    select * from #xxx
)
,R(CustomerCode, Rn, OrderId, Qty, RunningTotal) as
(
    select CustomerCode, Rn, OrderID, Qty, Qty
    from t 
    where rn = 1
     
    union all
     
    select t.CustomerCode, t.Rn, t.OrderId, t.Qty, p.RunningTotal + t.Qty
    from t t
    join r p on p.CustomerCode = t.CustomerCode and t.rn = p.rn + 1
     
)
select R.CustomerCode, R.OrderId, R.Qty, R.RunningTotal from r
order by R.CustomerCode, R.OrderId 
option(maxrecursion 0);
 
drop table #xxx;

Happy Computing! ツ

A primer on connection pooling

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Craft
{
    class Mate
    {
        // Connection pooling 101: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
 
        // To check the number of connections made:
 
        static string checkPooledConnections =
@"SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) 
FROM master.dbo.sysprocesses
WHERE program_name = '.Net SqlClient Data Provider'";
 
        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test"))
            {
                connection.Open();
                // Pool A is created.
 
                using (var cmd = connection.CreateCommand())
                {
                    string languageCode = "zh";
                    Console.WriteLine("Set Language: {0}", languageCode);
 
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "SetLanguage";
                    var prm = cmd.CreateParameter();
                    prm.ParameterName = "@LanguageCode";
                    prm.Value = languageCode;
                    cmd.Parameters.Add(prm);
                    cmd.ExecuteNonQuery();                   
                }
 
                ShowLanguage(connection);
                connection.Close();
            }
 
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test"))
            {
                connection.Open();
                // Pool B is created because the connection strings differ.
 
                // Even the database is the same, the casing of the database name is different(uppercased T), hence connection pooler treating this connection as different connection                
 
                ShowLanguage(connection);
                connection.Close();
            }
 
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test"))
            {
                connection.Open();
                // The connection string matches pool A.
 
                Console.WriteLine("Shall show no language even this connection matches the connection pool A.");
                ShowLanguage(connection);
                connection.Close();
            }
 
            Console.WriteLine("\nPlease execute the following query on SSMS before hitting the Enter key. You shall see that there are only two connections made instead of three. Connection pooling is in effect\n\n{0}",
                checkPooledConnections);
            Console.ReadKey();
 
      
        }
 
        static void ShowLanguage(SqlConnection connection)
        {
            using (var getLang = connection.CreateCommand())
            {
                getLang.CommandType = CommandType.Text;
                getLang.CommandText = "select cast(CONTEXT_INFO() as varchar(5))";
               
                object userLanguageCode = getLang.ExecuteScalar();
                Console.WriteLine("Get Language: {0}\n", userLanguageCode);
                Console.ReadKey();
            }           
        }
       
    } // classmate
} // namespacecraft
 

SetLanguage SP:

create procedure SetLanguage(@LanguageCode varchar(5)) as
begin
    declare @binvar varbinary(128);
    set @binvar = cast(@LanguageCode as varbinary(128));
    set context_info @binvar;
end;

Query SARGability

A colleague asked how to select from two tables dynamically, something along this line(question was not exactly phrase as the following):

DECLARE @tableToSelect CHAR(1) = 'y';
select * from @tableToSelect where a < 100;   

If that is even possible, it would be a problem if the similar columns are differently named on the two tables, say PersonID, MencschID (German for PersonID). This can not select all the persons from the American tables:

select * from @tableToSelect where MenschID < 100;

I suggested him this:

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

A discerning programmer that he is, he quizzed me if that would result to inefficient query. I like that he posed that question and wanted his code to be performant.


If he had asked me that question when I was just a starting developer, I would concur with him. I would naturally assume that it's not efficient to union the tables and filtering it further. I would assume that it would be better if the rows are filtered right from the root source, as illustrated below, and thus enabling both queries of the UNIONed queries to use the available index:

SELECT a,b
FROM t
WHERE @tableToSelect = 't' and a < 100
 
UNION all
 
SELECT c,d
FROM y
WHERE @tableToSelect = 'y' AND c < 100;

Fortunately for us, internet is a wonderful place; an eon ago, I stumble upon an article that says RDBMS doesn't short-circuit conditions: http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx


So this would result to an error (Conversion failed when converting date and/or time from character string):

SELECT   *
FROM     t1
WHERE    ISDATE(val) = 1
         AND CONVERT(DATETIME, val) < GETDATE();

If we try to fool the RDBMS by eagerly filtering the valid dates first, this would still not work, this will still result to conversion error. A proof that WITH or any form of re-arranging the query doesn't eagerly execute the query.

WITH x AS
(
      SELECT   *
      FROM     t1
      WHERE    ISDATE(val) = 1
)
SELECT *
FROM x    
WHERE CONVERT(DATETIME, val) < GETDATE();

That means, the conditions' execution order is not executed based on how you arrange your query. The outer query's conditions could be mixed with the inner query's condition by your RDBMS. Armed with that knowledge in mind, the following two queries are executed similarly:

SELECT *
FROM t
WHERE @tableToSelect = 't' and a < 100
 
UNION all
 
SELECT *
FROM y
WHERE @tableToSelect = 'y' AND c < 100;
 

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

That is, the second query(CTE) is expanded to the same query as the first query; thus, the second query's a < 100 expression utilizes all the available indexes on both tables, making the query performant. Given that they are semantically the same, it's better to use the second query as it's easier to maintain, the query's condition is just placed on one location only, there's no code duplication involved. Some shun the table-deriving or CTE approach, as they think enclosing the query on CTE or table-deriving it would deprive the expression a < 100 the SARGability it needed; this is not true, CTE-using queries can still use the available indexes on both tables.



Both queries have exactly the same identical query execution plan:

Here's the supporting DDL:

SET NOCOUNT ON;

CREATE TABLE t(a INT, b INT);
CREATE TABLE y(c INT, d INT);

DECLARE @i INT;

SET @i = 0;
WHILE @i < 100000 BEGIN
    INSERT INTO t(a,b) VALUES(@i,0);    
SET @i = @i + 1;
END;

INSERT INTO y(c,d) SELECT a,b FROM t;
CREATE INDEX ux_t ON t(a);
CREATE INDEX ux_y ON y(c);

SET NOCOUNT OFF;

If you are a keen observer, you'll ask, what will happen to the query with the date detection in it? How to prevent the query from executing the conversion if the field is not a date? We can really do short-circuit in SQL Server explicitly, but that would entails using CASE WHEN, and that would make your query not being SARGable. Here's the short-circuited query:

SELECT *
FROM t1
WHERE CASE WHEN ISDATE(val) = 1 AND CONVERT(DATETIME, val) < GETDATE() THEN 1 END = 1

Another approach, is to use an optimization fence on ISDATE. This way, SQL Server don't need to merge the ISDATE condition with CONVERT, avoiding the conversion failed error.

with x as
(
      SELECT   TOP 4000000000 *
      FROM     t1
      WHERE    ISDATE(val) = 1
)
SELECT *
FROM x    
WHERE CONVERT(DATETIME, val) < GETDATE();

So here's the performance of a non-SARGable query:

DECLARE @tableToSelect CHAR(1) = 'y';

-- select * from @tableToSelect where a < 100;


SELECT a,b
FROM t
WHERE case when @tableToSelect = 't' AND a < 100 then 1 end = 1
 
UNION all
 
SELECT c,d
FROM y
WHERE case when @tableToSelect = 'y' AND c < 100 then 1 end = 1;
 

WITH x AS
(
    SELECT a,b FROM dbo.t
    WHERE @tableToSelect = 't'
    UNION ALL    
    SELECT c,d FROM dbo.y    
    WHERE @tableToSelect = 'y'
)
SELECT * FROM x WHERE a < 100;

As we can see the non-SARGable query is slow compared to the SARGable one. non-SARGable query has a cost of 81% against 19% of SARGable one



That's it, we should strive to make our query use our tables' indexes



Happy Coding! ツ

Linq's syntax symmetry with Postgres

Can't help but notice, Linq multiple condition has more affinity with Postgres flexible syntax:

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals new {o.CompanyID, o.CustomerID}

Postgresql:

from Customers as c 
join Orders as o on (c.CompanyID,c.CustomerID) = (o.CompanyID,o.CustomerID)

Postgresql LAG windowing function

Given this:

create table t
(
ts timestamp not null,
code int not null
);

insert into t values
('2011-01-13 10:00:00', 5),
('2011-01-13 10:10:00', 5),
('2011-01-13 10:20:00', 5),
('2011-01-13 10:30:00', 5),
('2011-01-13 10:40:00', 0),
('2011-01-13 10:50:00', 1),
('2011-01-13 11:00:00', 1),
('2011-01-13 11:10:00', 1),
('2011-01-13 11:20:00', 0),
('2011-01-13 11:30:00', 5),
('2011-01-13 11:40:00', 5),
('2011-01-13 11:50:00', 3),
('2011-01-13 12:00:00', 3),
('2011-01-13 12:10:00', 3);

An stackoverflow user asked:

How can I select the first date of each set of identical numbers, so I end up with this:
2011-01-13 10:00:00, 5
2011-01-13 10:40:00, 0
2011-01-13 10:50:00, 1
2011-01-13 11:20:00, 0
2011-01-13 11:30:00, 5
2011-01-13 11:50:00, 3

I answered:

with sequencer as 
(
SELECT ROW_NUMBER() OVER(ORDER BY ts) seq, ts, Code
FROM T
)
select a.ts, a.Code
from sequencer a 
left join sequencer b on b.seq + 1 = a.seq 
where b.code IS DISTINCT FROM a.code;

But I'm not giving the problem a first-class treatment it deserves, I noticed somebody gave an answer that uses lag function, albeit he/she uses where prevcode <> code or prevcode is null

with r as
(
select ts, code, lag(code,1,null) over(order by ts) as prevcode
from t
)
select ts, code 
from r
where prevcode is distinct from code

Hmm.. :-) that code is very elegant, sleek and performant

http://www.postgresql.org/docs/8.4/static/functions-window.html

Exceptions to Rule #1 and Rule #2. One-to-one relationship on both ends of two tables

According to Rule-1-Rule-2, the following query falls under rule #2 (primary key on first table appears on second table's join condition). This means the second table is many-to-one to first table, there are many countries to a person:

SELECT Country.CountryName, Leader.PersonName AS Leader
FROM Person Leader
JOIN Country ON Country.CurrentLeaderID = Leader.PersonID

How this applies in real world?

Let's take Hongkong as an example, imagine that the records of Hongkongers are not yet updated to China's id; so one cannot delete HK from Country table, so it means that HK still has an entry on Country table, so what one can do is just update HK's leader to China's leader. So there goes the case of two (or more) countries that belong to same leader only.

But what if we really want to enforce these conditions: (1) one leader to one country only (2) one country to one leader only. How can we do those?

First, though at first glance, we can put a LeadingCountryID to Person table so it would satisfy the second condition, this would however introduce a problem, a country can be governed by two leaders with that solution. And the Person table will be filled with nulls on those who are not leaders, that's a waste of I/O; and at most, only one row with null column is allowed on unique nullable column on Sql Server 2005 and below; there's a work-around on SQL Server 2008 though, you create unique index out of filtered-out nulls. Another solution is we put unique constraints on Country's CurrentLeaderID and reverse the query:

SELECT Country.CountryName, Person.PersonName 
FROM Country
JOIN Person Leader ON Leader.PersonID = Country.CurrentLeaderID

Hmm.. it seems we are able to achive our goal of stating the intent on code. It beautifully fall under rule #1.

However, that would have problems on entities with circular references. Take an example: a Person is born in a Country, a Country has a leader(Person), see this article: http://www.ienablemuch.com/2010/12/do-not-make-circular-references-on.html

If you can be sure that your entities won't have a circular references on the future, you can stop here.



.
.
.
.
.
.
.


Otherwise, the only neat solution is to remove the CurrentLeaderID from the Country table and make an association table:

CREATE TABLE CountryCurrentLeader
(
  dummy serial not null primary key,
  CountryID int NOT NULL unique key references Country(CountryID),
  CurrentLeaderID int NOT NULL unique references CurrentLeaderID(PersonID)
)

So does our design automatically fall to rule #1 if we use this query?

SELECT CountryCurrentLeader.CountryID, Person.PersonName 
FROM Person
JOIN CountryCurrentLeader 
ON CountryCurrentLeader.CurrentLeaderID = Person.PersonID

Apparently it isn't, a primary key on first table (though not selected on SELECT clause) does appear on second table's join condition, so that's rule #2, it means the second table is many-to-one on first table.



So how can we at least make it appear in query that the new design(association table) conforms to a right rule(one-to-one on both ends), rule #1?

To achieve rule #1, we simply arrange the query to this form...

SELECT CountryCurrentLeader.CountryID, Person.PersonName 
FROM CountryCurrentLeader
JOIN Person ON Person.PersonID = CountryCurrentLeader.CurrentLeaderID 

...so by using an association table, there won't be a primary key from first table that will appear on second table's join condition. Conforms to rule #1, FTW!


Let's extend our query further if our two rules will still hold water on complex queries:

SELECT CountryName.CountryName AS Country, Person.PersonName As LeaderName, LeaderBirthPlace.CountryName AS LeaderBirthPlace 
FROM CountryCurrentLeader -- 1
JOIN Person ON Person.PersonID = CountryCurrentLeader.CurrentLeaderID -- 2
JOIN Country CountryName ON CountryName.CountryID = CountryCurrentLeader.CountryID -- 3
JOIN Country LeaderBirthPlace ON LeaderBirthPlace.CountryID = Person.BirthCountryID -- 4

Let's dissect our query:

J1) Line 1 to Line 2: Rule #1 one-to-one. OK
J2) Line 1 to Line 3: Rule #1 one-to-one. OK
J3) Line 2 to Line 4: Rule #1 one-to-one. OK

So all is well, J1 and J2 safely conforms to Rule #1 as long we use association table with dummy primary key. J3 conventionally fall under Rule #1


There's no fullproof way to deduce the intent of a query based on just two rules. But we can at least follow the existing simplest rule (it's the simplest, one have to remember two rules only) by re-arranging the query and/or redesigning the database design.

Rule #1 and Rule #2

Simple guide to deciphering query's rows relationship.

How can one know that B is one-to-one to A, or if B is many-to-one to A?

SELECT *
FROM A
JOIN B ON B.FieldNameHere = A.FieldNameHere

Two simple rules:

1. If the first table's primary key don't appear on second table's join condition, the second table is one-to-one to first table.
2. If the first table's primary key appear on second table's join condition, the second table is many-to-one to first table.

How one knows which field is the primary key?
They are easy to remember, there are only two(or three) variations of them, namely: Id, EntityId, Entity_Id; other variations are not encouraged.


Rule #1 If the first table's primary key don't appear on second table's join condition, the second table is one-to-one to first table. The second table will always return one row at most to first table. There's only one birth country to a person. Example:

SELECT * 
FROM Person
JOIN Country ON Country.CountryId = Person.BirthCountryId

Rule #2 If the first table's primary key appear on second table's join condition, the second table is many-to-one to first table. The second table will return zero or more rows to first table. Example:

SELECT * 
FROM Person
JOIN Bid ON Bid.PersonId = Person.PersonId

Would this technique still work on reverse query? Yeah it would, check this:

SELECT * 
FROM Bid
JOIN Person ON Person.PersonId = Bid.PersonId 

The first table's primary key don't appear on second table's join condition; so, second table is one-to-one to first table. One given bid always belong to one person only.


How about this?

SELECT * 
FROM Country
JOIN Person ON Person.BirthCountryId = Country.CountryId

The first table's primary key appears on second table's join condition, falls under rule #2, it means the second table is many-to-one to first table. There could be zero or more persons that was born on that country.

It would also.

Apparently there's some exceptions to this two rules, check this: http://www.ienablemuch.com/2010/12/exceptions-to-rule-1-and-rule-2-one-to.html

Do not make circular references on tables

Don't paint yourself in the corner. Do not make circular references on tables

Example:

create table country
(
country_code varchar(2) not null primary key,
country text not null 
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);

alter table country
add column current_leader_code
 varchar(6) null 
 references person(person_code);

insert into country(country_code, country) values('PH','Philippines');
insert into person(person_code, person_name, birth_country_code) values('MB','Michael Buen', 'PH');
update country set current_leader_code = 'MB' where country_code = 'PH';

Even we can get away with making the two tables reference each other by strictly adhering to the sequence of the scripts above and making the country's current_leader_code nullable, we are painting ourselves in the corner by doing so.

When it's time for us to transfer this scripts to production machines, and we forgot to save those scripts; we will be left with this script(extracted from RDBMS admin tool):

create table country
(
country_code varchar(2) not null primary key,
country text not null,
current_leader_code 
 varchar(6) null
 references person(person_code)
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);

That fails big time! How can that script execute?

So to design things properly for the case described above, we must introduce an association table:

create table country
(
country_code varchar(2) not null primary key,
country text not null
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);



-- associations table
create table country_current_leader
(
dummy serial not null primary key,
country_code varchar(2) not null unique references country(country_code),
current_leader_code varchar(6) not null unique references person(person_code)
);


 
insert into country(country_code, country) values('PH','Philippines');
insert into country(country_code, country) values('CN','China');
insert into person(person_code, person_name, birth_country_code) values('MB','Michael Buen','PH');
insert into person(person_code, person_name, birth_country_code) values('HJ','Hu Jintao','CN');
insert into country_current_leader(country_code, current_leader_code) values('PH','MB');
insert into country_current_leader(country_code, current_leader_code) values('CN','HJ');
-- insert into country_current_leader(country_code, current_leader_code) values('CN','MB'); -- this will fail, only one leader per country, and only one country per leader
-- insert into country_current_leader(country_code, current_leader_code) values('PH','HJ'); -- this will fail too, only one leader per country, and only one country per leader

Plural table names are one trick pony

English plurals are not ORM-friendly.

Is that a proof by blatant assertion? It is if you can assert otherwise ;-)

Case in point, Chinese have two form of plurals, one for person, another for non-person, you use "men" suffix for person, "henduo" prefix for non-person. Filipinos plural form for everything(person or non-person) is facilitated by using "mga" prefix


Chinese-way:

SELECT * FROM EmployeeMen

SELECT * FROM ChildMen

SELECT * FROM HenduoFruit

SELECT * FROM HenduoMouse

Filipino-way:

SELECT * FROM MgaEmployee

SELECT * FROM MgaChild

SELECT * FROM MgaFruit

SELECT * FROM MgaMouse

English-way:

SELECT * FROM Employees

SELECT * FROM Children

SELECT * FROM Fruits

SELECT * FROM Mice

See? Mice and Children are not directly mappable to ORMs. And any decent ORM without an AI of sort, will not be able to infer that Mice's singular form is Mouse. In Filipino and Chinese languages, pluralizers are consistent, more amenable to ORMs


Let's say you are in a radical programming environment and the management forbids you from using mnemonic aliasies...

SELECT E.Name AS EmployeeName, C.Name as ChildName
FROM Employees AS E
JOIN Children AS C ON C.ParentId = E.ID

...you will find AS keyword a glorified singularizer for table names:

SELECT Employee.Name AS EmployeeName, Child.Name AS ChildName 
FROM Employees AS Employee
JOIN Children AS Child ON Child.ParentId = Employee.ID

And let's say you are in a very very extremely radical programming environment, they won't allow you to use aliases...

SELECT Employees.Name AS EmployeeName, Children.Name AS ChildName -- ugh, those look odd
FROM Employees 
JOIN Children ON Children.ParentId = Employee.ID

..., your queries will look odd.

BTW, we programmers are good in switching mental model, we don't really read this...

box.setX( box.getX() + 1);

...as set the X based on its old X with 1.

we read that as:

box.X = box.X + 1;
box.X += 1;
box.X++;

And the real best programmers are the ones that are trying to infer the intent of code given the lack of comments or proper method name, they don't go through the motions, they infer intents, they read the code above as: Move the box to the right

The best programmers are the ones with mental models that makes them able to abstract a concept very well, with or without the first-class constructs on syntaxes.

So the best programmer, would really read this...

SELECT * FROM Computer 

SELECT * FROM Toy

...as:

SELECT * FROM Computer(s)

SELECT * FROM Toy(s)

And since I'm a Filipino programmer, prefixing words to entity to make them plural easily gets old...

SELECT * FROM MgaEmployee

SELECT * FROM MgaChild

SELECT * FROM MgaFruit

SELECT * FROM MgaMouse

...and almost akin to incessant prefixing of tbl to table names:

SELECT * FROM tblEmployee

SELECT * FROM tblChild

SELECT * FROM tblFruit

SELECT * FROM tblMouse

Naming tables in plural forms are one of the design patterns that is like a novelty, aside from they easily wear off, they are just a one-trick pony. Plural names only work well if you will query one table only; reading SELECT Lastname, Firstname, Birthdate FROM Employees gives one a warm-and-fuzzy feeling; but the norm is, we join tables, and aliasing table names are warranted, especially to plural-advocates. To plural-advocates, wanna bet you would give your tables aliases rather than going through the motion when joining tables? i.e. This feels icky to you despite your plural-form-fervor for naming tables...

SELECT Persons.Name AS PersonName, Countries.CountryCode 
FROM Persons JOIN Countries ON Countries.CountryID = Persons.BirthCountryID

... Why that feels icky to you? hint: because to any self-respecting programmers, any selected columns with table qualifier would look odd if the table qualifier is of plural form.

With plural form table names, you can't seamlessly plug-and-play them to other components(think ORM), can't(or won't) leave them alias-less when querying two or more tables(looks funneh):

SELECT Children.Name AS ChildName, Children.Age, Fathers.Name AS FatherName 
FROM Children 
JOIN Fathers ON Fathers.ID = Children.FatherID)

So next time you encounter advocates of singular names, don't argue with them, they are mostly right.

And if you don't want to flunk MCSD 70-300 exam, use singular names


In terms of ORM-friendly plural naming conventions, here's their rankings:

0. Singular (hey, you said ORM-friendly plural naming conventions! how this manage to sneak in here?)

1. Filipino plurals

2. Chinese plurals (words are immutable, you just prefix "henduo" or suffix "men")

3. English plurals (that's why this article exist ;-) ) Aside from suffix "s", you have irregular and mutable plural forms (child -> children, mouse -> mice, goose -> geese, moose -> moose( not mutable, i just want to expose the inconcistencies of the language ;-) ) )


So keep in mind folks, plural table names are one trick pony only, the only instance it could give you a warm and fuzzy feeling is when you are getting data from one table only.

SELECT Name, FavoriteLanguage, FavoritePizzaBrand FROM Programmers

When joining tables with plural names, pleasant-wise, all bets are off

English plural 101:
http://en.wikipedia.org/wiki/English_plural


[UPDATE March 11 2011]

My desire to name a DbSet on Entity Framework was not honored, EF tried to look for People table for my public DbSet Persons, need to override things on OnModelCreating :-)

Linq to SQL is not a perfect match for reporting needs

Is clean (or performant) SQL achievable in Linq to Sql?

I wanted Linq to Sql produce this code:

SELECT C.CustomerID, COUNT(O.CustomerID) AS N
FROM Customers C
LEFT JOIN Orders O ON O.CustomerID = C.CustomerID
GROUP BY C.CustomerID

Here's my first attempt:

var q = 
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y
select new { y.Key, N = y.Count(t => t.CustomerID != null) };

First, my beef with Linq is it cannot allow the natural way to write a query. For comparison expressions, it is best to put on the left side the value/expression that is varying much, the less varying ones(e.g. constants) should be on the right side. To convey the point:

int customerId = 7;
bool found = false;
for(int i = 0; i < orders.Length; ++i)
{
    if(orders[i].CustomerId == customerId)
    {
        found = true;
        break;
    }
}

Linq doesn't allow this kind of statement:

var q = from c in db.Customers
        join o in db.Orders on o.CustomerID equals c.CustomerID into sr

Writing query on this form: from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into sr is akin to doing this code:

int customerId = 7;
bool found = false;
for(int i = 0; i < orders.Length; ++i)
{
    if(customerId == orders[i].CustomerId)
    {
        found = true;
        break;
    }
}

Well that code just doesn't feel natural, isn't it?


Back to the gist of this topic, is performant code achievable in Linq to SQL?

The Linq to SQL code above produces:

SELECT [t2].[CustomerID] AS [Key], (
    SELECT COUNT(*)
    FROM [Customers] AS [t3]
    LEFT OUTER JOIN [Orders] AS [t4] ON [t3].[CustomerID] = [t4].[CustomerID]
    WHERE ([t4].[CustomerID] IS NOT NULL) AND ((([t2].[CustomerID] IS NULL) AND ([t3].[CustomerID] IS NULL)) OR (([t2].[CustomerID] IS NOT NULL) AND ([t3].[CustomerID] IS NOT NULL) AND ([t2].[CustomerID] = [t3].[CustomerID])))
    ) AS [N]
FROM (
    SELECT [t0].[CustomerID]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    GROUP BY [t0].[CustomerID]
    ) AS [t2]

Well, that is far cry from the simple thing we want to achieve; that is, Linq's Count(expression here) should not produce COUNT(*), it should produce COUNT(O.CustomerId), but since we cannot feed non-lambda operation on Linq's Count's parameter, we must do: select new { y.Key, N = y.Count(t => t.CustomerID != null) };. And that code produces inefficient query, look at the Sql output above.

Here's my second attempt...

var q = 
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y                                        
select new { y.Key, N = y.Sum(t => t.CustomerID != null ? 1 : 0 )};

...that code produces:

SELECT SUM(
    (CASE
        WHEN [t1].[CustomerID] IS NOT NULL THEN @p0
        ELSE @p1
     END)) AS [N], [t0].[CustomerID] AS [Key]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID]

Though cleaner and performance-wise is passable, it still leave much to be desired, it doesn't inline constants; parameters might be the way of Linq to Sql for passing values or expressions to query(be it a variable or constant), however it still doesn't have a natural feel into it. As far as I know, there's no way to make Linq-to-Sql produce this SQL code: COUNT(O.CustomerId)


Linq to Sql is good as an ORM, storage persistence, fetching data, CRUD operations; but for reporting needs, I wouldn't bet my farm on it though. I would continue using plain old combo of SP,functions,views and ADO.NET for reporting needs.

Fibonacci using SQL

An exercise of logic, thinking of solution on-the-fly:

create or replace function generate_fib(_count int) returns table(n numeric)
as
$$
 
with recursive fib(cnt, a, b) as
(
 select 1, 0::numeric, 1::numeric
 union all
 select cnt + 1, b, a + b from fib where cnt <= $1 - 1
)
select a from fib;

$$ language 'sql';
 
select n from generate_fib(1000); -- will generate 1,000 fibonacci

Custom sort order on hierarchical query

How to make Project Manager 2 sort before Project Manager 1. Tester 4 sort before Tester 3?

emp_id |            emp_name             
--------+---------------------------------
      1 | President
      2 |   Vice President
      3 |     CEO
      4 |     CTO
      5 |       Group Project Manager
      6 |         Project Manager 1
      8 |           Team Leader 1
      9 |             Software Engineer 1
     10 |             Software Engineer 2
     11 |           Test Lead 1
     12 |             Tester 1
     13 |             Tester 2
      7 |         Project Manager 2
     14 |           Team Leader 2
     15 |             Software Engineer 3
     16 |             Software Engineer 4
     17 |           Test Lead 2
     18 |             Tester 3
     19 |             Tester 4
     20 |             Tester 5
(20 rows)

Why is EXISTS (SELECT 1 ...) a cargo cult programming?

Why application programmers are using this kind of data existence checking...

select author_id, post_id, post from posts 
where exists(select 1 from comments where author_id = posts.author_id and post_id = posts.post_id)

 author_id | post_id |         post
-----------+---------+----------------------
       777 |       1 | what is optimization
(1 row)

...instead of this?

select author_id, post_id, post from posts 
where exists(select * from comments where author_id = posts.author_id and post_id = posts.post_id)

To create test data, expand the following:

create table posts
(
author_id int not null, -- composite pk
post_id int not null, -- composite pk
post varchar(50)  not null
);

insert into posts(author_id, post_id, post) values(777, 1, 'what is optimization');
insert into posts(author_id, post_id, post) values(777, 2, 'how to profile');

create table comments
(
author_id int not null,
post_id int not null,
commenter_id int not null,
comment varchar(50)
);

insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'magnificent');
insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'great');
insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'excellent');

Why use 1 instead of asterisk? Most application programmers when are in sight of asterisk inside EXIST clause, they cringe for its purported inefficiency, they thought that asterisk always project data, and as such can incur delays to query. They have this ill-conceived notion that the asterisk fetches columns. If there's a way to avoid the SELECT asterisk altogether, all will be happy, no one will have a nagging feeling that the engineers of their favorite database are not smart.

Any takers of this syntax?

select * from posts 
where exists( from comments where author_id = posts.author_id and post_id = posts.post_id)

That EXIST clause doesn't have a look and feel of projecting unnecessary data. Now, you might not immediately like that (though might be if you are C# programmer that is accustomed to Linq syntax), so for better or for worse, we have to contend with the fact that in order to make parsing job easier, the language designer have designed that the query inside of EXISTS clause must conform to normal SQL syntax too. Language aesthetics or language symmetry also played a factor here.

Now are the irrational feelings that the asterisk inside EXISTS project data warranted? It's not, contrary to popular belief, database engineers are smarter than you and I will ever be. Do application programmers think that the database vendors wanted to lose their product mindshares to their competitors?

Database is a complex product that potential bottleneck like that construct won't let go past unnoticed, the keyword EXISTS is already a directive to RDBMS that data projecting should be suppressed from query inside of EXISTS. Why should they blindly project data inside of EXISTS?

To disprove that irrational fear that EXISTS clause is projecting data(it isn't), try this in your favorite database:

select * from posts 
where exists(select 1/0 from comments where author_id = posts.author_id and post_id = posts.post_id)

Did it result to divide by zero error? If it is, try to hurl criticisms to the vendor of your favorite database. But rest assured, we can be practically sure that our database won't blindly project data inside of EXISTS clause.

If only all database supports tuple testing, we can do this:

select * from posts where (author_id,post_id) in (select author_id, post_id from comments)

But some database don't support that tuple testing. So we have to do this:

select * from posts 
where /* (author_id,post_id) in */
exists 
    (select author_id, post_id 
    from comments 
    where author_id = posts.author_id and post_id = posts.post_id)

If I would have to translate the tuple testing to WHERE EXISTS clause, I would retain the two fields in first WHERE clause and put them in comment brackets; and inside of EXISTS clause, I would preserve the tuples to be tested upon, it makes for great documentation. It's better than EXISTS(SELECT 1) and EXISTS(SELECT *)

On some database, they have sleep function to simulate delay. To prove that our database discard data projection of EXISTS clause. Try to do this (change the pg_sleep with your database's equivalent):

select * from posts 
where exists(
select pg_sleep(5000) -- 5 seconds delay
from comments where author_id = posts.author_id and post_id = posts.post_id)

test=# select * from posts 
where exists(
    select pg_sleep(5000) 
    from comments 
    where author_id = posts.author_id and post_id = posts.post_id);

 author_id | post_id |         post
-----------+---------+----------------------
       777 |       1 | what is optimization
(1 row)

Time: 1.548 ms

As we can see, it didn't incur any delay

Strive for readability first before applying cargo cult programming. Don't have that irrational fear that asterisk always gets expanded to data projection

Now, I'm not suggesting to application programmers that they convert all their EXISTS(SELECT 1 FROM ..) to EXISTS(SELECT * FROM ..). Just remove the irrational fear, eliminate or minimize your Cargo Cult Programming.

I don't mind if application programmers or colleagues are used to with EXISTS(SELECT 1..), but I would do mind if they will go out their way to bikeshed and point out that EXISTS(SELECT * ) is not efficient. I trust the engineers of database more than the programmers who just merely use database.

Debunking the myth that COUNT('Dracula') is faster than COUNT(*)

Ok, that was an attempt at humor ;-)

How to debunk the myth that COUNT(1) is faster than COUNT(*) ?

The most simple mistaken assumption is that the asterisk sign makes the RDBMS read all the columns. Fact is, asterisk just represent the cardinality(an unfortunate name, very computer-sciencey-sounding) of the set

Thinking that COUNT(1) or COUNT(anyNonNullConstantHere) is faster than COUNT(*), is the most common programming-by-superstition done by many. First point, if this language construct had made it into SQL standard...

SELECT COUNT(cat)
FROM cats

...we will not need COUNT(*) (and COUNT(1) for that matter) in SQL language, but as RDBMS don't have an AI to infer that the entity(cat) you want to count is the singular form of the cats table, this didn't make it into the final ANSI SQL spec


Second point, a compromise, if this language construct permits counting the table name directly...

SELECT users.user_id, COUNT(answers)
FROM users
JOIN answers ON answers.user_id = users.user_id
GROUP BY users.user_id

...we will not need COUNT(*) in SQL languages, it's very natural to read. But only Postgres supports this principle. But that will still fail if there's a nullable answers(same name as its table) column in either of the two table, as Postgres gives more priority to field than table; though can be worked around if we put asterisk after the name, so the RDBMS can detect that the thing we we wanted to count is the table rows rather than the column name:

SELECT users.user_id, COUNT(answers.*)
FROM users
JOIN answers ON answers.user_id = users.user_id
GROUP BY users.user_id

The ultimate proof that the database isn't reading all columns, this script outputs 4, not 0.

create table person
(
lastname varchar(50),
firstname varchar(50),
age int
);

insert into person(lastname,firstname,age) values(null,null,null);
insert into person(lastname,firstname,age) values(null,null,null);
insert into person(lastname,firstname,age) values(null,null,null);
insert into person(lastname,firstname,age) values(null,null,null);

select count(*) from person; -- output is 4, not 0

If the RDBMS reads all the columns, why the count still resolves to 4? Shouldn't it be 0 since all the values are null?

What makes the COUNT(1) different from COUNT(0), COUNT(2)? COUNT('your blessings')? COUNT('Dracula')? Answer: None. This will all output 4 on the above data. There's nothing special about COUNT(1) or COUNT(anyNonNullValues) that makes it different from COUNT(*); there's nothing slow in COUNT(*) as we can plainly see that from the output above, that it don't care whatever the column values are. How can COUNT(1) be faster than COUNT(*) when COUNT(*) doesn't evaluate any values on all columns?


And one could argue as well that COUNT(*) is faster than COUNT(fieldhere) or COUNT(1) for that matter, since it doesn't need interpreting any when it count rows.


The asterisk inside of COUNT is just a directive for the RDBMS to fetch the cardinality of the set, not to evaluate all columns of the row.

And SELECT COUNT(1) is a one trick pony, it's only pleasant and possible to use on one table query only.

SELECT COUNT(1) FROM person

You cannot mold the COUNT(1) for LEFT JOINed tables

Won't work on other scenarios(e.g. counting all bids of person, regardless if he/she has bids):

SELECT person.person_id, COUNT(bid.1) -- ugh! won't work, syntax error
FROM person
LEFT JOIN bid ON bid.person_id = person.person_id
GROUP BY person.person_id

This is the proper way to do it:

SELECT person.person_id, COUNT(bid.person_id) -- works on all RDBMS(Sql Server, Postgresql, Mysql, Access, etc)
FROM person
LEFT JOIN bid ON bid.person_id = person.person_id
GROUP BY person.person_id

This is the proper way to do it:

-- works on Postgresql only(afaik). More intuitive, you don't count the column, you count the cardinality of the set
SELECT person.person_id, COUNT(bid.*) 
FROM person
LEFT JOIN bid ON bid.person_id = person.person_id
GROUP BY person.person_id

/* COUNT(1) is a mistake on queries like below, 
this will always return at least 1 bid belonging to person, even that person don't have any bids. 
Not because the parameter is 1, even if you change the parameter of COUNT to COUNT(2), it won't double the rows count. 

You will never get a COUNT of 0 with COUNT(1), or even COUNT(0) will not result to a COUNT of 0 when you are using LEFT JOIN 


*/
SELECT person.person_id, COUNT(1) 
FROM person
LEFT JOIN bid ON bid.person_id = person.person_id
GROUP BY person.person_id

So if COUNT(1) is just a one-trick pony, why others are still using it on their queries? Cargo cult programming perhaps?


COUNT(*) is such a common programming idiom to be misinterpreted by the database vendor's engineers; to instill one a confidence that COUNT(*) is not slow compared to COUNT(1), one must visualize that the asterisk inside of COUNT(*)indicates cardinality of the set, it doesn't pertain to columns, full stop. Asterisk on COUNT(*) has no bearing with the asterisk on SELECT *, they just share a common token, i.e. asterisk. Database vendor's engineers are smarter than you and I will ever be, they won't dumbly implement the asterisk inside of COUNT to perform reading all columns, asterisk inside of COUNT indicates set cardinality.


Well it's just an unfortunate thing also that this doesn't make it to ANSI SQL:

SELECT COUNT() FROM person;

That will surely make debating 1 vs asterisk inside a function a moot point. The recommended way to give the RDBMS the hint that you want to count the cardinality of set is to use asterisk, rather than using 1; the parameterless COUNT was not able to made its way on ANSI SQL language constructs though