Truncate partition failure - "Cannot specify partition number in the truncate table statement as the table is not partitioned."

Question

I've got a table named "events" in environment Azure SQL server Paas 2019.

I've created a partition on that table to delete old records. I am getting an error when i try to truncate the partition. Error: Cannot specify partition number in the truncate table statement as the table 'dbo.SagaEvents' is not partitioned.

How do i solve this error?

I've checked and the data is partition on Nonclustered index "Created". Here are the result from the query :

select *
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name = 'SagaEvents'

Result : enter image description here

When trying to execute the script:

TRUNCATE TABLE [dbo].[SagaEvents] WITH (PARTITIONS (3))

I get the above error.

This is the step by step creation of the partition: 1.Create partition function:

DECLARE @DatePartitionFunction nvarchar(max) = 
 N'CREATE PARTITION FUNCTION DatePartitionFunctionByWeeks2025 (datetime2) 
 AS RANGE RIGHT FOR VALUES ('; 
DECLARE @i datetime2 = '20221001'; 
WHILE @i < '20250201' 
BEGIN 
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', '; 
SET @i = DATEADD(WEEK, 2, @i); 
END 
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');'; 
--select @DatePartitionFunction
EXEC sp_executesql @DatePartitionFunction; 
GO

2. Create Partition schema:

CREATE PARTITION SCHEME FileGroupDates 
AS PARTITION DatePartitionFunctionByWeeks2025 
ALL TO ( [PRIMARY] );

3. Create non clustered index with partition group

-- create non cluster index 
DROP INDEX IF EXISTS IX_SagaEvents_Created ON [dbo].[SagaEvents]
CREATE NONCLUSTERED INDEX [IX_SagaEvents_Created] ON [dbo].[SagaEvents]
(
 [Created] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON FileGroupDates([Created])
GO

Answer 1

A requirement for partition level TRUNCATE (and SWITCH) is the table and non-clustered indexes must be aligned (partitioned using the same underlying function).

In your case, the non-clustered index is partitioned but the clustered index is not partitioned, hence the error message telling you the table is not partitioned.

• dexon: so i need to drop and recreate the clustered index with partitioning also?
• Dan Guzman: Yes, you'll need to partition the clustered index similarly for partition-level truncate. Note that if the clustered index is unique (e.g. primary key), the clustered index key must include the partitioning column. This may have performance implications depending on your queries.
• dexon: Thanks Dan - works. I've recreated the primary key as a composite primary key with two columns. Id and Created. I'll have to check the performance implications for this move - the primary key is not unique but an Identity int variable. This is the code for the creation ``` ALTER TABLE [dbo].[SagaEvents] ADD CONSTRAINT [PK_SagaEvents] PRIMARY KEY CLUSTERED ( [Id] ASC, [Created] asc )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON FileGroupDates([Created]) GO ```