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


ANSI_NULLS


ANSI_NULLS

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36890 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36890 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Anil Gupta-301802
Anil Gupta-301802
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 316
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
Toreador
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3878 Visits: 8129
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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36890 Visits: 11361
Less than 50% pass rate so far w00t



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Anil Gupta-301802
Anil Gupta-301802
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 316
It is quite good question i think everyone knows but still don't know, I hope i am making sense like the question :-)
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: 26533 Visits: 12506
Paul White NZ (4/7/2010)
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.

The only time I can imagine it being useful is when you've inherited a DB with a lot of SQL scattered around embedded in application code that relies on the setting because that's the way it was designed by the IT development team on the Ark.
If you are lucky, that will never happen to you. I reckon most people will be lucky.

Edit: just realised there's another use: to run code or get execution plans for QsOTD that specify itHehe.

Tom

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: 26533 Visits: 12506
Nice clear question, correct answer, nice clear explanation.
The success rate is amazing. When I read the question I thought there would be a large majority getting it right - obviously I was wrong about that.

Tom

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36890 Visits: 11361
Tom.Thomson (4/7/2010)
Nice clear question, correct answer, nice clear explanation.
The success rate is amazing. When I read the question I thought there would be a large majority getting it right - obviously I was wrong about that.

Thank you, Tom.
As far as the prevalence of ANSI_NULLS OFF in the wild...my personal experience is that it is pretty common - but not in well-organized shops with standards ;-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
honza.mf
honza.mf
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2583 Visits: 1323
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.


Be aware! This setting is per connection, not per server. And SP keep the setting of the connection they are created in.

Remarked by a person that lives in environment, when ANSI NULLS are (due to history) moving from OFF to ON Crazy



See, understand, learn, try, use efficient
© Dr.Plch
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