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:
-- 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%’)
