Home Forums SQL Server 2008 T-SQL (SS2K8) Where clause prevents index seek RE: Where clause prevents index seek

  • November 30, 2015 at 4:53 pm

    #1843064

    PHXHoward (11/30/2015)

    I get it that they look the same but I did not know that SQL Server was smart enough to use an NCI on a computed column that is not referenced anywhere in the query.

    When you do operations in the WHERE clause (like in your case '_' + myString) SQL cannot really use an index to retrieve the resulting value since it does not know what that resulting value will be. When you persist the results ahead of time to the table, then SQL can order those results accordingly in a NCI.

    ----------------------------------------------------