Sql-server – Problem trying to force the use of a filtered index

filtered-indexhintssql server

I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation:

CREATE TABLE IndexTest 
(
    ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY, 
    Col1 CHAR(1)
)
GO

CREATE INDEX ix_IndexTest ON IndexTest(Col1)
WHERE Col1 IS NOT NULL
GO

INSERT INTO IndexTest VALUES ('A'),('B'),('C'),('D')
INSERT INTO IndexTest VALUES (''),(''),(''),('')
INSERT INTO IndexTest VALUES (NULL),(NULL),(NULL),(NULL)
GO

I then tried to run the following query using a hint to force it to use the filtered index.

SELECT * 
FROM IndexTest WITH (INDEX(ix_IndexTest))
GO

But I keep getting the error:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

It works if I remove the filter but not with it. I even tried changing the filter in various ways such as WHERE Col1 = '' etc. Any time I have a filter I get that error. Can anyone tell me why? Am I doing something wrong?

I tried this on a SQL Server 2012 and 2014 instance and created a sqlfiddle of it here:

http://www.sqlfiddle.com/#!6/4a850/1

Edit: Not sure that it matters but specifically I was trying to see if I could see what rows were actually contained by the index. The idea came from seeing someone use this technique to recover data (using an unfiltered index) from a corrupted table.

Best Answer

Possibly the disconnect is that the filtered index won't automatically filter the results for you - you have to write a WHERE clause that matches the index's filter predicate in order to be able to use the index.

In other words, if you're only trying to get rows WHERE Col1 IS NOT NULL, you still need a WHERE clause to limit the rows. If SQL Server can't use the index you're trying to force to satisfy all rows to be returned by the query, which it can't do by definition of the index holding fewer (or the same*) rows than the table, it can't run.

* There might be scenarios I haven't tested where this will work; for example, if the column is constrained to NOT NULL (or all rows happen to match the filter predicate), then the filtered index would, by extension, represent rows from the entire table. But that is a pretty contrived and impractical scenario (even if it were to work). And SQL Server couldn't generate a plan for the case where all rows happen to currently match the filter, because a single insert or update could break it.

If you're trying to locate data from the non-clustered index because the underlying table/CI is corrupt, you will probably have better luck with DBCC IND/PAGE or with a hex-editor-wielding Paul Randal impersonation.

Related Question