SET ANSI_NULLS ON|OFF In SQL Server

When i started learing SQL Server one of the mistakes I did a few times was something like the following.

   1: IF OBJECT_ID('Invoice') IS NOT NULL DROP TABLE Customers
   2: GO
   3: CREATE TABLE Invoice(
   4:     InvoiceId int, 
   5:     InvoiceNo VARCHAR(10),
   6:     InvoiceStatus)
   7: GO
   8:  
   9: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 1,'Jacob','Active'
  10: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 2,'Mike','Inactive'
  11: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 3,'Steve', NULL
  12:  
  13: SELECT *
  14: FROM Invoice
  15: WHERE InvoiceStatus = NULL

Well, my queries never returned a row, even though there is a row in the table with a NULL value in InvoiceStatus column. Then I tried the opposite.

   1: SELECT *
   2: FROM Invoice
   3: WHERE InvoiceStatus <> NULL

This did not return anything as well.

I realized that the result of a comparison operation that involves a NULL value is UNKNOWN. That is the reason why the above queries did not return any row. The correct way to write the queries is:

   1: SELECT InvoiceNo,InvoiceStatus
   2: FROM Invoice
   3: WHERE InvoiceStatus IS NULL
   4: /*
   5: InvoiceNo            InvoiceStatus
   6: -------------------- --------------
   7: Steve                NULL
   8: */
   9: SELECT InvoiceNo,InvoiceStatus
  10: FROM Invoice
  11: WHERE InvoiceStatus IS NOT NULL
  12: /*
  13: InvoiceNo            InvoiceStatus
  14: -------------------- --------------
  15: Jacob                Active        
  16: Mike                 Inactive      
  17: */

So where does SET ANSI_NULLS fit in this story?

Well,the behavior of comparison operations (= and <>) when they are used with NULL values depends on the setting of this option. The default value of SET ANSI_NULLS is ON. This setting causes the behavior explained in the previous examples.

The two examples we saw at the beginning of this post will work correctly if we set ANSI_NULLS to OFF. The following example demonstrates that.

   1: SET ANSI_NULLS OFF
   2: SELECT InvoiceNo,InvoiceStatus FROM Invoice WHERE InvoiceStatus = NULL
   3:  
   4: /*
   5:  
   6: InvoiceNo  InvoiceStatus
   7: ---------- -------------
   8: Steve      NULL
   9:  
  10: */
  11:  
  12: SELECT InvoiceNo,InvoiceStatus FROM Invoice WHERE InvoiceStatus <> NULL
  13:  
  14: /*
  15:  
  16: InvoiceNo  InvoiceStatus
  17: ---------- -------------
  18: Jacob      Active
  19: Mike       Inactive
  20:  
  21: */
Advertisements

6 thoughts on “SET ANSI_NULLS ON|OFF In SQL Server

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s