{"id":2049,"date":"2015-06-25T00:00:00","date_gmt":"2015-06-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-get-nulls-horribly-wrong-in-sql-server\/"},"modified":"2021-09-29T16:21:25","modified_gmt":"2021-09-29T16:21:25","slug":"how-to-get-nulls-horribly-wrong-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/how-to-get-nulls-horribly-wrong-in-sql-server\/","title":{"rendered":"How to Get NULLs Horribly Wrong in SQL Server"},"content":{"rendered":"<div id=\"pretty\">\n<ul>\n<li><a href=\"#first\">Failure #1: Not knowing what NULL means<\/a><\/li>\n<li><a href=\"#second\">Failure #2: Treating NULL like a real value in comparisons<\/a><\/li>\n<li><a href=\"#third\">Failure #3: Not knowing how ANSI_NULL works<\/a><\/li>\n<li><a href=\"#fourth\">Failure #4: Treating ISNULL the same as IS NULL<\/a><\/li>\n<li><a href=\"#fifth\">Failure #5: Treating ISNULL the same as COALESCE<\/a><\/li>\n<li><a href=\"#sixth\">Failure #6: Forgetting about the NULLIF function<\/a><\/li>\n<li><a href=\"#seventh\">Failure #7: Assuming that 2 + NULL = 2<\/a><\/li>\n<li><a href=\"#eighth\">Failure #8: Aggregating data without taking NULL into account<\/a><\/li>\n<li><a href=\"#ninth\">Failure #9: Forgetting about NULL in your Boolean expressions<\/a><\/li>\n<li><a href=\"#tenth\">Failure #10: Not accounting for NULL in your string expressions<\/a><\/li>\n<li><a href=\"#eleventh\">Failure #11: Joining tables on nullable columns<\/a><\/li>\n<li><a href=\"#twelveth\">Failure #12: Creating a unique index without taking NULL into account<\/a><\/li>\n<li><a href=\"#thirteenth\">Failure #13: Sorting data on a nullable column<\/a><\/li>\n<\/ul>\n<h1 id=\"first\">Failure #1: Not knowing  what  <code>NULL<\/code>  means<\/h1>\n<p>When referring to <b> <code>NULL<\/code><\/b> within our  data sets, we often use phrases such as &#8220;the <b> <code>NULL<\/code><\/b> value&#8221; or &#8220;a  value of <b> <code>NULL<\/code><\/b>.&#8221; I do it all the time. I see it done all  the time. Such phrases are so common that we think little of their use. But  inherent in the phrase is the notion of <b> <code>NULL<\/code><\/b> as an actual  value. It is not. <b> <code>NULL<\/code><\/b> is a  non-value, a nonexistent value. It is not zero. It is not an empty string. A  value cannot equal <b> <code>NULL<\/code><\/b>. No two <b><code>NULL<\/code><\/b>  values are equal.<\/p>\n<p>A <b> <code>NULL<\/code><\/b> value is often defined as one that is  unknown or not applicable, but even these definitions can be open to debate. For  example, a record might not include a customer&#8217;s birthdate because the  salesperson didn&#8217;t ask or because the customer would not provide it, but the  customer still knows that date, so it is hardly unknown, nor is it any less  applicable. There can be many reasons for missing data, and there has been much  debate about a precise meaning of <b> <code>NULL<\/code><\/b>. If you need  to attach a specific meaning to <b> <code>NULL<\/code><\/b>, then <i>missing <\/i>or <i>absent <\/i>data is probably your safest bet.<\/p>\n<p>Perhaps a better way to think of <b> <code>NULL<\/code><\/b> is as a  setting or marker that indicates if a data value does not exist. That certainly  seems to be how SQL Server treats <b> <code>NULL<\/code><\/b>. The database  engine uses a special bitmap to track which columns in a row are <b> <code>NULL<\/code><\/b> and which are not. The bitmap contains a bit for each  column, with the bit set to 1 if the column is <b><code>NULL<\/code><\/b>, that  is, if the value is missing.<\/p>\n<p>The confusion for some, particularly beginning T-SQL developers, is the  way SQL Server Management Studio indicates that no value exists for a specific  column: by displaying what appears to be a value that reads <b> <code>NULL<\/code><\/b>. For example, the following <b><code>SELECT<\/code><\/b>  statement returns data from the <b> <code>Product<\/code><\/b> table in  the <b> <code>AdventureWorks2014<\/code><\/b> sample database:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, ProductLine, \n&#160; Class, Style, SellEndDate\nFROM Production.Product\nWHERE ProductID BETWEEN 680 AND 715;\n<\/pre>\n<p>As the following results show, every row includes at least one column  for which no value exists:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductLine<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStyle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tSellEndDate<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">R   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">R   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Red<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Black<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Bike Socks, M<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2012-05-29 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Bike Socks, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2012-05-29 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Blue<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tAWC Logo Cap<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, M<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>If we look at the columns with the <b> <code>NULL<\/code><\/b> values, we  can see how difficult it is to determine why the values are missing. For  example, is a <b> <code>SellEndDate<\/code><\/b> value missing because the product is still live  or because the application failed to properly record the end date? Are the <b> <code>Class<\/code><\/b> and <b> <code>Style<\/code><\/b> values  missing because they don&#8217;t apply to those particular rows or because someone was  careless in entering data?<\/p>\n<p>An even bigger problem with viewing the results in this way is that it  suggests that the database is actually storing multiple instances of this  mysterious placeholder called <b> <code>NULL<\/code><\/b> and that all  those <b> <code>NULL<\/code><\/b> values are the same. They are not the same.  There is nothing there. Nothing cannot be the same as nothing. Nothing cannot be  compared to nothing. You can think of these as the fundamental principles behind <b> <code>NULL<\/code><\/b>. The  principles of nothingness. And not understanding these principles can lead to  all sorts of problems when working with SQL Server, resulting in many of the  failures we cover here. <\/p>\n<h1 id=\"second\">Failure #2: Treating  NULL  like a real value in comparisons<\/h1>\n<p>Developers who don&#8217;t understand how <b><code>NULL<\/code><\/b> works  will sometimes use comparison operators to compare an expression to a <b> <code>NULL<\/code><\/b> value, resulting in a statement that looks similar to  the following:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title = NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>The assumption here is that <b> <code>NULL<\/code><\/b> is a valid  value and therefore can be compared to other <b> <code>NULL<\/code><\/b> values. That  means, in this case, the statement should return each row whose <b> <code>Title<\/code><\/b> value is <b> <code>NULL<\/code><\/b>. A quick  check into the source data will show four rows that match the statement&#8217;s  assumed logic. However, because the database engine cannot compare nothing to  nothing, it will not return these rows and will instead provide us with an empty  result set.<\/p>\n<p>If we assume from the empty result set that no rows in the source data  match our criteria, we might be satisfied with the statement as is and send the  code onto production. (Let&#8217;s hope no one would really do that.) We might also  choose to continue along this logical trajectory and recast the statement to  return the non-<b><code>NULL<\/code><\/b> data:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title &lt;&gt; NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>The statement is no longer trying to compare <b><code>NULL<\/code><\/b> with <b> <code>NULL<\/code><\/b>, but rather compare real non-<b><code>NULL<\/code><\/b> values to <b> <code>NULL<\/code><\/b>, which would  seem an equally credible comparison, if not more so. It is not. We cannot  compare real values to nothing any more than we can compare nothing to nothing.  As a result, the statement still returns no data, even though we know for  certain the data exists.<\/p>\n<p>But before we go further, note that there are cases when comparing <b> <code>NULL<\/code><\/b> values in this way can work, which we cover in the next  failure. For now, know that the proper way to perform <b><code>NULL<\/code><\/b>  comparisons is to use the <b> <code>IS<\/code><\/b> <b><code>NULL<\/code><\/b> and <b> <code>IS<\/code><\/b> <b> <code>NOT<\/code><\/b> <b> <code>NULL<\/code><\/b> operators. For example, the following <b> <code>SELECT<\/code><\/b> statement uses the <b><code>IS<\/code><\/b> <b> <code>NULL<\/code><\/b> operator to return rows whose <b><code>Title<\/code><\/b>  value is <b> <code>NULL<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title IS NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>This time, the statement returns the data we expect, with the <b> <code>Title<\/code><\/b> value missing, along with a couple of <b> <code>MiddleName<\/code><\/b> values:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tTitle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tMiddleName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tLastName<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tKen<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">J<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tS&#195;&#161;nchez<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTerri<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLee<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tDuffy<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRoberto<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTamburello<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRob<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tWalters<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We can also return those rows that contain an actual <b> <code>Title<\/code><\/b> value by instead using the <b><code>IS<\/code><\/b> <b> <code>NOT<\/code><\/b> <b> <code>NULL<\/code><\/b> operator:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title IS NOT NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>Now the statement returns only the following two rows:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tTitle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tMiddleName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tLastName<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMs.<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGail<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">A<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tErickson<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMr.<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tJossef<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGoldberg<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The <b> <code>IS<\/code><\/b> <b> <code>NULL<\/code><\/b> and <b> <code>IS<\/code><\/b> <b> <code>NOT<\/code><\/b> <b> <code>NULL<\/code><\/b> operators provide the logic we need to handle the  ambiguous nature of <b> <code>NULL<\/code><\/b> values. The  purpose of these operators is to determine whether or not an expression is <b> <code>NULL<\/code><\/b> and from that return either <b><code>TRUE<\/code><\/b> or <b> <code>FALSE<\/code><\/b>. Comparison operators, when faced with a <b> <code>NULL<\/code><\/b> value, instead want to return <b><code>UNKNOWN<\/code><\/b>,  which can result in unpredictable and costly mistakes if implemented in  production. <\/p>\n<h1 id=\"third\">Failure #3: Not knowing  how  <code>ANSI_NULL<\/code>  works<\/h1>\n<p>In the last failure, I demonstrated why you should not use comparison  operators against <b> <code>NULL<\/code><\/b> values. Even  if there are results to be had, a statement such as the following will likely  not return them:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title = NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>The word <i>likely<\/i> is key here.  It turns out that the behavior I described is the default behavior, but not the  only behavior. SQL Server supports a feature called <b><code>ANSI_NULLS<\/code><\/b>,  which determines how the database engine handles <b><code>NULL<\/code><\/b>  comparisons. The option is a carry-over from the ghost of SQL Server Past and  has been deprecated since 2005. But the setting still exists, it still works,  and it can still cause all sorts of trouble.<\/p>\n<p>By default, SQL Server installs with the <b><code>ANSI_NULLS<\/code><\/b>  option turned on, which gives us the behavior I&#8217;ve described so far. If we  compare values to <b> <code>NULL<\/code><\/b>, the database  engine does not return those rows, even if <b> <code>NULL<\/code><\/b> exists.  <\/p>\n<p>We can verify the current <b> <code>ANSI_NULLS<\/code><\/b> setting  by running a statement similar to the following:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF ( (32 &amp; @@OPTIONS) = 32 ) \n&#160; PRINT 'The ANSI_NULLS option turned on.'\nELSE\n&#160; PRINT 'The ANSI_NULLS option turned off.';\n<\/pre>\n<p>If no one has messed with <b> <code>ANSI_NULLS<\/code><\/b>, the  database engine will return the first option, which will give us the results  we&#8217;ve seen so far. But suppose we turn the option off:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET ANSI_NULLS OFF;\nSELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title = NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>Now our statement returns the expected four rows, despite the fact that  we&#8217;re using a comparison operator against <b> <code>NULL<\/code><\/b>:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tTitle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tMiddleName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tLastName<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tKen<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">J<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tS&#195;&#161;nchez<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTerri<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLee<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tDuffy<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRoberto<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTamburello<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRob<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tWalters<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>With the option turned off, we can also use the not equal operator:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET ANSI_NULLS OFF;\nSELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title &lt;&gt; NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>Now the results look like the following:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tTitle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tMiddleName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tLastName<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMs.<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGail<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">A<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tErickson<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMr.<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tJossef<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGoldberg<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>If we turn the option back on (it&#8217;s default setting), the database  engine will again return no rows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET ANSI_NULLS ON;\nSELECT Title, FirstName, MiddleName, LastName\nFROM Person.Person\nWHERE Title = NULL AND BusinessEntityID &lt; 7;\n<\/pre>\n<p>Because the <b> <code>ANSI_NULLS<\/code><\/b> option  has been deprecated, you should not use it and you should update any code that  does, assuming you have the access and time. If you don&#8217;t, you could be in store  for bigger problems.<\/p>\n<p>Imagine an application that connects to two different SQL Server  instances, one with the option turned on (the default) and one with it turned  off. If the code is using comparison operators with <b><code>NULL<\/code><\/b>  values, you could end up with inaccurate data or conflicting results. Even if  you&#8217;re dealing with only one SQL Server instance, you still risk unexpected  results, especially if statements are turning the <b><code>ANSI_NULLS<\/code><\/b>  option on at the session level.<\/p>\n<h1 id=\"fourth\">Failure #4: Treating  ISNULL  the same as  IS  NULL<\/h1>\n<p>The <b> <code>ISNULL<\/code><\/b> function tends to cause a bit of  confusion when first encountered, in part because of its name, which implies a  meaning similar to the <b> <code>IS<\/code><\/b> <b><code>NULL<\/code><\/b>  operator. Another reason for confusion arises from the fact that products such  as Microsoft Access and MySQL also include the <b><code>ISNULL<\/code><\/b>  function, where it behaves much differently, more like the name suggests. But  we&#8217;re talking SQL Server and need to understand how <b><code>ISNULL<\/code><\/b> is  implemented here. A lack of understanding can lead to misuse and failure to take  advantage of its real purpose in SQL Server.<\/p>\n<p>Let&#8217;s looks at a few examples to demonstrate, starting with a simple query  that returns products whose <b> <code>Color<\/code><\/b> value is <b> <code>NULL<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND Color IS NULL;\n<\/pre>\n<p><\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>If we&#8217;re not familiar with <b> <code>ISNULL<\/code><\/b> in SQL  Server or are transitioning in from Access, we might attempt to retrieve the  same results by using <b> <code>ISNULL<\/code><\/b> in our <b><code>WHERE<\/code><\/b>  clause:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND ISNULL(Color);\n<\/pre>\n<p>Fortunately, this construction will generate the following error, which  we&#8217;ll likely catch immediately, or at least during the Q&amp;A phase:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 174, Level 15, State 1, Line 305\nThe isnull function requires 2 argument(s).\n<\/pre>\n<p>Those of us with a background in MySQL might instead take a different  approach with the <b> <code>ISNULL<\/code><\/b> function by  comparing it to 1 or 0, the function&#8217;s two possible return values:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND ISNULL(Color) = 1);\n<\/pre>\n<p>Once again, we receive an error message:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 174, Level 15, State 1, Line 322\nThe isnull function requires 2 argument(s). near ';'.\n<\/pre>\n<p>By now, we should have checked the documentation to verify what this  function is supposed to do and have come to the conclusion that, based on the  syntax <i>and<\/i> the fairly explicit error  messages, we should be adding a second argument, which we do:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND ISNULL(Color, 'neutral');\n<\/pre>\n<p>Unfortunately, all we&#8217;ve done is make our predicate completely  illogical, resulting in yet another (but different) error message:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 4145, Level 15, State 1, Line 339\nAn expression of non-boolean type specified in a context where a condition is expected, near ';'.\n<\/pre>\n<p>The point of that this is that, in SQL Server, the purpose of the <b> <code>ISNULL<\/code><\/b> function is simply to replace an expression&#8217;s value  with a real value, if the original value is <b><code>NULL<\/code><\/b>  (nonexistent). If we wanted to make the preceding example right, we would need  to define our Boolean condition correctly:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND ISNULL(Color, 'neutral') IN ('black', 'neutral');\n<\/pre>\n<p>Now the <b> SELECT<\/b> statement returns the results we  want, rather than an error:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Mountain Frame &#8211; Black, 40<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>That said, we can achieve the same thing by using <b><code>IS<\/code><\/b> <b> <code>NULL<\/code><\/b>, without incurring the extra processing:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949\n&#160; AND (Color = 'black' OR Color IS NULL);\n<\/pre>\n<p>More often than not, the place we&#8217;ll want to use the <b> <code>ISNULL<\/code><\/b> function is within our select list to replace the <b> <code>NULL<\/code><\/b> values with real values:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, ISNULL(Color, 'neutral')\nFROM Production.Product\nWHERE ProductID BETWEEN 940 AND 949;\n<\/pre>\n<p>The <b> <code>Color<\/code><\/b> column now contains a value for each row,  with no missing values to contend with:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Pedal<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver\/Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring Pedal<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver\/Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Mountain Frame-W &#8211; Silver, 38<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Mountain Frame &#8211; Black, 40<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Mountain Frame &#8211; Silver, 40<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Derailleur<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tneutral<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tneutral<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Brakes<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSilver<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Be aware that, when using <b> <code>ISNULL<\/code><\/b>, the  database engine converts the replacement value to the data type of the original  value, making its possible for data to get truncated or for the database engine  to generate a conversion error. Make sure you read up on <b><code>ISNULL<\/code><\/b>  before adding it to your code.<\/p>\n<h1 id=\"fifth\">Failure #5: Treating  ISNULL  the same as  COALESCE<\/h1>\n<p>In the last failure, I tried to address any confusion there might be  around the <b> <code>ISNULL<\/code><\/b> function, but there&#8217;s another area of  possible confusion, and that is in understanding the differences between <b> <code>ISNULL<\/code><\/b> and <b> <code>COALESCE<\/code><\/b>. Both  functions let us replace <b> <code>NULL<\/code><\/b> with a real  value, but there are differences between the two that can be important to  understand.<\/p>\n<p>First off, the <b> <code>ISNULL<\/code><\/b> function  implemented in SQL Server is specific to that brand of T-SQL, whereas <b> <code>COALESCE<\/code><\/b> is standard ANSI, making it more universal and,  consequently, more portable, an important consideration if the possibility  exists that you might one day want to point your apps to a different database  system.<\/p>\n<p>The <b> <code>COALESCE<\/code><\/b> function also lets us include more than  two expressions, as shown in the following <b><code>SELECT<\/code><\/b>  statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Class, Style, ProductLine,\n&#160; COALESCE(Class, Style, ProductLine, 'n\/a') AS FirstNotNull\nFROM Production.Product\nWHERE ProductID IN(679, 706, 709, 711);\n\t<\/pre>\n<p>The function evaluates the expressions in the order they&#8217;re specified  and returns the first non-<b><code>NULL<\/code><\/b>  value. As the following results show, each row returns one of the specified  values in the <b> <code>FirstNotNull<\/code><\/b> column:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStyle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductLine<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRear Derailleur Cage<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tn\/a<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">R   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Bike Socks, M<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Blue<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">S   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The <b> <code>COALESCE<\/code><\/b> function can actually be thought of as  syntactical shorthand for a <b> <code>CASE<\/code><\/b> expression:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Class, Style, ProductLine,\n&#160; CASE\n&#160;&#160;&#160; WHEN Class IS NOT NULL THEN Class\n&#160;&#160;&#160; WHEN Style IS NOT NULL THEN Style\n&#160;&#160;&#160; WHEN ProductLine IS NOT NULL THEN ProductLine\n&#160;&#160;&#160; ELSE 'n\/a'\n&#160; END AS FirstNotNull\nFROM Production.Product\nWHERE ProductID IN(679, 706, 709, 711);\n<\/pre>\n<p>The <b> <code>SELECT<\/code><\/b> statement achieves the same results as  the preceding example, but its construction is more complex. The main advantage  of using a <b> <code>CASE<\/code><\/b> expression is that it makes the logic  clearer, for those who might need a little extra help.<\/p>\n<p>Compared to the <b> <code>COALESCE<\/code><\/b> function,  the <b> <code>ISNULL<\/code><\/b> function is much more limited:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Class, Style,\n&#160; ISNULL(Class, Style) AS FirstNotNull\nFROM Production.Product\nWHERE ProductID IN(679, 706, 709, 711);\n<\/pre>\n<p>In this case, all we&#8217;re saying is that if <b><code>Class<\/code><\/b> is <b> <code>NULL<\/code><\/b>, then use <b> <code>Style<\/code><\/b>, giving us  the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStyle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRear Derailleur Cage<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<b><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Bike Socks, M<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">U   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Blue<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We could have specified <b> <code>n\/a<\/code><\/b> as our second  argument, similar to what we did with <b> <code>COALESCE<\/code><\/b>, but you  get the point. <b> <code>COALESCE<\/code><\/b> is more flexible and it conforms to  ANSI standards. In addition, <b> <code>COALESCE<\/code><\/b> handles  data types differently. To understand this difference, let&#8217;s start with a  different example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Name AS ProductName, Class, Style, ProductSubcategoryID\nFROM Production.Product\nWHERE ProductID = 525;\n<\/pre>\n<p>The <b> <code>Class<\/code><\/b> and <b> <code>Style<\/code><\/b> columns are  defined as <b> <code>NCHAR<\/code><\/b>, and the <b><code>ProductSubcategoryID<\/code><\/b>  column as <b> <code>INT<\/code><\/b>. The <b> <code>SELECT<\/code><\/b> statement  returns the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStyle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductSubcategoryID<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Shell<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now lets modify our code to include the <b><code>COALESCE<\/code><\/b>  function:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Name AS ProductName, Class, Style, ProductSubcategoryID,\n&#160; COALESCE(Class, Style, ProductSubcategoryID) AS FirstNotNull\nFROM Production.Product\nWHERE ProductID = 525;\n<\/pre>\n<p>The database engine now generates an error when it tries to convert the <b> <code>Class<\/code><\/b> value to an <b><code>INT<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 245, Level 16, State 1, Line 415\nConversion failed when converting the nvarchar value 'L ' to data type int.\n<\/pre>\n<p>It turns out that <b> <code>COALESCE<\/code><\/b> uses the  type with the highest precedence, based on the submitted values. Because the <b> <code>ProductSubcategoryID<\/code><\/b> column takes an <b><code>INT<\/code><\/b> and an <b> <code>INT<\/code><\/b> takes precedence over <b><code>NCHAR<\/code><\/b>  (which is treated as <b> <code>NVARCHAR<\/code><\/b>), the  database engine tries to convert <b> <code>L<\/code><\/b> to an <b> <code>INT<\/code><\/b>, which of course is not possible.<\/p>\n<p>In such cases, we might try changing the order of the columns we pass to  the <b> <code>COALESCE<\/code><\/b> function:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Name AS ProductName, Class, Style, ProductSubcategoryID,\n&#160; COALESCE(ProductSubcategoryID, Class, Style) AS FirstNotNull\nFROM Production.Product\nWHERE ProductID = 525;\n<\/pre>\n<p>But we will again receive a conversion area. The database engine does  not care about the order of the arguments, only which types take precedence over  others:<\/p>\n<p>The <b> <code>ISNULL<\/code><\/b> function works differently:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Name AS ProductName, Class, ProductSubcategoryID,\n&#160; ISNULL(Class, ProductSubcategoryID) AS FirstNotNull\nFROM Production.Product\nWHERE ProductID = 525;\n<\/pre>\n<p>In this case, the <b> <code>Class<\/code><\/b> data type  sets the standard, so the database engine has no problem returning the results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductSubcategoryID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Shell<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now let&#8217;s reverse the order of the columns we pass to <b> <code>ISNULL<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Name AS ProductName, Class, ProductSubcategoryID,\n&#160; ISNULL(ProductSubcategoryID, Class) AS FirstNotNull\nFROM Production.Product\nWHERE ProductID = 525;\n<\/pre>\n<p>The database engine will once again try to convert <b><code>L<\/code><\/b> to an <b> <code>INT<\/code><\/b> and generate a conversion error. Be sure that, whenever  you&#8217;re using these functions, you are well aware of the possibility for  conversion errors.<\/p>\n<h1 id=\"sixth\">Failure #6: Forgetting  about the  <code>NULLIF<\/code>  function<\/h1>\n<p>While we&#8217;re on the topic of <b> <code>NULL<\/code><\/b>-related  functions, it&#8217;s worth noting another one that garners less attention, but can  still be useful: the <b> <code>NULLIF<\/code><\/b> function. We don&#8217;t seem to come across  this one as often as the other functions, in part because there does not seem to  be as many use cases for <b> <code>NULLIF<\/code><\/b> as there  are the others. Basically, the function returns a <b> <code>NULL<\/code><\/b> value if two  expressions are equal. If they are not equal, the function returns the first  expression. Here&#8217;s an example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, MakeFlag, FinishedGoodsFlag,\n&#160; NULLIF(MakeFlag, FinishedGoodsFlag) AS NullIfEqual\nFROM Production.Product\nWHERE ProductID IN(534, 679, 680, 707);\n<\/pre>\n<p>All we&#8217;re doing here is passing the two columns into the function and  returning the results, as shown in the following table:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tMakeFlag<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFinishedGoodsFlag<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tNullIfEqual<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTop Tube<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRear Derailleur Cage<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSport-100 Helmet, Red<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We can achieve the same results by using a <b><code>CASE<\/code><\/b>  expression instead of the <b> <code>NULLIF<\/code><\/b> function:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, MakeFlag, FinishedGoodsFlag,\n&#160; CASE\n&#160;&#160;&#160; WHEN MakeFlag = FinishedGoodsFlag THEN NULL\n&#160;&#160;&#160; ELSE MakeFlag\n&#160; END AS NullIfEqual\nFROM Production.Product\nWHERE ProductID IN(534, 679, 680, 707);\n<\/pre>\n<p>As with <b> COALESCE<\/b>, the <b><code>NULLIF<\/code><\/b>  function helps to simplify our statements, but the <b><code>CASE<\/code><\/b>  expression makes the logic clearer to the rest of the world. <\/p>\n<p>Perhaps one of the most useful places to use the <b><code>NULLIF<\/code><\/b>  function is within a mathematical expression in which an error would be  generated if a zero were encountered. Dividing by zero is a good example of  this. Let&#8217;s start with the following <b> <code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, StandardCost, ListPrice,\n&#160; StandardCost \/ (ListPrice - StandardCost) AS NetDiff\nFROM Production.Product\nWHERE ProductID = 680;\n<\/pre>\n<p>The database engine runs the calculation with no problem and returns the  following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStandardCost<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tListPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tNetDiff<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 58<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t1059.31<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t1431.50<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.8461<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now let&#8217;s pull the data from a row in which the <b><code>StandardCost<\/code><\/b>  and <b> <code>ListPrice<\/code><\/b> values are zero:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, StandardCost, ListPrice,\n&#160; StandardCost \/ (ListPrice - StandardCost) AS NetDiff\nFROM Production.Product\nWHERE ProductID = 679;\n<\/pre>\n<p>This time, we get a divide-by-zero error:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 8134, Level 16, State 1, Line 603\nDivide by zero error encountered.\n<\/pre>\n<p>This is where <b> <code>NULLIF<\/code><\/b> comes it.  By returning a <b> <code>NULL<\/code><\/b> in place of a zero, we can at least prevent  the database engine from generating an error:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, StandardCost, ListPrice,\n&#160; StandardCost \/ NULLIF((ListPrice - StandardCost), 0) AS NetDiff\nFROM Production.Product\nWHERE ProductID = 679;\n<\/pre>\n<p>Now we at least get results we can live with.<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tStandardCost<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tListPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tNetDiff<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tRear Derailleur Cage<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t0.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t0.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We could have also used the <b> <code>WHERE<\/code><\/b> clause to  filter our rows that would have given us zero, but then we would not be able to  view any of the data&#8217;s row. The <b> <code>NULLIF<\/code><\/b> function at  least gives us data to work with and makes it easy to do so.<\/p>\n<h1 id=\"seventh\">Failure #7: Assuming that  2 +  <code>NULL<\/code>  = 2<\/h1>\n<p>As great as the <b> <code>NULLIF<\/code><\/b> function is  for handling certain situations, such as avoiding divide-by-zero errors, having <b> <code>NULL<\/code><\/b> show up in our numeric calculations is often less than  optimal. It&#8217;s not that SQL Server does anything surprising, but rather that you  need to know what&#8217;s coming should you run into issues with your calculations.<\/p>\n<p>This all goes back to the idea that <b><code>NULL<\/code><\/b> is a  non-value. It is nothing. It merely indicates that any real data value is  missing. Consequently, when we try to add a value to nothing or subtract from  nothing or multiply by nothing or divide by nothing or do anything by nothing,  we end up with nothing, or <b> <code>NULL<\/code><\/b>. Take a look  at this <b> <code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Weight,\n&#160; (Weight * 4.22) AS FreightCost\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 718;\n<\/pre>\n<p>We&#8217;re simply multiplying the <b> <code>Weight<\/code><\/b> value by <b> <code>4.22<\/code><\/b> and getting the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFreightCost<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.30<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t9.7060<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.12<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t8.9464<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Not surprisingly, whenever we try to multiply by <b><code>NULL<\/code><\/b>, we  get <b> <code>NULL<\/code><\/b>. What choice does the database engine have?  Now let&#8217;s throw a little addition in there to see what happens:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Weight,\n&#160; ((Weight + 3) * 4.22) AS FreightCost\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 718;\n<\/pre>\n<p>As before, when <b> <code>Weight<\/code><\/b> contains a  real value, we get a real value in return, but when <b><code>NULL<\/code><\/b>  enters the equation, we always end up with <b> <code>NULL<\/code><\/b>:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFreightCost<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.30<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t22.3660<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.12<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t21.6064<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>One approach we can take (when circumstances make it reasonable) is to  define a default value that we can return in the event the source data is <b> <code>NULL<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Weight,\n&#160; ISNULL((Weight * 4.22), 4.95) AS FreightCost\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 718;\n<\/pre>\n<p>In this case, we&#8217;re using <b> <code>ISNULL<\/code><\/b> to return a  default value of <b> 4.95<\/b> should our calculation result in a <b> <code>NULL<\/code><\/b> value, which gives us the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFreightCost<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t4.9500<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t4.9500<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.30<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t9.7060<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.12<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t8.9464<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>SQL Server does what it should do when it encounters <b> <code>NULL<\/code><\/b>. It returns <b> <code>NULL<\/code><\/b>. We just need  to be prepared for this eventuality. In some cases, having our expressions  return a <b> <code>NULL<\/code><\/b> value is fine for our circumstances, but at other  times, we&#8217;ll want to adjust our code to accommodate those wayward missing  values. Otherwise a whole lot of customers might end up getting free shipping  because the weight was initially not a consideration.<\/p>\n<h1 id=\"eighth\">Failure #8: Aggregating  data without taking   NULL into account<\/h1>\n<p>As with numeric calculations, we must be prepared to handle aggregations  that could include <b> <code>NULL<\/code><\/b> values.  However, unlike mathematic operations, the database engine eliminates <b> <code>NULL<\/code><\/b> values from the calculations before performing the  actual aggregations, at least at the most basic level. Let&#8217;s start with a simple <b> <code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, Weight\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 720;\n<\/pre>\n<p>The statement returns the following results, which include two rows with  a <b> <code>Weight<\/code><\/b> value of <b><code>NULL<\/code><\/b>:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, L<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLong-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.30<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.12<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.16<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Red, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t2.20<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Suppose we now try to find the average weight for these rows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT AVG(Weight)\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 720;\n<\/pre>\n<p>The database engine will perform the aggregation with no problem by  first eliminating the <b> <code>NULL<\/code><\/b> rows from the  mix and then returning a result of <b> <code>2.195000<\/code><\/b>. However,  the results also include something else, a warning message about the <b> <code>NULL<\/code><\/b> values:<\/p>\n<pre>Warning: Null value is eliminated by an aggregate or other SET operation.\n<\/pre>\n<p>We can verify that the <b> <code>NULL<\/code><\/b> rows are  being eliminated by instead using the <b> <code>COUNT<\/code><\/b> aggregate  function on the <b> Weight<\/b> column:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT COUNT(weight)\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 720;\n<\/pre>\n<p>This time the database engine returns a value of <b><code>4<\/code><\/b>,  indicating that the two <b> <code>NULL<\/code><\/b> rows have  been eliminated. We will again receive the warning message.<\/p>\n<p>All aggregate functions eliminate the <b><code>NULL<\/code><\/b>  values prior to preforming the actual calculations except for the <b> <code>COUNT(*)<\/code><\/b> function:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT COUNT(*)\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 720;\n<\/pre>\n<p>In this case, the database engine includes all rows, no matter where the <b> <code>NULL<\/code><\/b> values lie, and will generate no warning  messages.<\/p>\n<p>The fact that SQL Server handles aggregations this way is not a bad  thing. But it&#8217;s something you need to understand. If you&#8217;re not familiar with  the data, your queries might be eliminating rows without understanding the full  implications. You can consider using a function such as <b><code>ISNULL<\/code><\/b> to  replace the <b> <code>NULL<\/code><\/b> values, but you still risk skewing your  results. As with many situations, handling <b> <code>NULL<\/code><\/b> values can  take a special touch. For example, detailed analytics will require, at the very  least, knowledge of the number of <b> <code>NULL<\/code><\/b> values in a  particular data set.<\/p>\n<p>As a side note, those who don&#8217;t appreciate having their queries return  warning messages like those we&#8217;ve seen here, can use the <b><code>SET<\/code><\/b> <b> <code>ANSI_WARNINGS<\/code><\/b> statement to turn warnings off when executing  an aggregation:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET ANSI_WARNINGS OFF;\nSELECT AVG(Weight)\nFROM Production.Product\nWHERE ProductID BETWEEN 715 AND 720;\nSET ANSI_WARNINGS ON;\n<\/pre>\n<p>Although this will give us the same results without the message, turning  off this option can affect other operations. For example, modifying data on  indexed views or on tables with indexed computed columns will fail if the option  is turned off. So tread carefully if you consider this route.<\/p>\n<p>Also be aware that when you start grouping and aggregating data, you  might not get exactly the results you expect when that data includes <b> <code>NULL<\/code><\/b> values. For example, the following <b> <code>SELECT<\/code><\/b> statement groups by the <b><code>ProductSubcategoryID<\/code><\/b>  column in our trusty <b> <code>Product<\/code><\/b> table:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductSubcategoryID AS ProdSubID, \n&#160; AVG(Weight) AS AvgWeight,\n&#160; COUNT(*) AS FullCount, \n&#160; COUNT(Weight) AS ColCount\nFROM Production.Product\nGROUP BY ProductSubcategoryID;\n<\/pre>\n<p>The following table shows the first 10 rows in our result set:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProdSubID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tAvgWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tFullCount<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColCount<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t434.285714<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t209<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">7<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t25.058750<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t32<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t32<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">2<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t17.448139<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t43<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t43<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">3<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t27.680909<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t22<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t22<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">4<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">8<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">5<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t187.000000<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">3<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">3<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">6<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t317.000000<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">2<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">2<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">7<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">1<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">0<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">8<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t603.333333<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">3<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">3<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\">9<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t151.500000<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">2<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The full result set represents all 504 rows in the <b><code>Product<\/code><\/b>  table, including rows whose <b> <code>ProductSubcategoryID<\/code><\/b>  and <b> <code>Weight<\/code><\/b> columns are <b><code>NULL<\/code><\/b>. If  we were to dig into the averages and the individual column counts for each  group, we would discover that the <b> <code>NULL<\/code><\/b> values are  not included, but the result set as a whole includes all the data. Again, I  point this out not to suggest that there is a problem with the way SQL Server  groups and aggregates data, but only to demonstrate that we need to be aware of  what&#8217;s happening with our queries when <b> <code>NULL<\/code><\/b> values are  involved. In this case, by including both types of counts in each group, we&#8217;re  providing a handy way to provide insight into our source data.<\/p>\n<h1 id=\"ninth\">Failure #9: Forgetting  about  <code>NULL<\/code>  in your Boolean expressions<\/h1>\n<p>Boolean expressions that involve <b> <code>NULL<\/code><\/b> values can be  especially tricky because they introduce what is called three-valued logic.  Typically, when an expression is evaluated, it returns either <b><code>TRUE<\/code><\/b> or <b> <code>FALSE<\/code><\/b>. However, when <b> <code>NULL<\/code><\/b> enters the  mix, we get a third possibility: <b> <code>UNKNOWN<\/code><\/b>. As we&#8217;ve  already covered, SQL Server marks a value as <b><code>NULL<\/code><\/b> if it  does not exist. As a result, a Boolean expression that tries to evaluate a  condition based on a missing value results in <b><code>UNKNOWN<\/code><\/b>.  <\/p>\n<p>The main thing to keep in mind with Boolean expressions is that your  result set will include only those rows in which the expression evaluates to <b> <code>TRUE<\/code><\/b>. If the expression evaluates to <b><code>FALSE<\/code><\/b> or <b> <code>UNKNOWN<\/code><\/b>, the row is omitted. <\/p>\n<p>The specifics of three-valued logic can seem unwieldy when taking  account the various combinations that can result from using the <b> <code>AND<\/code><\/b>, <b> <code>OR<\/code><\/b>, and <b> <code>NOT<\/code><\/b> operators, but you can find a handy reference to help  sort all that out be referring to the SQL Server help topic &#8220;<a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms191504(v=sql.105).aspx\">Null  Values<\/a>.&#8221; In the meantime, lets look at a few examples that demonstrate what  happens when <b> NULL<\/b> values enter the Boolean mix,  starting with the following temporary table:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductID, Name AS ProductName, Weight, Class\nINTO #products\nFROM Production.Product\nWHERE ProductID BETWEEN 945 AND 954; \nSELECT * FROM #products;\n<\/pre>\n<p>The following results show the data that&#8217;s now stored in the <b> <code>#products<\/code><\/b> table:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t945<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Derailleur<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t88.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t946<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t947<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t948<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Brakes<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t317.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t600.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t950<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t635.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t951<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t575.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t952<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tChain<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t953<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-2000 Blue, 60<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t27.90<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t954<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-1000 Yellow, 46<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t25.13<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now let&#8217;s use the <b> <code>AND<\/code><\/b> operator to  create a simple Boolean expression in our <b> <code>WHERE<\/code><\/b> clause:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM #products\nWHERE Weight &gt; 500 AND Class = 'H'; \n<\/pre>\n<p>As expected, the statement returns the results shown in the following  table:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t951<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t575.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Notice that the query engine excludes the row for product ID 947. Even  though the <b> <code>Class<\/code><\/b> value is <b><code>H<\/code><\/b>, the <b> <code>Weight<\/code><\/b> value is <b> <code>NULL<\/code><\/b>, so the  expression evaluates to <b> UNKNOWN<\/b>. And of course, row 952 is  excluded because both the <b> <code>Weight<\/code><\/b> and <b> <code>Class<\/code><\/b> values are <b> <code>NULL<\/code><\/b>.<\/p>\n<p>Now let&#8217;s rerun the statement, but this time use the <b> <code>OR<\/code><\/b> operator for our Boolean expression:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM #products\nWHERE Weight &gt; 500 OR Class = 'H'; \n<\/pre>\n<p>As expected, our results look quite different:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t947<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t600.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t950<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t635.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t951<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t575.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t954<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-1000 Yellow, 46<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t25.13<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">H   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>In this case, only one of the two Boolean conditions needs to evaluate  to <b> <code>TRUE<\/code><\/b>, resulting in many more returned rows. Even  row 947 is included, even through the <b> <code>Weight<\/code><\/b> value is <b> <code>NULL<\/code><\/b>. Because the <b> <code>OR<\/code><\/b> operator is  used and the second condition evaluates to <b> <code>TRUE<\/code><\/b>, that row is  included.<\/p>\n<p>This time around, let&#8217;s use the <b> <code>AND<\/code><\/b> <b> <code>NOT<\/code><\/b> operator in our statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM #products\nWHERE Weight &gt; 500 AND NOT Class = 'H'; \n<\/pre>\n<p>Once again, we get very different results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tWeight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tClass<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t600.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">L   <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t950<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t635.00<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">M   <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Not surprising, the only rows to be included are those in which both  Boolean conditions evaluate to <b> <code>TRUE<\/code><\/b>. All the <b> <code>FALSE<\/code><\/b> and <b> <code>UNKNOWN<\/code><\/b> values are  left behind. <\/p>\n<p>As with aggregate functions, SQL Server&#8217;s treatment of Boolean  expressions in itself is not a bad thing. You just need to be aware of what can  happen with your data when <b> <code>NULL<\/code><\/b> values are  involved. Data can be eliminated that you might expect to be included, leading  to unintended consequences in subsequent analytics and decision-making.<\/p>\n<h1 id=\"tenth\">Failure #10: Not  accounting for   NULL in your string expressions<\/h1>\n<p>Up to this point, we&#8217;ve touched little upon string values and what  happens when <b> NULL<\/b> enters the mix, but at least some  coverage in this area is warranted, beyond what we saw when working with Boolean  expressions.<\/p>\n<p>Suppose we start with the following <b><code>SELECT<\/code><\/b>  statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductID, Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845;\n<\/pre>\n<p>The statement returns the results shown here:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t835<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t836<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t837<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t838<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t839<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t840<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t841<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMen&#8217;s Sports Shorts, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t842<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t843<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tCable Lock<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t844<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMinipump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t845<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Pump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now suppose when want to filter our data set even further by returning  all rows with a <b> Color<\/b> value other than <b> <code>black<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductID, Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845\n&#160; AND Color &lt;&gt; 'black';\n<\/pre>\n<p>Now our <b> SELECT<\/b> statement returns the following  results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t835<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t836<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t842<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We might have expected the results to include those rows whose <b> <code>Color<\/code><\/b> value is <b> <code>NULL<\/code><\/b>. However,  we&#8217;re once again faced with the issue of trying to evaluate an expression  against what is essentially a missing value. A &#8220;not black&#8221; value cannot be  equated with a &#8220;missing&#8221; value; consequently, those rows are omitted.<\/p>\n<p>Concatenating string values follows similar logic:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, ProductNumber, Color,\n&#160; (ProductNumber + '_' + Color) AS NewProductNumber\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845;\n<\/pre>\n<p>Whenever <b> NULL<\/b> shows up, we end up with nothing (<b><code>NULL<\/code><\/b>) in our <b> <code>NewProductNumber<\/code><\/b>  column:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductNumber<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tNewProductNumber<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44_Yellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48_Yellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-62_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-44_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-48_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-52_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMen&#8217;s Sports Shorts, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSH-M897-S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSH-M897-S_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPA-T100<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPA-T100_Grey<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tCable Lock<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLO-C100<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMinipump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-0452<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Pump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-M044<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Once again, the bottom line is that you must be prepared to handle <b> <code>NULL<\/code><\/b> values if they could in any way impact your results.  For example, you might try throwing an <b> <code>ISNULL<\/code><\/b> into the  mix:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Name AS ProductName, ProductNumber, Color,\n&#160; (ProductNumber + '_' + ISNULL(Color, 'neutral')) AS NewProductNumber\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845;\n<\/pre>\n<p>Now the statement returns the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductNumber<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tNewProductNumber<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44_Yellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48_Yellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-62_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-44_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-48_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-52_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMen&#8217;s Sports Shorts, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSH-M897-S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tSH-M897-S_Black<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPA-T100<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPA-T100_Grey<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tCable Lock<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLO-C100<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLO-C100_neutral<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMinipump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-0452<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-0452_neutral<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Pump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-M044<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tPU-M044_neutral<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>At least this way we have something to work with other than <b> <code>NULL<\/code><\/b>.<\/p>\n<h1 id=\"eleventh\">Failure #11: Joining  tables on nullable columns<\/h1>\n<p>Another area where we might get results other than what we expect is  when we join data based on nullable columns. Because <b><code>NULL<\/code><\/b> value  are considered non-values and because the database engine cannot match  non-values, <b> <code>NULL<\/code><\/b> values will show up in the join column only  for outer joins. To demonstrate, let&#8217;s start by creating and populating a couple  tables:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE Products\n(ProductID INT, ProductName NVARCHAR(50), CategoryAltID INT);\nINSERT INTO Products VALUES\n(945, 'Front Derailleur', 10),\n(946, 'LL Touring Handlebars', 20),\n(947, 'HL Touring Handlebars', NULL),\n(948, 'Front Brakes', 20),\n(949, 'LL Crankset', 10),\n(950, 'ML Crankset', NULL);\n\nCREATE TABLE Categories\n(CategoryNumber NVARCHAR(25), CategoryAltID INT);\nINSERT INTO Categories VALUES\n('FR-R72Y-44', 10),\n('FR-R72Y-48', 20),\n('FR-R92B-62', NULL),\n('FR-R92B-52', NULL);\n<\/pre>\n<p>Notice that the <b> <code>CategoryAltID<\/code><\/b>  column in both tables contains <b> <code>NULL<\/code><\/b> values. Now  let&#8217;s create an inner join between those tables, based on that column:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT p.ProductID, p.ProductName, p.CategoryAltID AS pCatAltID,\n&#160; c.CategoryAltID AS cCatAltID, c.CategoryNumber\nFROM Products p JOIN Categories c\n&#160; ON p.CategoryAltID = c.CategoryAltID;\n<\/pre>\n<p>Although the <b> <code>CategoryAltID<\/code><\/b>  column contains <b> <code>NULL<\/code><\/b> values in both tables, they are not  considered equal (for all the reasons we&#8217;ve discussed), which means they&#8217;re not  included in the result set:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tpCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tcCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tCategoryNumber<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t945<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Derailleur<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t946<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t948<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Brakes<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Now let&#8217;s revise the statement to include a left outer join:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT p.ProductID, p.ProductName, p.CategoryAltID AS pCatAltID,\n&#160; c.CategoryAltID AS cCatAltID, c.CategoryNumber\nFROM Products p LEFT OUTER JOIN Categories c\n&#160; ON p.CategoryAltID = c.CategoryAltID;\n<\/pre>\n<p>This time our results include all rows from the <b><code>Product<\/code><\/b>  table, including those whose <b> <code>CategoryAltID<\/code><\/b>  column is <b> <code>NULL<\/code><\/b> in both tables:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tpCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tcCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tCategoryNumber<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t945<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Derailleur<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t946<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t947<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t948<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Brakes<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t950<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The query engine includes <b> <code>NULL<\/code><\/b> rows not  because they&#8217;re considered matches, but rather because that&#8217;s the nature of an  outer join and the way it returns all rows from the &#8220;outer&#8221; table. We can also  try our statement with a full outer join:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT p.ProductID, p.ProductName, p.CategoryAltID AS pCatAltID,\n&#160; c.CategoryAltID AS cCatAltID, c.CategoryNumber\nFROM Products p FULL OUTER JOIN Categories c\n&#160; ON p.CategoryAltID = c.CategoryAltID;\n<\/pre>\n<p>Now our results include everything, regardless of the <b> <code>NULL<\/code><\/b> values:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tpCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tcCatAltID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tCategoryNumber<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t945<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Derailleur<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t946<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t947<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Touring Handlebars<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t948<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFront Brakes<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t20<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-48<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t949<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tLL Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t10<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R72Y-44<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t950<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Crankset<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-62<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tFR-R92B-52<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>If you consider the logic behind the query engine&#8217;s approach to <b> <code>NULL<\/code><\/b> values, it&#8217;s not surprising we get the results we do.  Like any place where <b> NULL<\/b> shows up, it comes down to  understanding the data we could potentially be dealing with and what that means  to the bigger picture. Whenever <b> <code>NULL<\/code><\/b> enters into  the equation, we must take it into consideration so we don&#8217;t end up with query  results very different from what we expect. When we join data, a <b> <code>NULL<\/code><\/b> in one column can cause us to miss important data in  other columns, all because we did not think through the possible outcomes.<\/p>\n<h1 id=\"twelveth\">Failure #12: Creating a  unique index without taking  NULL into account<\/h1>\n<p>Up to this point, I&#8217;ve tried to emphasize how SQL Server uses <b> <code>NULL<\/code><\/b> only to indicate that a value is missing. We cannot  compare or add or multiply <b> <code>NULL<\/code><\/b> values  because we cannot compare or add or multiply missing values. When it comes to  unique indexes, however, SQL Server seems to have a double standard, treating <b> <code>NULL<\/code><\/b> values as equal values and consequently permitting only  one <b> <code>NULL<\/code><\/b> value in a unique index.<\/p>\n<p>To demonstrate, let&#8217;s create and populate a simple table:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE Products\n(ProductID INT, ProductName NVARCHAR(50), ProductNumber NVARCHAR(25));\nINSERT INTO Products VALUES\n(945, 'Front Derailleur', 'FD-2342'),\n(946, 'LL Touring Handlebars', 'HB-T721'),\n(947, 'HL Touring Handlebars', 'HB-T928'),\n(948, 'Front Brakes', 'FB-9873'),\n(949, 'LL Crankset', NULL),\n(950, 'ML Crankset', NULL);\n<\/pre>\n<p>Now let&#8217;s try to create a unique index on the <b><code>ProductNumber<\/code><\/b>  column:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE UNIQUE INDEX ux_products_productnumber\nON Products(ProductNumber);\n<\/pre>\n<p>Because the column contains multiple <b><code>NULL<\/code><\/b>  values, the database engine returns the following error:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 1505, Level 16, State 1, Line 982\nThe CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Products' and the index name 'ux_products_productnumber'. The duplicate key value is (&lt;NULL&gt;).\nThe statement has been terminated.\n<\/pre>\n<p>This might seem contradictory behavior on the part of SQL Server, given  the non-value status it imposes on <b> <code>NULL<\/code><\/b> everywhere  else. After all, how can missing values be considered duplicates if there is no  way to compare them? <\/p>\n<p>Because of this limitation, we have to be particularly careful when we  create our tables and indexes prior to populating them. We could be faced with  similar error messages if we try to insert a row with a <i>duplicate<\/i> <b> <code>NULL<\/code><\/b> value.<\/p>\n<p>Fortunately, since SQL Server 2008, we&#8217;ve been able to create filtered  indexes that let us sidestep the duplicate <b> <code>NULL<\/code><\/b> issue:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE UNIQUE INDEX ux_products_productnumber\nON Products(ProductNumber)\nWHERE ProductNumber IS NOT NULL;\n<\/pre>\n<p>The index is now created without a hitch. We can even insert additional  duplicate <b> <code>NULL<\/code><\/b> values:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT INTO Products VALUES\n(951, 'HL Crankset', NULL);\n<\/pre>\n<p>Creating a filtered index is easy enough to do and is a handy way to  sidestep the limitations of a unique index. Just be sure to refer to the SQL  Server help topic &#8220;<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/cc280372.aspx\">Create  Filtered Indexes<\/a>&#8221; to make certain you&#8217;re familiar with the limitations and  restrictions on these types of indexes.<\/p>\n<h1 id=\"thirteenth\">Failure #13: Sorting data  on a nullable column<\/h1>\n<p>A result set that is not in the order you want might not seem such a big  deal, but it can still be annoying when you sort your results and end up with a  bunch of <b> NULL<\/b> values at the beginning. For  example, the following <b> <code>SELECT<\/code><\/b> statement  sorts the data by the <b> Color<\/b> column:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductID, Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845\nORDER BY Color;\n<\/pre>\n<p>The statement gives us the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t843<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tCable Lock<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t844<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMinipump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t845<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Pump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t837<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t838<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t839<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t840<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t841<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMen&#8217;s Sports Shorts, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t842<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t835<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t836<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We can instead sort the data in descending order, but that would put the  colors themselves in reverse order. Another work-around is to slip a <b> <code>CASE<\/code><\/b> expression into our <b><code>WHERE<\/code><\/b>  clause to trick the database engine:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT ProductID, Name AS ProductName, Color\nFROM Production.Product\nWHERE ProductID BETWEEN 835 AND 845\nORDER BY \n&#160; CASE WHEN Color IS NULL THEN 1 ELSE 0 END, \n&#160; Color;\n<\/pre>\n<p>The extra expression assigns <b> <code>1<\/code><\/b> to the <b> <code>NULL<\/code><\/b> rows and <b> <code>0<\/code><\/b> to the rest so  they&#8217;re sorted first. From there, the normal sorting occurs, as shown in the  following results: <\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tProductName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> \t\tColor<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t837<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 62<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t838<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t839<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t840<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tHL Road Frame &#8211; Black, 52<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t841<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMen&#8217;s Sports Shorts, S<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tBlack<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t842<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tTouring-Panniers, Large<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tGrey<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t835<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 44<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t836<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tML Road Frame-W &#8211; Yellow, 48<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tYellow<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t843<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tCable Lock<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t844<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMinipump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\t845<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tMountain Pump<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"> \t\tNULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>This might not be an earth-shattering solution, but it&#8217;s an easy enough  way to move those <b> <code>NULL<\/code><\/b> values to the  bottom, if you happen to prefer them there.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>NULLs in SQL are a mixed blessing. The Three-Valued Logic of SQL has its uses but can cause difficulties to anyone who assumes that NULL has a &#8216;value&#8217;. It can make reports go horribly wrong in a number of alarming ways, as Robert Sheldon explains.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5994,4150,4151,4252],"coauthors":[],"class_list":["post-2049","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-horribly","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2049","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2049"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2049\/revisions"}],"predecessor-version":[{"id":41197,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2049\/revisions\/41197"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2049"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2049"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2049"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2049"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}