ANSI NULLS

Question

Post reply

ANSI NULLS

cengland0

SSCertifiable

Points: 6102
More actions
October 9, 2010 at 5:13 pm

#230221

Comments posted to this topic are about the item ANSI NULLS

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

TomThomson

SSC Guru

Points: 104773

October 10, 2010 at 4:30 am

#1234683

Thanks for the question

Tom

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 2

Indeed. Thanks for the question.

Word of caution for everyone:

Per the MSDN article referred in the answer (http://msdn.microsoft.com/en-us/library/ms188048.aspx), ANSI_NULLS will always be ON in a future version of SQL Server. If developing any new application, please take this important consideration as part of your design. For pre-existing applications, please work towards re-engineering them if they are using ANSI_NULLS OFF.

Edited to add the MSDN article URL.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Nice question.

I think the reason for 'ANSI_NULLS will always be ON' for future version is to make the application behavior consistent.

Any other reason?

Thanks

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Great question. Thanks, cengland.

In addition to the explanation: "Thorough testing shows this also applies to the IN statement", there is also a logical explanation.

The ANSI standard defines the IN operator as a series of OR'ed equation tests. In other word, the ANSI standard says that "x IN (a, b, c)" equates to "x = a OR x = b OR x = c". Or in the case of this question, "WHERE Column1 IN (1,NULL)" equates to "WHERE Column1 = 1 OR Column1 = NULL". Under ANSI null setting, any comparison to NULL always results in the truth value Unknown. So for the five rows in the sample table, here are the evaluation results:

Column1 | Column1 = 1 | Column1 = NULL | Column1 = 1 OR Column1 = NULL

--------+-------------+----------------+------------------------------

1 | True | Unknown | True

2 | False | Unknown | Unknown

3 | False | Unknown | Unknown

4 | False | Unknown | Unknown

Null | Unknown | Unknown | Unknown

Only rows where the condition evaluates to True will be returned, so that is only 1 row.

With ANSI NULLS OFF, the result of a NULL = NULL test changes to True, so the last line now changes to all True results. (I don't know if the result of a (not NULL) = NULL test changes to False under non-ANSI settings. I've never used them and since they are deprecated, I don't really care.)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Hardy21 (10/11/2010)
Nice question.
I think the reason for 'ANSI_NULLS will always be ON' for future version is to make the application behavior consistent.
Any other reason?

Standardisation.

ANSI is a standard among relational database implementations. Though it does help in the competition to offer extra features in addition to the standard, it does not help to have features that go against the standard. Complete portability will always be an illusion, but the less code changes are required to port from one DBMS to another, the more pleased some customers are.

I think the only reason SET ANSI_NULLS OFF was introduced was to preserve backward compatibility with behaviour of very old implementations that predate the official standard. However, this is just speculation.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 6

Using SQL Server 2005, what is the output of the SELECT statement below?

Same behavior for previous versions of sqlserver.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

Good question, thanks. Nice additional explanation in the forum.

UMG Developer SSChampion Points: 13482 More actions · Answer 8

UMG Developer

SSChampion

Points: 13482

October 11, 2010 at 11:40 am

#1235160

Thanks for the question.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 10

Thanks for your explanation

Hugo Kornelis (10/11/2010)
Great question. Thanks, cengland.
In addition to the explanation: "Thorough testing shows this also applies to the IN statement", there is also a logical explanation.
The ANSI standard defines the IN operator as a series of OR'ed equation tests. In other word, the ANSI standard says that "x IN (a, b, c)" equates to "x = a OR x = b OR x = c". Or in the case of this question, "WHERE Column1 IN (1,NULL)" equates to "WHERE Column1 = 1 OR Column1 = NULL". Under ANSI null setting, any comparison to NULL always results in the truth value Unknown. So for the five rows in the sample table, here are the evaluation results:
Column1 | Column1 = 1 | Column1 = NULL | Column1 = 1 OR Column1 = NULL
--------+-------------+----------------+------------------------------
1 | True | Unknown | True
2 | False | Unknown | Unknown
3 | False | Unknown | Unknown
4 | False | Unknown | Unknown
Null | Unknown | Unknown | Unknown
Only rows where the condition evaluates to True will be returned, so that is only 1 row.
With ANSI NULLS OFF, the result of a NULL = NULL test changes to True, so the last line now changes to all True results. (I don't know if the result of a (not NULL) = NULL test changes to False under non-ANSI settings. I've never used them and since they are deprecated, I don't really care.)