SQL Server Performance Tuning - Indexed column with Function in Sql query

By Santhosh N

This explains about using functions with Indexed columns in Sql query

While using any functions on the indexed column in the WHERE clause of the SQL query, the index is not properly used. Hence, always try to avoid using functions on the index column in the WHERE clause.


For instance, consider this scenario:

when you have index on any date field and you want to use any function always try to avoid using the function and try to achieve the desired result in other way.


For example, the following query


SELECT name, joindate
FROM register
WHERE YEAR(joindate) = 2000 AND MONTH(joindate) = 3

can be rewritten as below to make use of index


SELECT name, joindate
FROM register
WHERE joindate BETWEEN ‘3/1/2000’ AND ‘3/31/2000’

Related FAQs

This describes the usage of DBCC SHOW_STATISTICS command which is used for performance tuning in the Sql Server.
This explains the performance overhead of using NULL values in the columns of the Database table.
SQL Server Performance Tuning - Indexed column with Function in Sql query  (1154 Views)