BUG: Filter Plan Chosen Instead of Selective Clustered Index Seek for LIKE or IN That Contains Predicates (261846)

SELECT * FROM TblA
WHERE A = '100'
  AND (B > '60' AND B < '61')
GO
				

SELECT * FROM TblA
WHERE (A = '100' OR A='200')
  AND (B > '60' AND B < '61')
GO
				

MORE INFORMATION

In such a situation, the filter is applied to a less specific index SEEK. For large tables this can lead to performance degradation because the rows are placed in cache before they are filtered. What you expect is a Clustered Index Seek to be chosen against the columns with the increased selectivity.

The following script demonstrates the problem:

CREATE TABLE TblA
(
A	VARCHAR(3)	NOT NULL,
B	VARCHAR(100)	NOT NULL,
C	VARCHAR(100)	NOT NULL
)
GO
CREATE UNIQUE CLUSTERED INDEX ucl_TblA ON TblA(A, B)
GO
SET NOCOUNT ON
GO 
DECLARE @x INT
SELECT @x = 0
WHILE @x < 1000
BEGIN
  SELECT @x = @x +1
  INSERT TblA (A, B, C)
  VALUES('100', CONVERT(VARCHAR(100), @x), CONVERT(VARCHAR(100), @x))
END
WHILE @x < 2000
BEGIN
  SELECT @x = @x +1
  INSERT TblA (A, B, C)
  VALUES('200', CONVERT(VARCHAR(100), @x), CONVERT(VARCHAR(100), @x))
END
GO

UPDATE STATISTICS TblA
GO
print ""
print ""
print "PERFORM Poorly (notice the Filter)"
print ""
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TblA
WHERE A = '100'
  AND B LIKE '60%'
GO
SELECT * FROM TblA
WHERE 
  (A = '100' AND B > '60' AND B < '61')
  OR
  (A = '200' AND B > '60' AND B < '61')
GO
SET SHOWPLAN_TEXT OFF
GO
print ""
print ""
print "PERFORM WELL"
print ""
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TblA
WHERE A = '100'
  AND (B > '60' AND B < '61')
GO
SELECT * FROM TblA
WHERE (A = '100' OR A='200')
  AND (B > '60' AND B < '61')
GO
SET SHOWPLAN_TEXT OFF
GO
				

The poorly performing queries generate SHOWPLAN texts (on SQL Server 7.0, SP1, and SP2) similar to the following:

StmtText
  |--Filter(WHERE:(like([TblA].[B], '60%')))
       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[TblA].[ucl_TblA]), SEEK:([TblA].[A]='100' AND [TblA].[B] >= '60' AND [TblA].[B] < '61') ORDERED)
				

In this example the statistics indicate the following: Density should encourage the SEEK on both columns (that is, A & B):

Statistics for INDEX 'ucl_TblA'.
Updated              Rows  Rows Samp Steps Density  Average key length       
-------------------- ----- --------- ----- -------- ------------------------ 
May 12 2000  2:09PM  2000  2000      286   0.0      0.0

All density   Columns       
------------- --------------
0.5           A
5.0000002E-4  A, B
				

NOTE: Relocating the clustered index does not seem to improve the symptoms.