ANSI NULLS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cary England » ANSI NULLS

11 posts, Page 1 of 2

1 2 »»

ANSI NULLS

Rate Topic

Display Mode

Topic Options

Author

Message

cengland0

Posted Saturday, October 09, 2010 5:13 PM

Ten 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

L' Eomot Inversé

Posted Sunday, October 10, 2010 4:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 7,087, Visits: 7,140

Thanks for the question

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1001800

Nakul Vachhrajani

Posted Monday, October 11, 2010 2:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:52 AM
Points: 1,277, Visits: 1,608

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: @nakulv_sql
Google Plus: +Nakul

Post #1001972

Hardy21

Posted Monday, October 11, 2010 2:15 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877, Visits: 1,158

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

Hugo Kornelis

Posted Monday, October 11, 2010 3:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 5,235, Visits: 7,037

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

Hugo Kornelis

Posted Monday, October 11, 2010 3:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 5,235, Visits: 7,037

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

Carlo Romagnano

Posted Monday, October 11, 2010 3:43 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 1,969, Visits: 1,820

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

Same behavior for previous versions of sqlserver.

Post #1002013

Daniel Bowlin

Posted Monday, October 11, 2010 5:49 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 2,672, Visits: 2,417

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

Post #1002087

UMG Developer

Posted Monday, October 11, 2010 11:40 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

Thanks for the question.

Post #1002371

SQLRNNR

Posted Monday, October 18, 2010 10:19 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the question

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

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions