ISNULL() vs. (x = y or x is null)…index scan/see | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ISNULL() vs. (x = y or x is null)…index scan/see

Why would ISNULL(x, y) use an index scan, but (x = y OR x is null) use an available covering index? The covering index is not used at all when ISNULL() is used…it scans a nonclustered single-column index which is on a column used in another part of the WHERE clause (this column is also wrapped with an ISNULL()). Thanks!
Any function on indexed column will not make use of it Madhivanan Failing to plan is Planning to fail
Because SQL Server doesn’t know offhand what the result of the function will be, it has to try and evaluate the result. Therefore it cannot make an that effective use of an index as it could when rewriting the clause. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderator