01
Apr
10

Chapter 9 — Avoiding three common query mistakes

Chapter 9 (Avoiding Three Common Query Mistakes) describes three typical pitfalls that are often encountered by query writers of all levels.  The first section of the chapter pertains to null values and the proper method of writing queries to evaluate fields that potentially contain null values.  Next, the author describes the reason to write queries containing multiple outer joins with left outer joins instead of using right outer joins.  Finally, the author describes the group by clause in an effort to exemplify why aggregate queries often cause errors at execution time or, even worse, do not return the correct data. 

I will leave the specifics of the above mentioned common mistakes to the author’s chapter but do want to say I can’t agree more that the three mistakes described above are both common and can easily be avoided with a basic understanding of set theory and aggregation.

Throughout the course of my career I have played the developer, report writer, database administrator, and “insert role that pulls data from a database here________” for multiple organizations in various industries.  Arguably the most common mistake I have observed when analyzing why queries error out or do not return the expected result set is because the query writer (many times me being the culprit) does not properly acclimate themselves with the underlying data model prior to writing a query. 

I can’t begin to count the amount of times I have fired up Management Studio, written a query, thought I had the correct result set, and eventually realized I wasn’t even close due to a miss-join or not accounting for a null value.  At this point in my career I would characterize myself as an expert query writer (especially when writing queries against a SQL Server database).  I know how to handle null values from both logic and syntax perspectives (I am practically best friends with the ISNULL function).  How is it then that I still end up writing a nested-query that returns zero records?  In short, it is because I did not realize that the table I was querying in the sub-query was going to return a null value (see author’s comment on null values in nested queries).  The scratching of my head, after I get my zero-record result set, could have been avoided if I had just taken the time to properly understand the nature of the data I was trying to retrieve.

As another trivial example, the following error messages can be used to exemplify how understanding the data model prior to writing a query will help to save you time and return the anticipated result upon execution.

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table ‘example’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

This message was returned when I executed the following query.

insert into example values (1, ‘some value’)

If I had looked at the table definition prior to writing the query I would have realized the correct query would have been:

insert into example values (‘some value’)

Obviously, a very simple example but I believe it serves the purpose of illustrating the importance of understanding the data model prior to writing the query.

Before you close your browser and kick me off my soapbox let me say I completely understand that many times querying a database is more of an art form and less of a science.  From a cost/time perspective taking the time to fully understand the underlying data model may not be feasible. I don’t recommend telling your client that you will spend the next five hours looking at a data model to sum a single field in one table.  In fact, I myself will continue to fire up Management Studio and scratch my head when my result set isn’t as expected.  In short, what I am trying to say is, when it comes down to it, and you need to write a query that you not only know will perform but return the proper result set, know your data/database prior to writing the query.

 

Derrick Hadzima is the Director of Technology Solutions & Data Architecture at RevGen Partners. He developed his expertise by supporting large-scale enterprises in high-transaction industries including telecommunications, manufacturing and healthcare. He maintains technical and functional expertise in multiple platforms and languages including SQL Server, SharePoint and .NET.   He is a coauthor of Social Computing with Microsoft SharePoint 2007: Implementing Applications for SharePoint to Enable Collaboration and Interaction in the Enterprise.

Advertisements

0 Responses to “Chapter 9 — Avoiding three common query mistakes”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers