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 ««12345»»»

ANSI_NULLS Expand / Collapse
Author
Message
Posted Wednesday, April 7, 2010 2:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
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

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898272
Posted Wednesday, April 7, 2010 2:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898273
Posted Wednesday, April 7, 2010 2:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:34 AM
Points: 94, Visits: 221
Good QOD, got my brain cells working and had reindex all the chapters in my brain but still got it worng , thank you , A
Post #898278
Posted Wednesday, April 7, 2010 2:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 1,736, Visits: 6,333
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
Post #898285
Posted Wednesday, April 7, 2010 2:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
Less than 50% pass rate so far



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898289
Posted Wednesday, April 7, 2010 2:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:34 AM
Points: 94, Visits: 221
It is quite good question i think everyone knows but still don't know, I hope i am making sense like the question
Post #898292
Posted Wednesday, April 7, 2010 3:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 8,682, Visits: 9,208
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 it.


Tom
Post #898300
Posted Wednesday, April 7, 2010 3:09 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 8,682, Visits: 9,208
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
Post #898301
Posted Wednesday, April 7, 2010 3:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898316
Posted Wednesday, April 7, 2010 4:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:20 AM
Points: 1,352, Visits: 1,312
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




See, understand, learn, try, use efficient
© Dr.Plch
Post #898330
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse