PRB: Like Predicate Optimization is Limited to 7 Character Positions (278656)
The information in this article applies to:
This article was previously published under Q278656 SYMPTOMS
When determining the valid search range for a LIKE query, SQL Server is limited to 7 character positions during optimization.
For example, look at the following query plan. When you look at the Showplan output the Clustered Index Seek is using a range from "1234567" to "1234568":
select * from t1 where c1 like '1234567890%'
----------------------------------------------------------------------------------------------------------
select * from t1 where c1 like '1234567890%'
|--Filter(WHERE:(like([t1].[c1], '1234567890%')))<BR/>
|--Clustered Index Seek(OBJECT:([server].[dbo].[t1].[idx]), SEEK:([t1].[c1] >= '1234567' AND [t1].[c1] < '1234568') ORDERED)
STATUS
This behavior is by design.
MORE INFORMATION
The SQL Server engine uses the range boundary technique to help optimize and produce the results of a Like expression so rowset qualification is more efficient than string search techniques.
By using the appropriate character set information, the engine can determine the next or previous character and flatten to a greater than or less than expression.
The system DLL that is used to determine the upper and lower boundaries imposes a 7 character limit.
Thus, the plan shown in the "Symptoms" section uses an index seek operation to return rows through the index, and then uses a filter to fully qualify the remaining rowset boundaries.
| Modification Type: | Major | Last Reviewed: | 7/16/2001 |
|---|
| Keywords: | kbDSupport kbprb KB278656 |
|---|
|