Filtered index is not used when the variable in WHERE condition

704    Asked by ankur_3579 in SQL Server , Asked on Apr 24, 2021

Why MS SQL Server refuse using supporting filtered index in this scenario?

-- demo data CREATE TABLE #Test ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID() ,Col2 NVARCHAR(20) NOT NULL DEFAULT N'' -- !! ); WITH L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5) INSERT INTO #Test(Col2) SELECT TOP 100000 N'' FROM Nums; INSERT INTO #Test(Col2) VALUES(N'ABC'); -- FILTERED index to support filter predicate of a query CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N''; -- just checking statistics DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered') -- condition on variable = index scan :-( DECLARE @Filter NVARCHAR(20) = N'ABC' SELECT Col1 FROM #Test WHERE Col2 = @Filter AND Col2 <> N'';

enter image description here

Everything goes as expected when using literals.

-- condition on literal value - index seek + key lookup :-) SELECT Col1 FROM #Test WHERE Col2 = N'ABC';

enter image description here



Answered by Carolyn Buckland

 Filtered indexes can't use variables/parameters unless you're building your query with dynamic SQL so that the query ends up getting executed with a literal.

A good article on the topic is Filtered Indexes and Dynamic SQL by Jeremiah Peschka. OR As Martin suggested, you could add WITH (RECOMPILE) to the query, but that should not be used without understanding the potential repercussions.



Your Answer

Interviews

Parent Categories