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