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!