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:
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.