SQL Server Index Scan when using 'OR' operator

Question

We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-chaos here.) The query:

SELECT 
    TOP(50) [Name], [Surname]
FROM 
    [dbo].[Clients]
WHERE 
    [Name] LIKE @SearchTerm + '%' OR
    [Surname] LIKE @SearchTerm + '%'

It is a sizable table, so I added two non-clustered indexes on both columns to help speed things up:

CREATE NONCLUSTERED INDEX [IX_Patients_Name] ON [dbo].[Clients]
(
    [Name] ASC
)
INCLUDE([Surname]);

CREATE NONCLUSTERED INDEX [IX_Patients_Surname] ON [dbo].[Clients]
(
    [Surname] ASC
)
INCLUDE([Name]);

My thinking was that SQL would do an index seek on both columns, but it seems the query optimizer decides to use an index scan.

This might not be a real issue for this simple use-case, but we have more complex versions of this with multiple joins, etc.

Is there any way to optimize this query to uses seeks?

Answer 1

As you mentioned in your question, this kind of Google-style query isn't really what SQL Server is "good at." Erik Darling talked about this exact query anti-pattern in his post The Only Thing Worse Than Optional Parameters….

All that aside.

It's possible to naturally get a seek with that type of query, but it's much more common to get the scan as you noticed. Here's an example from the StackOverflow2010 sample database.

First I'll create these two helpful indexes:

CREATE NONCLUSTERED INDEX IX_DisplayName ON dbo.Users (DisplayName) INCLUDE ([Location]);
CREATE NONCLUSTERED INDEX IX_Location ON dbo.Users ([Location]) INCLUDE (DisplayName);
GO

Then I'll create a procedure similar to the one you have:

CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName, 
        [Location]
    FROM 
        dbo.Users
    WHERE 
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

If I run that procedure with a fairly selective parameter, I'll end up with an index union plan. If the parameter is less selective, a scan of one of the covering indexes is used instead.

DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'Josh';
GO
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'S';
GO

Execution plans are here.

Note that this is true even if you write this as separate UNION queries directly.

One way to reliably get the index union plan, as mentioned in the linked post, is to add a FORCESEEK hint to the table you'd like to union on.

If I change the proc to this, I don't get scans on either plan:

CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName, 
        [Location]
    FROM 
        dbo.Users WITH (FORCESEEK)
    WHERE 
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

The bigger issue with the query, as simplified in the question anyway, is that you are using TOP without an ORDER BY, which is likely to produce drastically different search results depending on which index is used. Make sure your real query has an ORDER BY, or that this problem is accounted for in some way.

Answer 2

And the reason why SQL Server is restrictive to do SEEKs is the elimination of duplicates. Imagine a person named Josh Josh. Perhaps we find that strange, but how can SQL Server know that there are no persons with same first and last name? So SQL Server find this person through the index in FirstName, and SQL Server also find this same person through the index on LastName.

You don't want to see Josh Josh twice, so SQL Server need to eliminate the duplicates. Either through hashing or sorting. None of those are cheap (unless the union of the rows matched is small), as you can see in the plan that Josh posted.