Functions in a where clause

Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post.

-- returns 5 out of 5000 rows
SELECT InsertDate FROM TestingFunction WHERE LEFT(Code,1)='AA'

Knowing that there’s an index on the column code, the optimiser might be expected to use an index seek to satisfy the query. However, the execution plan shows an index scan. Why?

Any form of function on a column in the where clause of a query will prevent SQL from using an index seek to find the records. Even something as simple as SearchColumn+1 will prevent index seeks.

This is something I see over and over again in sample queries, on forums and in production code. It’s something that looks fine on the average dev system. Couple hundred rows, it’s hard to see a difference. In a production environment with a couple million rows, it’s a very different story.

There is often, though not always, a way of rewriting the query to move the function elsewhere. In the above example, the following query is equivalent and allows for an index seek.