Posted by Erland Sommarskog on 09/26/75 11:24

John (John@nospam.infovis.co.uk) writes:
> Is there a major speed difference if WHERE col like 'abc%' is used against
> WHERE col = 'abc' in a select statement in stored procedure?

It can be virtually difference at all, or it can like night and day.

If col is non-indexed, the only overhead is a somewhat more complex
comparison operation.

If col is indexed with a non-clustered index, the optimizer will have
to make a decision on whether to use the index. Assume for simplicity
that the index is unique. Then = will use that index, and access will be
fast. But if there is LIKE there is another story. In this case, there
are two possible strategies: use the index or scan the table. Scanning
the table is better a large proportion of the rows start with 'abc%'.
The optimizer does not know about this, but from statistics compiled
about the table, it can make an estimate. If the estimate is incorrect,
the plan may not be the best - something the users will very painfully
notice.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]