If you have a partitioned table or index in SQL Server, and you want to determine which partition a given value would be mapped to, you can do this nice and quickly with the $PARTITION system function.

All you need to know is the name of the partition function (and of course, the value you’re interested in).

Example

Here’s an example to demonstrate.

SELECT $PARTITION.MoviesPartitionFunction(5);

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 2                  |
+--------------------+

In this case, the value 5 would go into partition number 2.

Let’s try some other values.

SELECT 
    $PARTITION.MoviesPartitionFunction(-100) AS [-100],
    $PARTITION.MoviesPartitionFunction(100) AS [100],
    $PARTITION.MoviesPartitionFunction(1000) AS [1000],
    $PARTITION.MoviesPartitionFunction(100000) AS [100000];

Result:

+--------+-------+--------+----------+
| -100   | 100   | 1000   | 100000   |
|--------+-------+--------+----------|
| 1      | 2     | 3      | 4        |
+--------+-------+--------+----------+

Cross-Database Queries

You can also prefix the database name in order to query a different database.

SELECT MovieDb.$PARTITION.MoviesPartitionFunction(5);

Data Type Conversion

The data type of the value you provide must either match or be implicitly convertible to the data type of its corresponding partitioning column.

If not, you’ll probably get error 245.

SELECT $PARTITION.MoviesPartitionFunction('Hey!');

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Hey!' to data type int.

Note that $PARTITION returns the partition number for any valid value, regardless of whether the value currently exists in a partitioned table or index that uses the partition function.