Below a couple of techniques that should coerce partition elimination while providing the same functionality as the
OR methods. These both use a
JOIN instead, one with a row constructor and the other a table variable. The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing the query itself.
JOIN ( VALUES ( @ContainerId1), ( @ContainerId2) ) AS containers ( ContainerId )
ON containers.ContainerId = Container_Partitioned.ContainerId;
DECLARE @Containers TABLE(ContainerId int PRIMARY KEY);
INSERT INTO @Containers VALUES(5);
INSERT INTO @Containers VALUES(30000000);
JOIN @Containers AS containers ON Containers.ContainerId = Container_Partitioned.ContainerId;
As to why, SQL Server generates a plan that can be reused regardless of the values that may be specified. With an index on ContainerId, I get a seek with a partition range as the first seek predicate and the supplied container values as the second. Remember that the partition number is treated much like the first column of an index an index in SQL 2008 and later. The partition range seek must allow for all partitions in order to be able to return any possible container id values with that plan.
Note that even though the unneeded partitions are touched, the number of logical reads for each is just the number of index levels since no rows will be found for those. Not ideal, especially if you have a lot of partitions, but not too expensive since those pages will likely remain memory resident if the query is executed often enough.
Interestingly, it seems SQL Server can eliminate partitions better in this case when the partitioning column is not the first column of an index. Without an index on ContainerId, the plan I get scans with a nested loop operator using a RangePartitionNew function to only access the partition with the specified container ids. One would think SQL Server could generate a similar plan for the index seek. There seems to be room for improvement but keep in mind that partitioning adds another layer of optimizer on top of the already complex job to optimizer has to do.