• There's no way around that.

    A filtered index for deal_date > '20120401' cannot satisfy a query WHERE deal_date > @YStartDate because of the parameterisation.

    The plan is cached and reused. The optimiser, knowing that, must generate a plan that is safe for reuse, one where the result will always be correct and the query will not error. In most cases, that 'safe for reuse' rule applies even with recompile is specified.

    A plan that uses the filtered for a parameterised query is not safe for reuse, because if the query is rerun with a parameter value less than what is specified in the filtered index, the results of the query will be wrong.

    p.s. Please post new questions in a new thread in future.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass