Functions in a where clause

Or ‘How to really slow a query down without trying’

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.

 SELECT InsertDate FROM TestingFunction WHERE Code LIKE 'AA%'

Sample data:

CREATE TABLE TestingFunction (
    ID int identity,
    Code CHAR(3),
    InsertDate DATETIME default getdate()
)

CREATE NONCLUSTERED INDEX idx_TestingFunction_Code on TestingFunction (Code)

;WITH DataInsert (RowNo) AS (
    SELECT 1 AS RowNo
    union all
    select RowNo+1 FROM DataInsert WHERE RowNo<5000
)
insert into TestingFunction (Code, InsertDate)
SELECT char(FLOOR(Rand(RowNo*78000)*26)+65) + char(FLOOR(Rand(RowNo*15000)*26)+65) + char(FLOOR(Rand(RowNo*51000)*26)+65),
    DATEADD(mi,Rand(RowNo*78000)*575000,'2000/01/01')
    FROM DataInsert
    OPTION (MaxRecursion 5000)