Is this correct: first % in "Like" ignores indexes?

Camillia
Camillia used Ask the Experts™
on
This is SQL 2014.

I'm doing a search query using Like %whatever%

Is this correct:
First %-sign will make any index useless, but one at the end will use the index.

I got it from here columns in Like
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Yes that is correct.
WHERE whatever LIKE 'foo%' will use the index as the range is continuous.
WHERE whatever LIKE '%foo' will not.

I kid you not, that exact question was on one of my 70-761 practice exams.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Yes, that's correct, % at the prefix will make the existing index useless for that query..
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
A prefix of % does not make SQL "ignore" the index, it means that SQL can't do a seek on the index.  It could still scan the index to satisfy the query, and almost certainly will if that is the only condition in the WHERE clause.
Thanks, Scott
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
I stand correct.  Glad you said that, it might be on the exam.  Thanks Scott.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial