SQL Query Performance - Functions and Indexes

Indexes play an important role in SQL query performance, but improper knowledge of how indexes work can lead to degraded performance. In this article I will throw some light on one such practices. We all know that indexes help in select statements and make other statements (insert, update, delete) slower. At the same time if we use a function on an indexed column in a where statement then it does not use the index and hence the query performs the same as when it does not have a index. Let's look ar a realistic example.

Step 1: Create a table Person 

CREATE TABLE [dbo].[Person](
[ID] [char](800) NULL,
[FirstName] [char](2000) NULL,
[LastName] [char](3000) NULL,
[City] [char](500) NULL
)

Step 2: Insert some random data

INSERT INTO Person (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
‘Vineet’,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN ‘Sanjay’
ELSE ‘Mahesh’ END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘New Delhi’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘Chennai’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Hyderabad’
ELSE ‘Bangalore’ END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Step 3: Execute a select statement with a where clause on City:

SELECT * FROM Person
WHERE City = ‘Chennai’

Note : Until now we do not have any index on the City column.

The following is the execution plan:



Look at the query execution plan, SQL Server did a table scan and that is expected because there is no index on the City column.

Step 4: Create a non-clustered index on the City column as in the following:

CREATE NONCLUSTERED INDEX Index_Person_City
ON Person (City);

Step 5: Execute the following select statement:

SELECT * FROM Person
WHERE City = ‘Chennai’

Notice the execution plan (below), now SQL Server is using an indexed seek.



Step 6: Now let's execute a select query that uses a function on the column name in the where clause/

SELECT * FROM Person
WHERE CONVERT(Varchar(500), City) = ‘CHENNAI’

The following is the Query Execution Plan. SQL Server is using a scan that contributes to 29% of the total cost of the query whereas in Step 5, it used a seek that contributed to only 5% of the total cost.



Conclusion

So in summary, the preceding procedure shows that SQL Server was able to do a seek operation on the City column but when a function is used on the column it was not able to do a seek and hence it performed a scan operation.