ANSI Options Part 1 - ANSI_NULLS

This article continues my series on ANSI settings in SQL Server. The other parts are:

Introduction

SQL Server comforms to a number of ANSI standards to varying degrees. The level of compliance or even whether a particular object behaves as per an ANSI Standard is usually goverened by an option setting. In this series I plan to look at the various options that are available in SQL Server. This article looks at ANSI_NULLS.

What are ANSI_NULLS

The NULLability of columns is an oft debated topic; whether to use them or not is something that will probably always be argued. How the server treats NULLs, however, is something that is not argued. But it is something that you should be aware of.

The primary effect of this setting is to control how the server handles the comparison operations of equals (=) and not equals (<>) when dealing with null values. SQL Server may either conform to the ANSI SQL-92 standard (ON) or not (OFF).

The SQL-92 standard specifies that any equals or not equals comparison against a NULL value should return a false, or no rows. This would mean that even if a table contains a NULL value, no rows are returned. An example may help clarify this:

Suppose I have this table and run the select statement below it:

create table MyTable(MyID int, MyChar char(1))
go
insert MyTable select 1, 'A'
insert MyTable select 2, 'B'
insert MyTable select 3, NULL
insert MyTable select 4, NULL
select
*
 from MyTable
MyID      MyChar
--------- -------------
1         A
2         B
3         NULL
4         NULL

select
*
 from MyTable a
 where a.MyChar = NULL

With ANSI_NULLS set to ON, this should return 0 rows. And if you run a "SET ANSI_NULLS ON" before running the select statement, you will find that no rows are returned.

SQL Server, however, gives you the option to change this behavior. If you set this option to OFF, then if there are NULL values, they will be returned.

SET ANSI_NULLS OFF
select 
*
 from MyTable
 where MyChar = NULL
MyID      MyChar
--------- -------------
3         NULL
4         NULL

What to do?

The default setting is ANSI_NULLS depends on how you connect to SQL Server. The SQL Server ODBC driver and OLEDB providers set this option to ON. Books online recommends that connections set this option to OFF, but this is for older applications and behaviors. For the most part, unless you have a compelling reason not to, assume and set this option to ON. Most of your clients will depend on the defaults.

There are a few places where you must set this or consider it. For stored procedures, the setting at the time of compilation, not execution, is the one that applies. Again, compile your stored procedures with this set to ON. If you work with indexes on computed columns or views, then this must be set to ON. For distributed queries, this must be set to ON.

References:

Books Online - Search "Set ANSI_NULLS"
Online BOL

Conclusions

Nothing earth shattering in this article. Indeed, most of this information can be found in Books Online. However, it is an area that relates to questions I see posted in various forums. Understanding the workings of SQL Server is essential to developing stable and robust applications.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also

rate this article.

Steve Jones

Return to Steve Jones Home

Ansi Options Part 2 - ANSI_NULLS