There are multiple scenarios where usage of function on indexed column leads to Index/Table Scan instead of Index seeks. In this article we will go through few such use case scenarios.

Script @ https://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and Employee table used in this article. Create two Non-Clustered index’s using the below script, one index on the FirstName Column and second index on the HireDate Column of the Employee Table:

Create INDEX IX_Employee_FirstName ON Employee(FirstName)
GO
Create INDEX IX_Employee_HireDate ON Employee(HireDate)
GO

Use Case Scenario:
Need to write a query which returns all the employees for a given HireDate. As we know the HireDate column in the Employee table is of DateTime DataType and the date stored in this column has time part also in it.
For the above use cases we can write query as below:

SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),HireDate,101) = ’05/16/2001′

Now let us see the execution plan and IO’s for the above query:

From the above execution plan it is clear that instead of doing index seek, our query is resulting in Index Scan even when we have index on the HireDate column.

Workaround Solution:
One of the workaround solution is to re-write this query without using function on the index column HireDate as below:
SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE HireDate BETWEEN ’05/16/2001 00:00:00′ AND ’05/16/2001 23:59:59:997′

Now let us see the execution plan and IO when we execute this query. As expected it is now doing an index seek and IO’s are drastically reduced as shown in the below images: