If you’ve got a partitioned table in SQL Server, and you now want to run a query that includes the partition number on each row returned by the query, you can use the $PARTITION system function to do just that.

The $PARTITION function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

You can therefore use it in your SELECT statement to return the partition that each row belongs to.

Example

Here’s an example to demonstrate.

SELECT 
    CatId,
    CatName,
    $PARTITION.CatsPartitionFunction(CatId) AS [Partition]
FROM Cats;

Result:

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

In this case, the rows are distributed between partitions 2 and 3.

Partition 1 is empty (as is partition 4), in accordance with Microsoft’s recommendation of leaving partitions at both ends empty, to eliminate data movement in the event partitions are split or merged.