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

Thanks

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.


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Arun" <Arun@discussions.microsoft.com> wrote in message
news:8467436B-129A-42AD-9E69-F1B58709722E@microsoft.com...
> Any pointers on how the sql engine process LIKE expressions, especially on
> cases like '%searchkey%'.
>
> Thanks

....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.

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


"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:eRaZiO84IHA.1892@TK2MSFTNGP06.phx.gbl...
> 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.
>
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Arun" <Arun@discussions.microsoft.com> wrote in message
> news:8467436B-129A-42AD-9E69-F1B58709722E@microsoft.com...
>> Any pointers on how the sql engine process LIKE expressions, especially on
>> cases like '%searchkey%'.
>>
>> Thanks
>

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).

--
Gert-Jan
SQL Server MVP


Tibor Karaszi wrote:
>
> ...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.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:eRaZiO84IHA.1892@TK2MSFTNGP06.phx.gbl...
> > 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.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "Arun" <Arun@discussions.microsoft.com> wrote in message
> > news:8467436B-129A-42AD-9E69-F1B58709722E@microsoft.com...
> >> Any pointers on how the sql engine process LIKE expressions, especially on
> >> cases like '%searchkey%'.
> >>
> >> Thanks
> >

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

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


"Arun" <Arun@discussions.microsoft.com> wrote in message
news:8467436B-129A-42AD-9E69-F1B58709722E@microsoft.com...
> Any pointers on how the sql engine process LIKE expressions, especially on
> cases like '%searchkey%'.
>
> Thanks

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%'?

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.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Arun" <Arun@discussions.microsoft.com> wrote in message
news:980D3A6A-735E-4963-9C4A-310FCDF24F78@microsoft.com...
>
> 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%'?
>