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.
- Arithmetic Operators
WHERE columnName * 5 > 500
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.