Why would ANSI_NULLS ON cause performance issues?

HubertFarnsworth used Ask the Experts™
Good Day!

I understand that ANSI_NULLS OFF is being deprecated, perhaps in SQL Server 2012. This concerns me only because we have experienced serious performance problems when ANSI_NULLS are ON. We have taken procedures compiled with ANSI_NULLS ON and they perform very poorly. When we recompile the exact same SP with ANSI_NULLS OFF, it is very fast. So, when the day comes that ANSI_NULLS is always ON, I fear my system will slow to a crawl.

I am sure rewriting the SQL is the answer, but I cannot figure out why ANSI_NULLS ON cause a performance issue, in the first place.

None of my SQL uses straight joins on nullable columns. We always use code like IsNull( TableA.ColA, '!NULL!') = IsNull( TableB.ColA, '!NULL!').

So, why would the ANSI_NULLS setting affect performance? Should we stop using IsNull in our joins and just do a straight join on nullable columns or use the ugly ((TableA.ColA = TableB.ColA) OR (TableA.ColA IS NULL and TableB.ColA IS NULL))?

Thanks in advance!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Only explanation I can think of is that with ANSI NULLs off, the join condition is simplified (the isNull removed), because NULL = NULL is valid comparision, allowing for direct index usage. With ANSI NULLS on, the IsNull needs to be applied, rendering indexes useless.
Top Expert 2012
Yes, that is exactly the reason.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial