Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ANSI NULLS Expand / Collapse
Author
Message
Posted Saturday, October 9, 2010 5:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Comments posted to this topic are about the item ANSI NULLS
Post #1001761
Posted Sunday, October 10, 2010 4:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
Thanks for the question

Tom
Post #1001800
Posted Monday, October 11, 2010 2:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:55 AM
Points: 1,414, Visits: 1,824
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1001972
Posted Monday, October 11, 2010 2:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,108, Visits: 1,371
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
Post #1001975
Posted Monday, October 11, 2010 3:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1002001
Posted Monday, October 11, 2010 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1002003
Posted Monday, October 11, 2010 3:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 2,509, Visits: 2,386
Using SQL Server 2005, what is the output of the SELECT statement below?

Same behavior for previous versions of sqlserver.
Post #1002013
Posted Monday, October 11, 2010 5:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
Good question, thanks. Nice additional explanation in the forum.
Post #1002087
Posted Monday, October 11, 2010 11:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question.
Post #1002371
Posted Monday, October 18, 2010 10:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Thanks for the question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1006351
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse