SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI NULLS


ANSI NULLS

Author
Message
cengland0
cengland0
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2250 Visits: 1300
Comments posted to this topic are about the item ANSI NULLS
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26197 Visits: 12506
Thanks for the question

Tom

Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3321 Visits: 2149
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Hardy21
Hardy21
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 1399
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18969 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18969 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7570 Visits: 3400
Using SQL Server 2005, what is the output of the SELECT statement below?

Same behavior for previous versions of sqlserver.

I run on tuttopodismo
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8102 Visits: 2629
Good question, thanks. Nice additional explanation in the forum.
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4088 Visits: 2204
Thanks for the question.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67589 Visits: 18570
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search