If you’ve got a partitioned table or index in SQL Server, and you want to check the parameter type of the partition function, you can use the sys.partition_parameters system catalog view.

This view returns a row for each parameter of a partition function.

The parameter type should match or be implicitly convertible to the data type of the partitioning column in the table or index.

Example

Here’s an example that shows the columns returned by the sys.partition_parameters view.

SELECT * FROM sys.partition_parameters;

Result:

+---------------+----------------+------------------+--------------+-------------+---------+------------------+----------------+
| function_id   | parameter_id   | system_type_id   | max_length   | precision   | scale   | collation_name   | user_type_id   |
|---------------+----------------+------------------+--------------+-------------+---------+------------------+----------------|
| 65542         | 1              | 56               | 4            | 10          | 0       | NULL             | 56             |
+---------------+----------------+------------------+--------------+-------------+---------+------------------+----------------+

I’ve only got one partition function, and so only one row is returned.

Here’s the result again using vertical output (to save you from having to scroll sideways):

function_id    | 65542
parameter_id   | 1
system_type_id | 56
max_length     | 4
precision      | 10
scale          | 0
collation_name | NULL
user_type_id   | 56

Get the Type Name

The sys.partition_parameters view doesn’t actually return the type name. It returns the type ID. Actually, it returns the type ID of both the system type and the user defined type.

Fortunately, you we can use the TYPE_NAME() function to return the name of each.

SELECT
    function_id,
    parameter_id,
    TYPE_NAME(system_type_id) AS [System Type],
    max_length,
    precision,
    scale,
    collation_name,
    TYPE_NAME(user_type_id) AS [User Type]
FROM sys.partition_parameters;

Result (using vertical output):

function_id    | 65542
parameter_id   | 1
System Type    | int
max_length     | 4
precision      | 10
scale          | 0
collation_name | NULL
User Type      | int

Return the Function Name

We can also join the sys.partition_functions view to return the function name instead of its ID.

SELECT
    pf.name,
    pp.parameter_id,
    TYPE_NAME(pp.system_type_id) AS [System Type],
    pp.max_length,
    pp.precision,
    pp.scale,
    pp.collation_name,
    TYPE_NAME(pp.user_type_id) AS [User Type]
FROM sys.partition_parameters pp
INNER JOIN sys.partition_functions pf
ON pf.function_id = pp.function_id;

Result (using vertical output):

name           | MoviesPartitionFunction
parameter_id   | 1
System Type    | int
max_length     | 4
precision      | 10
scale          | 0
collation_name | NULL
User Type      | int