How does SQL engine process LIKE expressions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arun - 12 Jul 2008 01:07 GMT

Any pointers on how the sql engine process LIKE expressions, especially on
cases like '%searchkey%'.

Thanks

Reply to this Message

Andrew J. Kelly - 12 Jul 2008 03:04 GMT

Sure that is easy. It will scan the entire index or table to find the
matching values. You cannot do a seek when the wildcard is at the
beginning.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Any pointers on how the sql engine process LIKE expressions, especially on
> cases like '%searchkey%'.
>
> Thanks

Reply to this Message

Tibor Karaszi - 12 Jul 2008 08:49 GMT

...and on some cases, an index containing this column can be scanned and then "dive down" to the
data pages where matches are found (as opposed to all data is in the index or table scan). This is
where the "string indexes" (aren't really indexes, only statistics over the words in the string
column) can be useful to the optimizer - to try to determine selectivity. To check if such "string
index" exist see the output from DBCC SHOW_STATISTICS.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Sure that is easy. It will scan the entire index or table to find the matching values. You cannot
> do a seek when the wildcard is at the beginning.
[quoted text clipped - 3 lines]
>>
>> Thanks

Reply to this Message

Gert-Jan Strik - 12 Jul 2008 10:58 GMT

To elaborate on Tibor's response: if you are running SQL Server 2005 (or
later), then in many situations the database engine will (automatically)
keep String Summary information. Then, if you submit a query like

SELECT *
FROM my_table
WHERE string_column LIKE '%searchkey%'

(assuming a nonclustered, noncovering index on string_column, and a
clustered index on some other column)
it will estimate the number of qualifying rows based on these String
Summary statistics. With that (accurate) estimate it will determine
whether to scan a nonclustered index on string_column and look up the
remaining columns through the clustered index, or whether to simply scan
the table / clustered index.

If you are on SQL Server 2000 or earlier, or if your column + data does
not support String Statistics, then the optimizer will assume the number
of qualifying rows to be a fixed percentage of all rows, which typically
leads to the scanning of a covering index (such as the clustered index).

Signature

Gert-Jan
SQL Server MVP

> ...and on some cases, an index containing this column can be scanned and then "dive down" to the
> data pages where matches are found (as opposed to all data is in the index or table scan). This is
[quoted text clipped - 18 lines]
> >>
> >> Thanks

Reply to this Message

TheSQLGuru - 12 Jul 2008 21:43 GMT

I will add that if you routinely need to do such searches you may want to
consider using Full Text Indexing.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Any pointers on how the sql engine process LIKE expressions, especially on
> cases like '%searchkey%'.
>
> Thanks

Reply to this Message

Arun - 14 Jul 2008 21:08 GMT

Thanks for the replies.

If I'm performing a prefix match such as LIKE 'FOO%' does it translate this
internally into something like a range predicate: columnName >= “FOO” AND
columnName < “FOOA” and if that is the case then what will be the range for
the expression LIKE '%OO%'?

Reply to this Message

Andrew J. Kelly - 14 Jul 2008 22:20 GMT

Internally the engine will look at the stats for that index and decide how
to approach it but it may end up acting like a range for the case when the
wildcard is on the end. For the wildcard at the beginning it is exactly as
everyone has told you. It can not do a range unless you consider the range
the entire index or table.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Thanks for the replies.
>
[quoted text clipped - 4 lines]
> for
> the expression LIKE '%OO%'?

Reply to this Message