ANSI_NULLS setting is ignored?

Question

ANSI_NULLS setting is ignored?

e-ghetto

SSChasing Mays

Points: 616
More actions
September 27, 2011 at 4:50 am

#248470

I'm setting the ANSI_NULLS setting for a query explicitely to "ON". But execution plan properties show "FALSE" for this setting. Is this a bug?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

That's interesting. I am seeing similar behavior, but the results are being returned according the active setting, not by what's reported in the execution plan.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

e-ghetto SSChasing Mays Points: 616 More actions · Answer 2

Jack Corbett (9/27/2011)
... the results are being returned according the active setting, not by what's reported in the execution plan.

Yes, you're right Jack - I can confirm on my machine the final results are also correct. But the execution plan reports the wrong values. What version are you using?

Here is my

SELECT @@Version

--2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I'm on 2k8 SP2:

Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) Feb 25 2011 13:56:11 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I'm going to see if I can get Grant Fritchey, the execution plan guru, to comment on this.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Not sure, it seems to lag by an execution. If I run it once, it shows as the way I ran it previously.

Version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (X64) Jun 1 2011 15:43:18 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Further, if I set a GO in place to separate the commands, it works right every time. Trying it on Denali now.

Same behavior in Denali.

Guess based on behavior, the properties display the setting at the time the batch is called.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul White SSC Guru Points: 150468 More actions · Answer 6

e-ghetto (9/27/2011)
I'm setting the ANSI_NULLS setting for a query explicitely to "ON". But execution plan properties show "FALSE" for this setting. Is this a bug?

Almost certainly a bug in the show plan XML generated by the server (and so repeated in the graphical output). Bugs in the XML are pretty common - they tend to get fixed gradually over time, and without much of a fanfare. I've never used these attributes because they always seemed to be wrong, though it seems they are more accurate with queries executed against later SQL Server releases. When I really need to see attributes, I use the sys.dm_exec_plan_attributes DMV.

Anyway, you'll get an answer eventually for your Connect item on this:

http://connect.microsoft.com/SQLServer/feedback/details/691270/ssms-execution-plan-reports-wrong-results-on-set-options-ansi-nulls-ansi-padding

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 7

I'm using 2008 R2 Dev Edition, and I get the correct settings per the script.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

e-ghetto SSChasing Mays Points: 616 More actions · Answer 8

SQL Kiwi (9/28/2011)

... When I really need to see attributes, I use the sys.dm_exec_plan_attributes DMV. ...

sys.dm_exec_plan_attributes is interesting!

But the whole story seems to be just a SSMS-Bug in particular releases. Maybe Microsoft will confirm this. Anyway - thanks for your replies and don't forget: Never trust your (SSMS-/)GUI 🙂