Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is


SELECT COUNT(*) OfflineData

Do you see the bug?


Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case?

It returns 1.

So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes?
An way is very easy; always prefix your table names with schema. Besides this bug there are a lot of other benefits from prefixing your tables names with schema.

In my client's case, if OfflineData had been prefixed with dbo, the query wouldn't parse and you get a compile error.
Next thing to do to avoid stupid mistakes is to put AS before alias names, and have alias names after the expression.

SELECT COUNT(*) AS MyCount FROM dbo.OfflineData

http://weblogs.sqlteam.com:8081/peterl/archive/2011/09/22/avoid-stupid-mistakes.aspx