Ok, so this one is interesting. I was reading an Internal DL (Distribution Group) post on tuning %Like% operator performance. I find some confusion in various places about index selectivity of '%Like%' operator, and I decided to do few test myself. Hopefully test details below can help a bit:
Problem Statement: - While optimizing high CPU consuming queries on 3rd party application, verified that most of the queries are using '%LIKE%' operator. - Interestingly enough, while some of these queries are going for "INDEX SEEK" while others are going for "INDEX SCAN". Why ?
Facts: As per SQL BOL - (http://msdn.microsoft.com/en-us/library/ms179859(SQL.90).aspx)
More importantly, wildcard char % can be placed in one of below Four ways:
1. SEARCH-STRING% - The SEARCH-STRING% will perform INDEX SEEK and return data in least possible time.
2. %SEARCH-STRING - When using %SEARCH-STRING it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.
3. %SEARCH-STRING% - When using %SEARCH-STRING% it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.
4. SEARCH%STRING - The SEARCH%STRING will perform INDEX SEEK and generate an initial result set, containing the values that match first string i.e. SEARCH%. Next it will scan through the values to get second string i.e. %STRING
Lab-Test: Product: SQL Server 2005 (SP2) x64 Database: Northwind Table: dbo.Customers
Here’s the output from SP_HELPINDEX on dbo.Customers before we proceed. Please note, “Customer” column already has a clustered index on it.
[Example 1.] Select on … SEARCH-STRING%
[Example 2.] Select on … %SEARCH-STRING
[Example 3.] Select on … %SEARCH-STRING%
[Example 4.] Select on … SEARCH%STRING
Note: SEARCH%STRING will return all values starting with 'S' and ending with 'A'
The moral of the story? You can tune/speed-up your ‘%Like%’ operator queries by making use of indexes. Just be aware of above limitations and write your queries accordingly.
Enjoy! and Thanks for reading.
Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.
The wildcard character can also be placed in the middle of a string e.g. 'John%on' will match both 'Johnson' and 'Johnston'.
Thanks for your comment. Yes! definitely, the wildcard character '%' can also be placed in the middle of a string as specified by you.
Also this should work as below:
'John%on' will give all strings which are starting with 'John%' and ending with '%on'.
2. Index Usage:
Will do 'Index Seek' while searching for all the string starting with 'John%' such as:
Next, it will then perform an 'Index Scan' to get filter the values ending with '%on' and will return below values
Hope this explains.
Thanks again for bringing this up, I will include this in above post.