SQL Server Performance

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

Discussion in 'T-SQL Performance Tuning for Developers' started by bobogs, Jun 27, 2007.

  1. bobogs New Member

    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!
    Gary
  2. Madhivanan Moderator

    Any function on indexed column will not make use of it

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

    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 Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de

Share This Page