All 7 Partitioning questions
More in Partitioning
What is table partitioning in SQL Server, and what problem does it help solve for very large tables? What is the difference between a partition function and a partition scheme in SQL Server? How does a sliding window pattern work for automatically archiving old data using partitioning? How do you determine which partition a specific row of data belongs to, and how can this help with troubleshooting? What requirements must be satisfied for the ALTER TABLE SWITCH PARTITION operation to work correctly when moving data between tables? How would you decide on an appropriate partitioning strategy, including the partition key and boundary values, for a very large table in a new system?
Advertisement
SQL Server › Partitioning

How does partition elimination improve query performance for a partitioned table?

Intermediate

Answer

Partition elimination allows SQL Server's query optimizer to skip scanning partitions that could not possibly contain data relevant to a query's filter condition, such as skipping every partition except the current year when a query filters for recent dates, dramatically reducing the amount of data that needs to be read.
-- Only the 2026 partition needs to be scanned
SELECT * FROM Sales
WHERE SaleDate >= '2026-01-01' AND SaleDate < '2027-01-01';
Real-world example A reporting query filtering only for the current year's sales runs significantly faster on a properly partitioned table, since SQL Server automatically skips scanning several years of older, irrelevant partitions entirely.

Common follow-ups: How do you verify that partition elimination is actually happening for a specific query?;What query patterns might accidentally prevent partition elimination from working?

Query Optimization & Plans;Columnstore Indexes
All 7 Partitioning questions