How to Check SQL Server Table Partitioning

Leave a reply

To check the partition information for a table in SQL Server and understand its partitioning type, you can use various system catalog views and functions.

1. Check if a Table is Partitioned

To find out if a specific table is partitioned, use the following query:

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ps.name AS PartitionSchemeName,
    pf.name AS PartitionFunctionName
FROM 
    sys.tables t
JOIN 
    sys.indexes i ON t.object_id = i.object_id
JOIN 
    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE 
    t.name = 'YourTableName';


NOTE:
PartitionSchemeName: The name of the partition scheme applied to the table.
PartitionFunctionName: The partition function that defines the boundaries of the partitions.
If no rows are returned, then the table is not partitioned.

2. Check Partition Details for a Table

To view the partition distribution and row count for each partition of a table, use the following query:

SELECT 
    p.partition_number, 
    p.rows AS RowCount, 
    fg.name AS FileGroupName,
    r.value AS PartitionRangeBoundary
FROM 
    sys.partitions p
JOIN 
    sys.filegroups fg ON p.data_compression = fg.data_space_id
JOIN 
    sys.partition_schemes ps ON p.partition_id = ps.data_space_id
JOIN 
    sys.partition_range_values r ON ps.function_id = r.function_id AND p.partition_number = r.boundary_id
WHERE 
    p.object_id = OBJECT_ID('YourTableName')
    AND p.index_id IN (0, 1);  -- 0 for heap tables, 1 for clustered index

3. Determine the Partitioning Column

To find out which column is used for partitioning, you can query the sys.index_columns system view.

SELECT 
    t.name AS TableName,
    ic.column_id,
    c.name AS ColumnName,
    ic.partition_ordinal
FROM 
    sys.indexes i
JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    t.name = 'YourTableName' 
    AND ic.partition_ordinal > 0;  -- Only partitioning columns

4. Check Partition Function Information

You can inspect the partition function, its type (e.g., RANGE LEFT or RANGE RIGHT), and boundary values with the following query:

SELECT 
    pf.name AS PartitionFunctionName,
    pf.type_desc AS PartitionType,
    r.value AS RangeBoundary
FROM 
    sys.partition_functions pf
JOIN 
    sys.partition_range_values r ON pf.function_id = r.function_id
WHERE 
    pf.name = 'YourPartitionFunctionName';

5. Checking Indexes on Partitions

If your table has indexes, you can check whether those indexes are also partitioned using this query:

SELECT 
    i.name AS IndexName,
    ps.name AS PartitionSchemeName,
    pf.name AS PartitionFunctionName,
    i.type_desc AS IndexType
FROM 
    sys.indexes i
JOIN 
    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE 
    i.object_id = OBJECT_ID('YourTableName');

6. Find Out the Type of Partitioning

To determine the type of partitioning, you can examine the partition function. SQL Server supports two types of partitioning functions:

  • RANGE LEFT: Partitions include the left boundary.
  • RANGE RIGHT: Partitions include the right boundary.

You can see this in the query that checks partition functions (sys.partition_functions), which includes the column type_desc.

SELECT 
    pf.name AS PartitionFunctionName,
    pf.type_desc AS PartitionType  -- This will be RANGE_LEFT or RANGE_RIGHT
FROM 
    sys.partition_functions pf;

Leave a Reply