Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Thursday, September 22, 2011 8:38 AM | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 Miscellaneous Denali ]

Feedback

Gravatar

# re: Avoid stupid mistakes

This sounds quite easy.. :D anyway is easier to avoid than to repair..so good point! :D
10/27/2011 11:23 AM | Sandra
Gravatar

# 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.
2/18/2012 1:16 AM | Jerry2
Gravatar

# re: Avoid stupid mistakes

This is why I want to force AS before column alias. Read this post beyondrelational.com/...
9/14/2012 7:39 AM | Madhivanan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET