BUG: LIKE with ESCAPE May Result in Different Query Access Plan (262340)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

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.

MORE INFORMATION

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

Modification Type:	Major	Last Reviewed:	10/29/2003
Keywords:	kbBug kbpending KB262340