When you create a partitioned table in SQL Server, you specify which values go into each partition.

This is done when you create the partition function. When you create the partition function, you specify boundary values, which determine which values go into each partition.

Once you’ve created your partitioned table, and you’ve inserted data, you can run a normal SELECT statement to return data, just as you would with a non-partitioned table (actually, even non-partitioned tables have one partition).

But did you know that you can also specify which partition you want data from?

You can do this with the help of the $PARTITION system function in your WHERE clause.

Example

Imagine we have a partitioned table that contains the following data:

+---------+-------------+
| CatId   | CatName     |
|---------+-------------|
| 1       | Meow        |
| 2       | Fluffy      |
| 3       | Scratch     |
| 4       | Bulldog     |
| 5       | King George |
| 6       | Sharp       |
| 7       | Fritz       |
| 8       | Garfield    |
| 9       | Boss        |
+---------+-------------+

And CatId is the partitioning column.

And we used the following partition function to create its partitions:

CREATE PARTITION FUNCTION CatsPartitionFunction (int)  
    AS RANGE LEFT FOR VALUES (-1, 5, 100);

This tells us how the data is stored, according to the values in the partitioning column.

So we can now run a query that only returns data from a specific partition.

Return Data from the Second Partition

Here’s how we can return all rows from the second partition.

SELECT * FROM Cats
WHERE $PARTITION.CatsPartitionFunction(CatId) = 2;

Result:

+---------+-------------+
| CatId   | CatName     |
|---------+-------------|
| 1       | Meow        |
| 2       | Fluffy      |
| 3       | Scratch     |
| 4       | Bulldog     |
| 5       | King George |
+---------+-------------+

Return Data from the Third Partition

And here’s all data from the third partition.

SELECT * FROM Cats
WHERE $PARTITION.CatsPartitionFunction(CatId) = 3;

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 6       | Sharp     |
| 7       | Fritz     |
| 8       | Garfield  |
| 9       | Boss      |
+---------+-----------+

Return Data from the First and Last Partitions

In this case, my first partition is empty, because there are no negative values in the CatId column.

SELECT * FROM Cats
WHERE $PARTITION.CatsPartitionFunction(CatId) = 1;

Result:

(0 rows affected)

Similarly, the last partition is also empty, due to there not being enough rows to populate that partition.

SELECT * FROM Cats
WHERE $PARTITION.CatsPartitionFunction(CatId) = 4;

Result:

(0 rows affected)

This adheres to Microsoft’s recommendation of leaving the first and last partitions empty, in order to avoid any data movement in the event that the partitions are split or merged in the future.