Ansi Options Part 2 - ANSI_NULLS

ANSI Options Part 1 - ANSI_NULLS

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:

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.

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.