Filtered index not used and key lookup with no output

Filtered index not used and key lookup with no output by GilaMonster

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	454744
Opened:	5/20/2009 9:45:37 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

A query with an IS NULL predicate that precisely matches a filtered index predicate where the index is covering gets a plan that uses a clustered index scan. If the index is forced via a hint then the expected nonclustered index scan is seen, but there's a key lookup present in the plan as well that's not looking anything up. It's output list is empty.

If the predicate of the query and the index is inverted (to IS NOT NULL) then the index is selected as expected and there is no key lookup present.

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Windows Vista 64-bit

US English

CREATE TABLE Employees(
ID INT NOT NULL IDENTITY,
ManagerID INT NULL,
Department VARCHAR(2) NOT NULL
)
GO

INSERT INTO Employees (ManagerID, Department)
SELECT CASE column_id WHEN 1 THEN NULL ELSE column_id END, 'ab'
FROM master.sys.columns

CREATE CLUSTERED INDEX PK_Employee ON Employees ( ID )
GO

CREATE INDEX IX_0 ON Employees ( Department ) INCLUDE (ID)
WHERE ManagerID IS NULL
GO

-- according to the section in Books Online on filtered indexes, IX_0 is covering for this query
SELECT ID
FROM Employees
WHERE ManagerID IS NULL

-- however by default, the execution plan chosen is a clustered index scan.
-- If I force the filtered index, then the plan does show a seek on the index IX_0, but there's also a key lookup in the plan, one with an empty output list

SELECT ID
FROM Employees WITH (INDEX = IX_0)
WHERE ManagerID IS NULL

DROP TABLE Employees

Query without hint shows a clustered index scan. Query with hint shows an index scan with key lookup.

Execution plan attached

Both queries get a straightforward index scan. No key lookup should be necessary here as the index is covering

X64

File Name	Submitted By	Submitted On	File Size
Filtered indexes.sqlplan (restricted)	GilaMonster	5/20/2009	-

Posted by Microsoft on 4/10/2010 at 11:08 AM

Dear Customer,

Thank you for pointing this out. After further investigation, we have discovered that this is actually not a bug but rather a known gap in functionality. This is now an active DCR for a future release of SQL Server.

Thank You,
Mohammed Ali

Posted by Microsoft on 6/3/2009 at 4:10 PM

Dear Customer,

Thanks for reporting this issue to us. It is valid, and we expect to release a fix in SQL Server Service Pack 2.

Thanks,
Yavor
Senior Program Manager
SQL Engine

Posted by RichardB CFCU on 9/29/2011 at 9:15 AM

I've experienced this as well (SQL2008R2 v10.50.1600.1 Std64bit), and a simple workaround is to INCLUDE the column that is being filtered on.

Example:
---
CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE]
(
[TABLE_ID] ASC,
[TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)
---

I look forward to this no longer being necessary in a future release!