Chapter 9. Avoiding three common query mistakes
Writing correct and well-performing queries is both an art and a science. The query must return the expected results and execute in a reasonable time. Many blogs and articles have been written about improving query performance, but this chapter will focus on common mistakes that ultimately cause incorrect or incomplete data to be returned. These are problems I have frequently been asked to help solve or have encountered myself.
The examples in this chapter use SQL Server 2008 and the AdventureWorks2008 database that is available for download at http://www.codeplex.com. Search for “SQL Server 2008 code samples” on the site to find the latest release of the database. The queries will also work with the SQL Server 2005 version of the AdventureWorks database.
The NULL value means unknown; no value has been assigned. This is not the same as an empty string or zero. As long as the ANSI_NULLS setting is turned on, which is the default, comparing a value to NULL returns unknown. One usually expects a value of TRUE or FALSE when making comparisons, but unknown complicates matters under certain conditions.
