Microsoft KB Archive/262340

= BUG: LIKE with ESCAPE May Result in Different Query Access Plan =

Article ID: 262340

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q262340



BUG #: 57398 (SQLBUG_70)



SYMPTOMS
An ESCAPE character in a LIKE expression allows you to search for a wildcard character in a character string. To denote this special use, you place the ESCAPE character in front of the wildcard character.

The problem is that the optimizer chooses to perform an index SCAN instead of a SEEK. Consequentially, query performance may deteriorate with large tables.



WORKAROUND
You can decrease the performance impact by evaluating your indexing architecture. For instance, if you have alternate unique row identifiers, you can use them to get your data. Or, you can add more columns (with indexes) to the query predicate to persuade the optimizer to use a SEEK against those indexes. Then, you use a FILTER on the result set, which uses the ESCAPE clause of the LIKE.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
Note the index selection strategy in the SHOWPLAN output.

(uncomment the last 2 lines for repeated execution.) CREATE TABLE Tbl1 (  Col1       VARCHAR(40) NOT NULL,   Col2       VARCHAR(1) ) GO ALTER TABLE Tbl1 ADD CONSTRAINT [Tbl1~0] PRIMARY KEY CLUSTERED (Col1) GO SET NOCOUNT ON GO DECLARE @x INT SELECT @x = 0 WHILE @x < 100 BEGIN SELECT @x = @x + 1 INSERT Tbl1 (   Col1,    Col2  ) VALUES (   CONVERT(VARCHAR(40), @x),    'A'                        ) END GO SET SHOWPLAN_TEXT ON GO SELECT * FROM Tbl1 WHERE Col1 LIKE '88' escape '%' SELECT * FROM Tbl1 WHERE Col1 LIKE '88' GO SET SHOWPLAN_TEXT OFF GO --DROP TABLE Tbl1 --GO

Keywords: kbbug kbpending KB262340

-

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

© Microsoft Corporation. All rights reserved.