27 Jan

ANSI_NULLS setting

Back in my Cyberquery days we used to search for NULL values with colname = NULL. I always thought it was strange but that’s how it worked. Well, ou can do the same thing with SQL Server by using the ANSI_NULLS setting.

SET ANSI_NULLS ON; — This is the default setting.
with cte as
(
select null as col1
union
select 1
)
select * from cte where col1 = NULL;

Returns no rows.

However, the same query with ANSI_NULLS OFF returns a different result.

SET ANSI_NULLS OFF; — Turns ANSI_NULLS off so the = operator will work.
with cte as
(
select null as col1
union
select 1
)
select * from cte where col1 = NULL;

Returns

col1
NULL

You’ll probably never run into it but if you see “= NULL”, and it works, this is what’s going on.

Note: At some point in the future this will be turned off so that this setting is always on and trying to turn it off will result in an error.

MSDN: http://msdn.microsoft.com/en-us/library/ms188048.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *