Parameterization affecting query optimizer plan on SQL Server 2008 R2

Question

Refering to the scenario described on my previous post:

In a T-SQL script I have the initialization code:

declare @DeviceID int
declare @Partition int

set @DeviceID = 4000
set @Partition = 4000 % 250

And, if I try the following query, I get partition elimination:

select COUNT(*) 
from Devices
where DeviceID = @DeviceID 
and Date_Time >= '2010-02-01' and Date_Time < '2010-03-01'
and Partition = 0

But, if I try the following, I don't get partition elimination:

select COUNT(*) 
from Devices
where DeviceID = @DeviceID 
and Date_Time >= '2010-02-01' and Date_Time < '2010-03-01'
and Partition = @Partition

How am I going to be able to build a stored procedure if I need to explicitly set the partiton value this way?

Answer 1

The query plan is generated based on the entire of the procedure and prior to any execution - the plan engine and execution engine being different entities as such.

So at the time the query plan is being generated, the value of @Partition is unknown (and we assume what you partitioned upon), even though you can see that it represents a deterministic numeric value, the plan engine is not doing the maths to get this literal value at the time of planning the query.

As a result, the query can not partition eliminate, since it does not know which partitions it is eliminating as yet. When you use a literal numeric value, it will show elimination, because no pre-processing is required to compare that against the values for the partitioning.