Why SQL server Filtered index is not used when a variable in WHERE condition?
Why does MS SQL Server refuse using supporting filtered indexes 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'';
Everything goes as expected when using literals.
-- condition on literal value - index seek + key lookup :-) SELECT Col1 FROM #Test WHERE Col2 = N'ABC';
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. Why SQL server Filtered index is not used when a variable in WHERE condition? The reason for that is, Indexes may benefit queries with narrow ranges. The wider the range, the less likely it will result in an index hit.
Indexes should not be used on small tables. Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. For instance, you would not have an entry for the word "the" or "and" in the index of a book.
DBCC SHOW_STATISTICS('Test', 'IX_Test_Col2_filtered')When you use a constant value in a filter then Optimizer makes a plan specific for that value. Since Optimizer knows the parameter value,it uses the Statistics Histogram to estimate the number of records that can be returned by a query. When Local Variable is used in the where condition, Optimizer makes a plan for "OPTIMIZE FOR UNKNOWN". It does not use Statistics Histogram,it uses the information on the Density Vector.
`All Density* Number of rows` which is equal to `1*100001`So Number of rows to read will be 100001.As we know in cost base optimization ,Optimizer will choose a cost effective plan quickly. So choosing an index will not be feasible for Optimizer. If you Index Hint then it will use Index Seek but cost will be more. Optimizer makes a plan for OPTIMIZE FOR UNKNOWN because next time you change value for a local variable then it will reuse the same plan. In same case it is beneficial and in some case it will hurt performance.In OPTIMIZE FOR UNKNOWN assumption is based on calculation All Density * Number of rows. Now I create Proc ,
Create Proc TestProc @Filter NVARCHAR(20) as Begin SELECT Col1 FROM #Test WHERE Col2 = @Filter AND Col2 <> N''; End Exec TestProc N'ABC'Verify the plan, plan is exactly similar to (dynamic)sp_executesql .We see Index seek and key look up .No of Execution=1 Estimated Number of rows=1 when you write dynamic sql, DECLARE @Sql NVARCHAR(200) = '' DECLARE @Filter NVARCHAR(20) = N'ABC' set @Sql='SELECT Col1 FROM Test WHERE Col2 <> N'''' AND Col2 = @Filter1'; print @Sql execute sp_executesql @Sql,N'@Filter1 NVARCHAR(20)',@Filtersp_executesql is a Proc . Variable @Filter1 exactly behaves like a parameter of Proc. sp_executesql is not using @Filter.