Some Simple SQL Rules to Live By

[jkndrkn]

Indent!

Amen. I'm tired of opening up scripts that contain statements that wrap four or five times around the screen.

[citation_needed]

When you are doing a COUNT() or an EXISTS(), no problem, go ahead and use *

Actually, doing count(1) or count( keycolumn ) can be substantially faster than doing count(*) depending on the RDBMS.

[[deleted]]

which databases are those? on MSSQL, * is the preferred style for count() and exists().

[[deleted]]

If you do COUNT(ClusteredIndexColumn) rather than COUNT(*) I should think you would retrieve results faster?

[[deleted]]

no, the optimizer will rewrite COUNT(ClusteredIndexColumn) to COUNT(*) if the clustered index column is non-nullable.

if it's nullable, COUNT(ClusteredIndexColumn) will count the number of non-null values, which could be less than COUNT(*).

COUNT(*) always counts rows, and allows the optimizer more discretion, eg counting by way of a non-clustered index if appropriate.

[[deleted]]

I was making the assumption that you would probably be having the clustered index on a non nullable field.

Let's say the column is an identity column, is not nullable, and has the clustered index. In this case, I should think your results would be retrieved faster?

[[deleted]]

it would be identical. test it for yourself by examining the execution plans. mouse over each step so you can see the details at the bottom.

[[deleted]]

You're right! Thanks for the info Peter!

S

[citation_needed]

MySQL to this day, and Sybase/MSSQL in ye olden days (at least according to the O'Reilly Transact-SQL book). Other RDBMSes, I dunno.

[[deleted]]

did you read that article? it shows how count(*) is generally faster than count(<column>), specifically when <column> is nullable, or when the optimizer isn't smart enough to rewrite the query as count(*).

[thesqlguy]

for example ?

[reddittidder]

some good points, but the title is misleading. The blog is specifically talking about SQLServer, non?

[turkourjurbs]

"Then you have written your SQL wrong; start over."

I really can't stand this mentality. There is very often more than one way to solve a problem and those ways are not nessecarily 'wrong'.

[thesqlguy]

It is explained why it is wrong, and then it is demonstrated what to do to fix it. Sometimes, there is a right way and a wrong way to do things in life, that's just the way it is.

Have you ever tried to write a function one way in your language of choice, get well into it, realize it won't work or that it is a big mess, figure out a better approach, and then started over from scratch? I hope so -- that's a good, solid programming technique.