Monday, March 19, 2012

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.
Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.

No comments:

Post a Comment