Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ansi Options Part 2 - ANSI_NULLS

By Steve Jones,

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
©dkRanch.net July 2002
Return to Steve Jones Home

Total article views: 4630 | Views in the last 30 days: 1
 
Related Articles
FORUM

Select statement of view stays 5 hours to return results......

Select statement of view stays 5 hours to return results......

FORUM

Using "Sql server" option for Configuration type

Using "Sql server" option for Configuration type

FORUM

Regionaland Language Option settings for SQL Server

Regionaland Language Option settings for SQL Server

BLOG

Common SQL Server Mistakes – Multi Row DML Triggers

How often have you seen someone write a DML trigger like this: createtrigger mytrigger on Mytable f...

FORUM

Creating XML from SQL Server 2005 - What are my options?

Creating XML from SQL Server 2005 - What are my options?

 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones