I'm on Azure SQL, adding monthly partitioning to some historical archive tables (creating a 2nd table with partitioning scheme/function and dumping the rows from the original into it). I was using a query to view all the partition numbers and date ranges and was able to see everything after inserting the data into the new table, but after I add the PK constraint, my query stopped returning rows, or if I comment out the joins on the sys.index table and below I only see a single partition. I'm not exactly sure what modifications I would need to make to the query below.


CREATE PARTITION FUNCTION [PF_YearMonthBiWeekly] (datetime)
    '2024-01-01', '2024-01-16', '2024-02-01', '2024-02-16', '2024-03-01', '2024-03-16', '2024-04-01', '2024-04-16', '2024-05-01', '2024-05-16', '2024-06-01', '2024-06-16', '2024-07-01', '2024-07-16', '2024-08-01', '2024-08-16', '2024-09-01', '2024-09-16', '2024-10-01', '2024-10-16', '2024-11-01', '2024-11-16', '2024-12-01','2024-12-16',

AS PARTITION [PF_YearMonthBiWeekly]

CREATE TABLE [dbo].[Table1]
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Test_ID] [int] NULL,
    [Metric_ID] [int] NULL,
    [Metric_Desc] [nvarchar](300) NULL  
    [Result_On] [datetime] NULL,
) ON [PS_YearMonthBiWeekly] ([Result_On]);

Insert data, query working, then add PK:

ALTER TABLE [dbo].[Table1] 

Partition View query:

    OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
    ,OBJECT_NAME(pstats.object_id) AS TableName
    ,pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
    ,c.name AS PartitionKey
        WHEN pf.boundary_value_on_right = 0 
        THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
        ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
    END AS PartitionRange
    ,pf.name AS PartitionFunctionName
    ,ps.name AS PartitionSchemeName
    ,ds.name AS PartitionFilegroupName
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
    ,prv.value AS PartitionBoundaryValue
    ,p.data_compression_desc AS DataCompression
        when i.[type] = 0 then 'Nonclustered Heap Index'
        when i.[type] = 1 then 'Clustered Index'
        when i.[type] = 2 then 'Nonclustered Unique Index'
        when i.[type] = 3 then 'XML Index'
        when i.[type] = 4 then 'Spatial Index'
        when i.[type] = 5 then 'Clustered Columnstore Index'
        when i.[type] = 6 then 'Nonclustered Columnstore Index'
        when i.[type] = 7 then 'Nonclustered Hash Index'
        else NULL
    end as index_type
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p 
    ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds 
    ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds 
    ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps 
    ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf 
    ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.indexes AS i 
    ON pstats.object_id = i.object_id 
    AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id 
    --AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic 
    ON i.index_id = ic.index_id 
    AND i.object_id = ic.object_id 
    AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c 
    ON pstats.object_id = c.object_id 
    AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv 
    ON pf.function_id = prv.function_id 
    AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
--WHERE pstats.object_id = OBJECT_ID('Table1')
--ORDER BY TableName, PartitionNumber;

1 Answer 1



When you create a clustered primary key on a heap, it transforms the original heap table that you created, by logically ordering the previously unordered structure by the key column(s) you've chosen, and replacing internal row identifiers (RIDs) with the index key columns. Sort of like how a caterpillar turns into a butterfly. A beautiful butterfly.

The clustered primary key you created has ON [PRIMARY], rather than on ON [PS_YearMonthBiWeekly] ([Result_On]), as the heap had originally. Doing so removes the partitioned-ness of the table.

You could fix this by including the Result_On column in the primary key as a key column, and using the original ON [PS_YearMonthBiWeekly] ([Result_On]) syntax, but it would require you to change the Result_On column to not be NULLable. Primary key columns cannot be NULLable.

You could also create a nonclustered primary key only on ID. If you do this, I would suggest making sure that you use the ON [PS_YearMonthBiWeekly] ([Result_On]) syntax so that the index is aligned to the partitioning scheme/function, and you can still use all of the data management capabilities of table partitioning.

If you can't use those data management capabilities, there is absolutely zero point Kelvin to partitioning a table in the first place.

If you go the second route, with a nonclustered primary key, you could also create a non-unique clustered index on Result_On, aligned to the partitioning scheme/function, if your ultimate goal is for the table to not be a heap.

There is no obvious right choice as to if the table should be a heap with a nonclustered primary key, or have a clustered index on it in some fashion. There are too many workload and local factors to account for to answer that question easily.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.