Why SQL server Filtered index is not used when a variable in WHERE condition?

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.