Plan Caching - Pro SQL Server Internals - page 496

,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 rows
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 rows
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2 ) -- 65,536 rows
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.RawData(Processed)
select 1
from Ids;
insert into dbo.RawData(Processed)
select 0
from dbo.RawData
where RecId <= 16;
create unique nonclustered index IDX_RawData_Processed_Filtered
on dbo.RawData(RecId)
include(Processed)
where Processed = 0;
For the next step, let's run the queries that count a number of unprocessed rows in both simple and forced
parameterization mode. Listing 26-21 shows the code to achieve this.
Listing 26-21. Forced Parameterization and Filtered Indexes: Test queries
select count(*)
from dbo.RawData
where Processed = 0
go
alter database ParameterizationTest set parameterization forced
go
select count(*)
from dbo.RawData
where Processed = 0
If you examine the execution plans shown in Figure 26-12 , you will notice that SQL Server utilized a filtered index
in the case of a simple parameterization. SQL Server can cache this plan because of the constant in the Processed=0
predicate. That plan would be valid regardless of the value of the @RecId parameter. Alternatively, with forced
parameterization, SQL Server parameterizes the query using the parameter in the Processed=@0 predicate. Therefore,
it cannot cache the plan with the filtered index because it would not be valid for the case when a query selects
processed ( Processed=1 ) rows. SQL Server generated the execution plan with a Clustered Index Scan, which is far less
efficient in this case.