Sql Server: Cautions Must Be Taken for WHERE Clause

During query performance tuning sessions, I have observed that most of us like to care about columns that are going to be used in WHERE clause. Indexes are carefully created on these columns to enhance performance. But most of the time we are unaware that these indexes are useless, because of the way we use columns in WHERE clause. Specially be careful for following two situations.

1. Arithmetic Operators

SELECT *

FROM dbo.TestTable

WHERE columnName * 5 > 500

2. Functions

SELECT FirstName

FROM HumanResources.Employee

WHERE SUBSTRING(FirstName,1,3)='Joh'

Use of arithmetic operations and functions in WHERE clause causes the optimizer to omit indexes (created on these columns). Resultantly query performance decreases dramatically.

Rewrite your queries to avoid arithmetic operators and function in WHERE clause.