Sql server–Using indexes with LIKE operator in stored procedures

 

In the most cases msdn is a trusted source of information. Of course sometimes some mistakes are slipping into. The other day I read a documentation about CREATING STORED PROCEDURES when I found an interesting slip-up:

“Avoid using a wildcard as the leading character in a LIKE clause, for example, LIKE ‘%a%’. Because the first character is non-deterministic, the query processor is unable to use available indexes. Use LIKE ‘a%’ instead.”

Well, I thought there was a catch in it because I use the LIKE operator with leading wildcard in many cases and I know, we can get a significant speed increase with indexes in this case too.

I made a simple test using my Northwind database:

-- create sample stored procedure for demonstrate
-- the using of indexex with leading wildcards
create procedure SelectCustomers
as begin
    select * from Customers
    where CompanyName like 'a%'
end

go

-- running the example
exec SelectCustomers

go

-- delete the example
drop procedure SelectCustomers

After running I got the following result in my actual execution plan:

The conclusion about using indexes with LIKE operator is the following:

- without wildcards: Index seek (like ‘a’)

- with wildcards: Index scan (like ‘a%’, like ‘%a’, like ‘%a%’)