My filtered index is not used: The impact of forced parameterization.

As of SQL Server 2008 filtered indexes can be used. This can save you a lot of index space and will improve performance during index updates and will result in less index defragmentation. In case your database is configured with forced parameterization, it can happen that your filtered index is not used. For instance you have an filtered index like:
CREATE INDEX ZipcodeFiltered on Zipcodes(Zipcode) WHERE Zipcode = '3000'

Next query is executed
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
Looking to the query plan the ZipcodeFiltered index is not used. This happens if your database is configured for Forced Parameterization.

To explain this behaviour I will create a repro sample:

CREATE DATABASE FilterTest
GO
USE [FilterTest]
GO
CREATE TABLE Zipcodes
    (ID INT,
     Zipcode NVARCHAR(20),
     City NVARCHAR (100),
     Extra NVARCHAR (100))

GO

INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3000','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3001','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3002','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3003','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3004','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3005','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3006','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3000','Rotterdam','TestFilter')
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3001','Rotterdam','TestFilter')
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3002','Rotterdam','TestFilter')

-- Create regular indexes and filtered version
GO
CREATE CLUSTERED INDEX Clus_zipcodes ON Zipcodes(ID)
CREATE INDEX Zipcode ON Zipcodes(Zipcode)
-- Create same index but now with filter
CREATE INDEX ZipcodeFiltered ON Zipcodes(Zipcode)
                WHERE Zipcode = '3000'
CREATE INDEX Extra ON Zipcodes(Extra)
-- Create same index but now with filter
CREATE INDEX ExtraFiltered ON Zipcodes(Extra)
                WHERE EXTRA IS NOT NULL

-- Database Not using forced parameterization
ALTER DATABASE FilterTest SET PARAMETERIZATION SIMPLE;

SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
-- Look in the queryplan: Index ZipcodeFiltered is used

SELECT Extra,ID FROM Zipcodes WHERE EXTRA = 'TestFilter'
-- Look in the queryplan: Index ExtraFiltered is used

ALTER DATABASE FilterTest SET PARAMETERIZATION FORCED;

SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
-- Look in the queryplan: Index Zipcode is used. So not the ZipcodeFiltered

SELECT Extra,ID FROM Zipcodes WHERE EXTRA = 'TestFilter'
-- Look in the queryplan: Index ExtraFiltered is still used. Even with Forced Parameterization

DROP . DATABASE FilterTest


Why is the filtered index not used with forced parameterization?

With forced parameterization the SQL Server engine will auto parameterize your query. This means that next query:
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
will be stored in the procedure cache as:
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = @0

The query plan for this query: SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = @0
will be using the 'regular' index without the filter because for the value @0 the query optimizer does not know if it is '3000' or another value.
As you have seen in the example this behaviour does not apply for filtered indexes with an WHERE COLUMN_X IS NOT NULL.

Conclusion:
You can use filtered indexes with Forced Parameterization if your filtered indexes are using a WHERE Clause like COLUMN_X IS NOT NULL.

Special thanks to my colleague Ad van der Hoeven for bringing this to my attention.

Enjoy the use of filtered indexes