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.|||"almurph@.altavista.com" wrote:
> 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.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
--
Gert-Jan