Partitioning a Table Vs Partitioning a clustered index

Question

If I have a Table in SQL server, with Time series data for every business date (~100k rows a day) and only the last 45 days are frequently updated/deleted/inserted, it would be better to partition this table on the date, and only rebuild the partitions with significant fragmentation(instead of rebuilding the whole table)

In the case of a Clustered INDEX, is there any significant difference between partitioning the table versus mapping the partition function to the Clustered Index, Because SQL server lets you do both .Is there any difference is what happens under the hood? (Non-Clustered is not confusing since in that case the index is a separate entity)

Answer 1

The clustered index organizes the table as a b-tree index instead of as a heap so creating a partitioned clustered index will partition the table's rows.

The example below shows the ON [PRIMARY] specification of the CREATE TABLE DDL is effectively ignored and subsequently created non-clustered indexes are partitioned by default as well.

CREATE PARTITION FUNCTION PF_Date(date) AS RANGE RIGHT FOR VALUES('20230901','20230902');
CREATE PARTITION SCHEME PS_Date AS PARTITION PF_Date ALL TO ([PRIMARY]);
CREATE TABLE dbo.Example1(
      DateColumn date NOT NULL
        INDEX cdx CLUSTERED ON PS_Date(DateColumn)
    , OtherColumn int NOT NULL
) ON [PRIMARY];
CREATE INDEX idx ON dbo.Example1(OtherColumn);
INSERT INTO dbo.Example1 VALUES('20230901',1);
INSERT INTO dbo.Example1 VALUES('20230902',1);
GO

SELECT index_id, partition_number, rows
FROM sys.partitions 
WHERE object_id = OBJECT_ID(N'dbo.Example1');
GO

index_id	partition_number	rows
1	1	0
1	2	1
1	3	1
2	1	0
2	2	1
2	3	1

Answer 2

I know that your question was specifically about partitioning, and Dan Guzman's answer is good, but have you considered alternatives?

You could make the clustered index be on the time series, perhaps even a composite clustered index of time series + primary key. This would keep the frequently fragmented parts (you said it was the most recent that changed) at the end of the b-tree and not require lifting large sections of the table over and over for index maintenance. Especially if the queries you use typically specify the time series component as part of the query then this would probably greatly benefit those queries.

I mention this because while partitioning will solve this problem, by letting smaller partitions be defragmented, Dan's answer passes over the maintenance aspects of adjusting partitions so that you have these rolling ranges of active vs inactive.

Here is a sample of how to setup a table for this. Of course, changing a clustered index on an existing table could take time, but I think it will be worth it.

/** DEMO TABLE
    Note that I still have an Identity column going
    Note that there is a default for the TransactionDate (what I'm using for the timeseries)
    **/
CREATE TABLE dbo.Demo
    (
    DemoPrimaryKeyID INT NOT NULL IDENTITY(1,1)
    , TransactionDate DATETIME2(7) NOT NULL CONSTRAINT DF_Demo_TransactionDate DEFAULT (SYSUTCDATETIME())
    , TransactionValue DECIMAL(10,2) NULL
    )
GO

/** I've created a composite clustered index here.
    The table will be stored in TransactionDate, then DemoPrimaryKey order
    **/
CREATE CLUSTERED INDEX PK_Demo 
    ON dbo.Demo
    (TransactionDate, DemoPrimaryKeyID)
    WITH (FILLFACTOR=90, SORT_IN_TEMPDB=ON)

GO

/** IMPORTANT - I have a unique index/constraint on the DemoPrimaryKeyID
    **/
CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_Demo_DemoPrimaryKeyID
    ON dbo.Demo
    (DemoPrimaryKeyID)
    WITH (FILLFACTOR=100, SORT_IN_TEMPDB=ON)