Peter Larsson Blog

Patron Saint of Lost Yaks

Avoid stupid mistakes

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

Legacy Comments

re: Avoid stupid mistakes
This sounds quite easy.. :D anyway is easier to avoid than to good point! :D

re: Avoid stupid mistakes
Ha, I could not figure out what was wrong for over a full minute. I can't believe I forgot about the FROM clause. This is why I come to this site to learn all I can about SQL so that I can run the Indianapolis Jiu Jitsu website I work on as smoothly as possible. If I run into any errors or have any general questions I'll be sure to post them to this blog. You all are a great source of information for people like me.

re: Avoid stupid mistakes
This is why I want to force AS before column alias. Read this post