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

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?

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.

StackExchange Database Administrators Q#115258, answer score: 10