SQL Server parameterized query does not use Non Clustered Filtered

Question

I defined a non clustered index with Include and Filter on Students table. The SQL Server version is 2017.

Students table definition:

CREATE TABLE [dbo].[Students]
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](50) NOT NULL,
    [CreatedOn] [DATETIME2](7) NOT NULL,
    [Active] [BIT] NOT NULL,
    [Deleted] [BIT] NOT NULL,

    CONSTRAINT [PK_Students] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

Non-clustered index with include and filter:

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20200508-225254] 
ON [dbo].[Students] ([CreatedOn] ASC)
INCLUDE([Name]) 
WHERE ([Active] = (1) AND [Deleted] = (0))
ON [PRIMARY]
GO

This query uses NonClusteredIndex-20200508-225254

SELECT Name, CreatedOn FROM dbo.Students
WHERE Active = 1
  AND Deleted = 0
ORDER BY CreatedOn

Actual execution plan

But when I use the parameterized query as following, it doesn't use the NonClusteredIndex-20200508-225254. Why does this happen? Where am I wrong?

DECLARE @Active BIT = 1
DECLARE @Deleted BIT = 0

SELECT Name, CreatedOn 
FROM dbo.Students
WHERE Active = @Active
  AND Deleted = @Deleted
ORDER BY CreatedOn

Actual execution plan

Answer 1

This is entirely expected.

When you compile a plan with parameters or variables it needs to produce a plan that will work for any possible value they may have.

You can add OPTION (RECOMPILE) to the statement so the runtime values of these are taken into account (basically they are replaced by literals with the runtime value) but that will mean a recompile every execution.

You are probably best having two separate queries, one for the case handled by the filtered index and one for the other cases.

You might have been hoping that SQL Server would do something like the below and dynamically switch between the clustered index scan + sort vs filtered index scan and no sort (the filters have startup predicates so only at most one branch is executed)

But getting this plan required a change to the filtered index to move Name into the key columns as below...

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20200508-225254] 
ON [dbo].[Students] ([CreatedOn] ASC, [Name] asc)
WHERE ([Active] = (1) AND [Deleted] = (0))

... and rewriting the query to

DECLARE @Active BIT = 1
DECLARE @Deleted BIT = 0

SELECT NAME,
       CreatedOn
FROM   dbo.Students WITH (INDEX =[NonClusteredIndex-20200508-225254])
WHERE  Active = 1
       AND Deleted = 0
       AND 1 = 1 /*Prevent auto parameterisation*/
       AND ( @Active = 1 AND @Deleted = 0 )
UNION ALL
SELECT NAME,
       CreatedOn
FROM   dbo.Students
WHERE  Active = @Active
       AND Deleted = @Deleted
       AND NOT ( @Active = 1
                 AND @Deleted = 0 )
ORDER  BY CreatedOn
OPTION (MERGE UNION)