ANSI_NULLS

Question

ANSI_NULLS

Paul White

SSC Guru

Points: 150467
More actions
April 6, 2010 at 9:09 pm

#220994

Comments posted to this topic are about the item ANSI_NULLS
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Viewing 15 posts - 1 through 15 (of 81 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice question Paul.

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

CirquedeSQLeil (4/6/2010)
Nice question Paul.

1. Did you get it right?

2. Are you scared by the behaviour ANSI_NULLS OFF?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Paul White NZ (4/6/2010)
CirquedeSQLeil (4/6/2010)
Nice question Paul.
1. Did you get it right?
2. Are you scared by the behaviour ANSI_NULLS OFF?

Yes I got it correct.

I thought you explained things nicely.

And yes, by having Ansi_Nulls Off, I can see there being complications. I will have to verify this setting on my servers.

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

Paul White SSC Guru Points: 150467 More actions · Answer 4

CirquedeSQLeil (4/6/2010)
And yes, by having Ansi_Nulls Off, I can see there being complications. I will have to verify this setting on my servers.

SELECT object_type = OBJ.type_desc,

[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),

[object_name] = OBJ.name,

SM.[definition]

FROM sys.objects OBJ

JOIN sys.sql_modules SM

ON SM.[object_id] = OBJ.[object_id]

WHERE OBJ.type_desc IN

(

N'SQL_STORED_PROCEDURE',

N'SQL_SCALAR_FUNCTION',

N'SQL_INLINE_TABLE_VALUED_FUNCTION',

N'SQL_TABLE_VALUED_FUNCTION',

N'SQL_TRIGGER',

N'VIEW'

)

AND SM.uses_ansi_nulls = 0

AND OBJ.is_ms_shipped = 0

ORDER BY

object_type,

[schema_name],

[object_name];

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95409 More actions · Answer 5

I agree... Good question. Made me sit back and think about this a bit.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ziangij SSCertifiable Points: 7983 More actions · Answer 6

Thanks for the question, Paul. Luckily, I have set ANSI_NULLS ON on my servers..:-)

Paul White SSC Guru Points: 150467 More actions · Answer 7

ziangij (4/6/2010)
Thanks for the question, Paul. Luckily, I have set ANSI_NULLS ON on my servers..:-)

Good stuff.

If you would like to check that no-one else has manually created a procedure, function, trigger, or view and overridden your default settings at the session level, run the script I posted before - just to check 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 8

Nice question.

Maybe I read it too fast: "Select all...", I selected "ABCD", then pressed the "Submit" button. Oops, there was "...that apply" part 🙂

It's interesting to explore the execution plans.

The condition "= NULL" is replaced with the condition "IS NULL". Execution plans "A" and "C" both have the node "Table Scan(OBJECT: (@T), WHERE: ([A] IS NULL))".

Execution plan "B" has the only node "Constant Scan", which means SQL Server understands (at compile time!) that there will be no output rows.

Execution plan "D" has the node "Table Scan(OBJECT: (@T), WHERE: ([A]=[A]))". This clarifies why "if both sides of the comparison are columns ... the setting [SET ANSI_NULLS] does not affect the comparison."

Toreador SSChampion Points: 11358 More actions · Answer 9

Is there any situation where setting ansi_nulls off is a good idea?

Paul White SSC Guru Points: 150467 More actions · Answer 10

vk-kirov (4/7/2010)
Nice question.
Maybe I read it too fast: "Select all...", I selected "ABCD", then pressed the "Submit" button. Oops, there was "...that apply" part 🙂

Ha - yes it was terribly misleading, I'll ask Steve to credit the points to you :w00t:

It's interesting to explore the execution plans.
The condition "= NULL" is replaced with the condition "IS NULL". Execution plans "A" and "C" both have the node "Table Scan(OBJECT: (@T), WHERE: ([A] IS NULL))".
Execution plan "B" has the only node "Constant Scan", which means SQL Server understands (at compile time!) that there will be no output rows.
Execution plan "D" has the node "Table Scan(OBJECT: (@T), WHERE: ([A]=[A]))". This clarifies why "if both sides of the comparison are columns ... the setting [SET ANSI_NULLS] does not affect the comparison."

I am so glad you took the time to do that - I had great fun putting this question together, which included examining the query plans and having much the same reactions as you. I was particularly pleased with the = ANY version. Of course, some people will just run the code, I expect :Wow:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 11

Toreador (4/7/2010)
Is there any situation where setting ansi_nulls off is a good idea?

I have never come across one. Apart from the bizarre rules illustrated by the question, ANSI_NULLS OFF is going away in a future version. Attempting to set ANSI_NULLS OFF will generate an error message in that future version. Will be a huge change for some people.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Anil Gupta-301802 SSC Enthusiast Points: 166 More actions · Answer 12

Good QOD, got my brain cells working and had reindex all the chapters in my brain but still got it worng , thank you , A

Toreador SSChampion Points: 11358 More actions · Answer 13

Paul White NZ (4/7/2010)
I have never come across one.

Good - I won't worry too much that I got the answer wrong 😉

Paul White SSC Guru Points: 150467 More actions · Answer 14

Less than 50% pass rate so far :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi