A partitioning key is the column or expression whose values determine the partition containing each table row. In SQL Server, choosing it around real reporting filters enables partition elimination: the engine can skip irrelevant partitions instead of scanning the whole table. section 10.7 section 10.8
Mistake: choosing a key without checking how queries use it.
- Filter directly on the key when possible. Combining CampaignID with an EventTime range allows elimination; using OR can force reads from every partition. section 10.8
- Keep comparison types aligned. An EventTime DATETIME filter using DATE variables can require conversion and prevent elimination; DATETIME variables preserve the demonstrated behavior. section 10.8
- Check parameterization. Simple parameterization can stop elimination; OPTION(RECOMPILE) and the demonstrated predicate workaround are available investigation points. section 10.8
ImpressionsArchive reporting query
A date-range query against the unpartitioned ImpressionsArchive reports 46,979 logical reads. The same EventTime range against ImpressionsArchivePartitioned reports 15,732 logical reads, illustrating the payoff when the partitioning key matches the filter.
