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 :

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.