0

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
  1. Create Partition schema:
CREATE PARTITION SCHEME FileGroupDates  
AS PARTITION DatePartitionFunctionByWeeks2025  
ALL TO ( [PRIMARY] );

  1. 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

1 Answer 1

3

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.

3
  • 1
    so i need to drop and recreate the clustered index with partitioning also?
    – dexon
    Nov 23, 2022 at 13:19
  • 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.
    – Dan Guzman
    Nov 23, 2022 at 13:24
  • 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 ```
    – dexon
    Nov 24, 2022 at 6:05

Your Answer

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

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