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