Why does this query not use the index that exists on SURNAME?

Question

There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used.

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME 
FROM PATIENTS AS PAT
WHERE (PAT.SURNAME LIKE @SURNAME + '%' OR @SURNAME IS NULL)

It seems to be because we check if the variable is NULL as if I remove that then I get a constant scan, compute scalar, index seek and nested loop.

We need to check if the parameter is NULL as we then ignore that in the where clause.

Is there a way to do this and get SQL to use the index?

Cant you do this test before you execute the query? If @SURNAME IS NOT NULL then SELECT ... WHERE ... else SELECT ... PATIENTS — Lennart - Slava Ukraini, Sep 16 '15 at 15:42
@Lennart - I could, but there are more of these so then the logic would get complicated. The example above just kept the query simple. — chris, Sep 16 '15 at 15:57
You might try: SET @SURNAME = COALESCE(@SURNAME, '') before executing the query: SELECT ... FROM PATIENTS WHERE PAT.SURNAME LIKE @SURNAME + '%' but I'm not sure that the optimizer will realize that it can eliminate the predicate. Check the plan — Lennart - Slava Ukraini, Sep 16 '15 at 16:03
FWIW, the db2 optimizer does this. I.e. WHERE x like '%' is removed from the optimized statement. — Lennart - Slava Ukraini, Sep 16 '15 at 16:08

score 10 · Accepted Answer · edited Nov 13 '18 at 23:34

Why does this query not use the index that exists on SURNAME?

Because it has to compile a plan that will work correctly for both the cases where @SURNAME is NULL and when it is not.

In principle it would not be impossible for SQL Server to create such a plan that works in both cases and still uses an index. The dynamic seek mechanism already copes with the case that @SURNAME contains a leading wildcard and so the whole not null part of the index will need to be seeked - but your requirement is not something currently implemented.

The above would only be useful in the event that the index covers the query (as your example does). If the index is non covering then it would not be desirable to do a range seek on the whole index and then require lookups for the whole table.

Is there a way to get SQL Server to use the index?

If PAT.SURNAME is not nullable you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE ISNULL(@SURNAME, '') + '%' )

Alternatively you could request a new plan compilation every time, taking into account that execution's parameterised values, with

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
          OR @SURNAME IS NULL )
OPTION (RECOMPILE)

(as long as you are on a reasonably recent version that supports the parameter embedding behaviour.)

Or you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
         AND @SURNAME IS NOT NULL )
UNION ALL
SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( @SURNAME IS NULL )

The plan above will show two table references but hopefully both underneath a filter with a start up predicate so only the relevant branch actually gets executed.

A final option would be to just have two entirely separate queries and choose the correct one with procedural logic.

I suggest reviewing Dynamic Search Conditions in T‑SQL as an informative article on the topic.

Why does this query not use the index that exists on SURNAME?

1 Answers1