Post reply

ANSI_NULLS setting is ignored?

  • 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?

  • September 27, 2011 at 10:49 am

    #1387339

    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

  • September 27, 2011 at 11:38 pm

    #1387546

    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

    --Microsoft SQL Server 2005 - 9.00.5292.00 (Intel X86) Apr 13 2011 15:56:31 Copyright (c) 1988-

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

  • September 28, 2011 at 7:01 am

    #1387720

    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

  • September 28, 2011 at 7:32 am

    #1387755

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 28, 2011 at 7:39 am

    #1387767

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 28, 2011 at 7:41 am

    #1387769

    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
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 28, 2011 at 8:20 am

    #1387798

    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

  • September 28, 2011 at 10:10 am

    #1387908

    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 🙂

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

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