title: Created filtered index is not used in execution plan
body: <p>I am trying to make use of filtered index on sql server 2008 R2.</p>
  
  <p>I am able to create the index successfully but while executing the query the created index is not used, even after dropping all the indexes using primary key.</p>
  
  <p>I have also tried to clear the plan cache but no change in execution plan.</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [IX_MyTable_test] ON [MyTable]
  (
      COL01 ASC
  )
  WHERE ([FK_ID]=(34))
  
  DBCC FREEPROCCACHE
  
  DECLARE @SQL NVARCHAR(MAX), @ProductID INT 
  SET @ProductID = 34 
  SET @SQL = N'select top 1 COL01 from [MyTable] 
  where [FK_ID] =' + CAST(@ProductID AS VARCHAR(10)) 
  EXECUTE sp_executesql @SQL    
  
  select top 1 COL01 from [MyTable] 
  where [FK_ID] = cast( 34 as varchar(10))
  </code></pre>
  
  <p>I have checked similar questions but could not find any solution for it. When tried similar script with adventureworks 2012 data base the created index was used there.</p>
  
  <p>So, is there any setting needed to be done at database level or how to make use of the filtered index?</p>