LIKE in where won't use Indexes??

[Guest_imported]

Hi - hope some one can help out here - a colleague where I am working has asked for advice on query performance as a lot of the queries use LIKE operators in the where clauses and he seems to think that if this is the case then they won't use the indexes he's set up -

I've actually passed the MCP SQL7 design exam and have been wracking my brains on this as I think he's incorrect in this assumption (I personnaly don't recall anything to this effect - I do know however that the optimizer may choose not to use an index on the basis of uniqueness etc..)

can anyone say for certain whether this is or isn't the case

cheers
Antony

 

[ColinM]

If you're queries have wildcards at the start, they wont use indexes
i.e.
like '%smith'
but I think other than that exception, indexes WILL be used.

 

[Guest_imported]

cheers - I think you're right there - managed to scan BOL for info on indexes to this effect

thanks for reply - appreciate it
Ant

 

[HoustonGuy]

Also, remember that using any negated operator such as NOT or <> in a statement can cause the query to not use indexes.
Cody ford
codyjford@hotmail.com

VB6, Seagate Info/Crystal Reports, Data Warehousing

 

[bg716]

Indexes are not used when there is an ineqality, like operator and searching for null in a record set. This goes to the nature of an index which puts a feild in sorted order and when the value is found the engine knows it can stop searching. With the conditions given above it makes sence that an index (a sorted on a specific feild) would not be used.