SQL Server : LIKE with parameter results in index scan instead of index seek

Question

I have 3 nonclustered indexes on my SQL Server user table, they are

FirstName
MiddleName
LastName

When I run this query

select * 
from user 
where FirstName like 'name' + '%' 
   or LastName like 'name'  + '%' 
   or MiddleName like 'name'  + '%'

The actual execution plan shows an index seek based on nonclustered index for limited number of rows.

But when I change the query to this:

declare @search varchar(30) = 'name'

select * 
from user 
where FirstName like @search + '%' 
   or LastName like @search + '%' 
   or MiddleName like @search + '%'

The actual execution plan performs an index scan on the whole table without taking nonclustered indexes into account.

The DDL is as below

CREATE TABLE [dbo].[user] (
    [UserGuid]                             UNIQUEIDENTIFIER NOT NULL,
    [CreateDate]                             DATETIME         NOT NULL,
    [LastModifiedDate]                       DATETIME         NULL,
    [Active]                                 BIT              NOT NULL, 
    [FirstName]                              VARCHAR (20)     NULL,
    [MiddleName]                             VARCHAR (100)    NULL,
    [LastName]                               VARCHAR (20)     NULL,
    [DateOfBirth]                            DATETIME         NULL,
    [Email]                                  VARCHAR (255)    NULL,
    [HomePhone]                              VARCHAR (10)     NULL,
    [WorkPhone]                              VARCHAR (10)     NULL
) 


CREATE CLUSTERED INDEX [M_ACTIVE]
    ON [dbo].[user]([Active] ASC, [LastName] ASC, [FirstName] ASC);

CREATE NONCLUSTERED INDEX [M_DateOfBirth]
    ON [dbo].[user]([DateOfBirth] ASC, [LastName] ASC, [FirstName] ASC, [MiddleName] ASC);


CREATE NONCLUSTERED INDEX [M_FIRSTNAME]
ON [dbo].[user]([FirstName] ASC) 

CREATE NONCLUSTERED INDEX [M_MIDDLENAME] 
ON [dbo].[user]([MiddleName] ASC)

CREATE NONCLUSTERED INDEX [M_LASTNAME]
ON [dbo].[user]([LastName] ASC)

What if `@search` contains `%ab`, resulting in `LIKE '%ab%'` ? That can't use an index. The optimizer doesn't know whether `@search` contains wildcards or not so it has to use a scan — Panagiotis Kanavos, Oct 22 '21 at 06:19
To be honest, it's probably more likely the `or` is not helping things. What happens if you try each predicate separately? — Charlieface, Oct 22 '21 at 06:32
@PanagiotisKanavos It's simply not true that an index seek can't be done. The compiler will create a begin and end seek point, see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8ab279947e9adca03f537056ddcfa4e8 and https://www.sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html — Charlieface, Oct 22 '21 at 06:35
The best you can get with this query is an index scan, because the query optimizer can't know what the parameter contains when it parses the query. That would still be better than a full table scan but not as fast as the index seek used in the first query — Panagiotis Kanavos, Oct 22 '21 at 06:35
Yes , i am using the express edition. When i use the predicate it still performs the index scan — abhijit rajan, Oct 22 '21 at 06:37
@Charlieface that fiddle doesn't apply. When you only have a single column, everything will seek or scan that column. The entire table is that index. Try with a *separate* indexed column name. Besides, if the term is is `%potato`, the query engine will have to search all rows. — Panagiotis Kanavos, Oct 22 '21 at 06:39
@abhijitrajan Express won't use other indexes in this case. Check the warning [in this answer](https://stackoverflow.com/a/38354578/134204): `if you add other columns in the SELECT, the optimizer will not use indexes anymore but it will go for full clustered index scan` — Panagiotis Kanavos, Oct 22 '21 at 06:40
@Charlieface if you [add two columns and indexes to your fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=005e7edb6bb2e1d5b8ee84e6ac753839) the execution plan changes to an index scan — Panagiotis Kanavos, Oct 22 '21 at 06:46
Please see the table ddl in the link [link](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3e1429abbc2f508205817ba6d14691a4) — abhijit rajan, Oct 22 '21 at 06:52
@PanagiotisKanavos , if the make changes in the code and run it as a dynamic sql or as sql command from the code, will it change from scan to seek ? `select * from user where FirstName like 'name' + '%' or LastName like 'name' + '%' or MiddleName like 'name' + '%'` — abhijit rajan, Oct 22 '21 at 07:02
@Charlieface or @PanagiotisKanavos when i try this `EXECUTE sp_executesql N'select * from user where FirstName like @search', N'@search varchar(50)', @search = 'name%';`it works fine — abhijit rajan, Oct 22 '21 at 07:25
Charlie is correct - the optimiser can create a plan with an index seek and calculate the seek start and end points at run time. In the case that the parameter contains a leading wildcard the range seeked is the whole index otherwise it can be much more selective. The `OR` here will mean that index seek can only be used if it decides to do an index union plan however (with lookups probably). Which will depend on cardinality estimates. — Martin Smith, Oct 22 '21 at 08:37
@PanagiotisKanavos - your fiddle is just a cost based choice as the index no longer covers the query. You can use `with (forceseek)` to get a plan with a seek but it estimates it as twice the cost of just scanning the CI due to the key loookup — Martin Smith, Oct 22 '21 at 08:42
@PanagiotisKanavos Indeed Martin Smith is right. Non-clustered index selecting all columns: yes it's going to choose a clustered scan instead. But if your NCI had `INCLUDE` columns then it would seek that. See http://sqlfiddle.com/#!18/07bbb1/3 And that link to the answer is a complete red-herring for the same reason. — Charlieface, Oct 22 '21 at 08:50
OK let's see your full table definition, including indexes, full DDL please (`CREATE TABLE`). Do you actually need to `select *`, can you cut down the number of columns you're selecting? — Charlieface, Oct 22 '21 at 08:51
Yes , that does work. But is it possible to get index seek on `select *` because when i try this `EXECUTE sp_executesql N'select * from user where FirstName like @search', N'@search varchar(50)', @search = 'name%';` the query works with seek — abhijit rajan, Oct 22 '21 at 09:16
Use `UNION` instead of `OR`. Please show use the things I asked for, otherwise we can't help you any further — Charlieface, Oct 22 '21 at 10:41
Do you *need* `select *`, can you cut down the columns selected? — Charlieface, Oct 22 '21 at 12:50
`declare @search varchar(30)= 'name%' select * from user where FirstName LIKE @search UNION select * from user where LastName LIKE @search UNION select * from userwhere MiddleName LIKE @search ` i have tried this but still it does index scan. and i do not need `select *` per say but this table is largely used everywhere and there might places where we need to have different columns. — abhijit rajan, Oct 22 '21 at 12:53
Because you want all those columns you are going to have to put them into the indexes as includes, so `CREATE NONCLUSTERED INDEX [M_FIRSTNAME] ON [dbo].[user]([FirstName]) INCLUDE (allTheOtherColumnsHere) WITH (DROP_EXISTING = ON)` and the same for `INDEX [M_LASTNAME]` and `INDEX [M_MIDDLENAME]`. Without doing this, the compiler is going to choose a clustered index scan, because it's just not worth it to do so many bookmark lookups. Alternatively, you could force an index seek by using an index hint on each of those parts of the `UNION` — Charlieface, Oct 24 '21 at 09:44

SQL Server : LIKE with parameter results in index scan instead of index seek

0 Answers0