Microsoft KB Archive/278656

= PRB: Like Predicate Optimization is Limited to 7 Character Positions =

Article ID: 278656

Article Last Modified on 7/16/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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 &quot;1234567&quot; to &quot;1234568&quot;: select * from t1 where c1 like '1234567890%' -- select * from t1 where c1 like '1234567890%' |--Filter(WHERE:(like([t1].[c1], '1234567890%'))) |--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 &quot;Symptoms&quot; section uses an index seek operation to return rows through the index, and then uses a filter to fully qualify the remaining rowset boundaries.

Keywords: kbprb KB278656

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.