TRUNCATE PARTITION in older version
Certainly, you have heard about TRUNCATE. On a table.
Also probably you’ve already heard about TRUNCATE on partitions. This feature is in the latest version (latest, I mean 2016 as the 2017 has not been released yet) of SQL Server. And it’s very cool.
But this post is not about this statement. I only mention about it to give you an opportunity to compare both solution: the newest-one-cool-statement TRUNCATE PARTITION and my-own-written-old-targetted version.
Let’s take a look how does the syntax looks like in SQL Server 2016 and later:
-- Syntax for SQL Server and Azure SQL Database TRUNCATE TABLE [ { database_name .[ schema_name ] . | schema_name . } ] table_name [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ) ] [ ; ] <range> ::= <partition_number_expression> TO <partition_number_expression>
So, let prepare one example with data:
CREATE PARTITION FUNCTION pfQuarter(INT) AS RANGE RIGHT FOR VALUES (201701, 201702, 201703, 201704) GO CREATE PARTITION SCHEME psByQuarter AS PARTITION pfQuarter ALL TO ( [PRIMARY] ) GO CREATE TABLE [dbo].[BigOrderTable]( [ID] BIGINT NOT NULL, [OrderNumber] VARCHAR(30) NOT NULL, [OrderDate] DATETIME NOT NULL, [QuarterNum] AS (datepart(year,[OrderDate])*(100)+datepart(quarter,[OrderDate])) PERSISTED, CONSTRAINT [PK_dbo_BigOrderTable] PRIMARY KEY CLUSTERED ([ID] ASC, [QuarterNum] ASC), ) ON psByQuarter ([QuarterNum]) GO --Let’s fill the [BigOrderTable] table with a few rows: INSERT [dbo].[BigOrderTable] ([ID], [OrderNumber], [OrderDate], [Notes]) SELECT message_id * 10000 + language_id as [ID] , 'ORD/' + CAST(message_id AS varchar(20)) + '/' + CAST(language_id AS varchar(20)) as [OrderNumber] , DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '20170101') as [OrderDate] , LEFT([text], 500) as [Notes] FROM sys.messages;
Hence, a simple example would look like below:
--Show the entire table SELECT * FROM [dbo].[BigOrderTable] GO --Show me how many rows are per partition SELECT partition_number, rows FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='BigOrderTable'; GO --You can truncate one partition... TRUNCATE TABLE [dbo].[BigOrderTable] WITH (PARTITIONS (2)); GO --...or range of partitions TRUNCATE TABLE [dbo].[BigOrderTable] WITH (PARTITIONS (2 TO 3)); GO
Table is nothing but a set of one partition, so you can use above operation for tables as well.
Fine. But this feature is available in 2016. And now is the year 2017. It’s a quite new feature, and believe me – some companies still use 2005 (yes, that not supported one) or even 2000. Should say at the moment: god, bless America. Eeeee… no. Better: god, bless DBA’s who have to use those versions.
So, what when we need or want to use such trick like truncate but for selected partition?
CASE: Truncate table with SQL Server before 2016
Let’s do some simple (but not trivial) trick.
Do create the same table as our target as a distinct object. I suggest using separate schema with the same table name:
--Create table in 'part' schema CREATE SCHEMA [part] GO --Create clone of the table CREATE TABLE [part].[BigOrderTable] ( [ID] BIGINT NOT NULL, [OrderNumber] VARCHAR(30) NOT NULL, [OrderDate] DATETIME NOT NULL, [QuarterNum] AS (DATEPART(YEAR,[OrderDate])*(100)+DATEPART(QUARTER,[OrderDate])) PERSISTED, [Notes] CHAR(500) NULL, CONSTRAINT [PK_dbo_BigOrderTable] PRIMARY KEY CLUSTERED ([ID] ASC, [QuarterNum] ASC), ) GO
Fine. But, “how can I truncate partition using SQL Server older than 2016 version?” – you would ask. I’ve just revealed that secret in the text above partly and here is a low-budget counterpart of TRUNCATE TABLE WITH PARTITION for servers <2016:
USE DEMO GO ------------------------------------------------ -- PROCEDURE: [TruncatePart_BigOrderTable] ------------------------------------------------ ALTER PROCEDURE [dbo].[TruncatePart_BigOrderTable] @Quarter INT AS --Drop check constraint IF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'CK_BigOrderTable_Quarter' and [type]='C') ALTER TABLE [part].[BigOrderTable] DROP CONSTRAINT CK_BigOrderTable_Quarter --Truncate partition TRUNCATE TABLE [part].[BigOrderTable]; --Switch off the partition from the table DECLARE @PartNo INT; SELECT @PartNo = $PARTITION.pfQuarter (@Quarter); ALTER TABLE [dbo].[BigOrderTable] SWITCH PARTITION @PartNo TO [part].[BigOrderTable]; --Truncate partition TRUNCATE TABLE [part].[BigOrderTable]; --Add CHECK constraint again DECLARE @ConstraintSQL NVARCHAR(500); SET @ConstraintSQL = N'ALTER TABLE [part].[BigOrderTable] ADD CONSTRAINT CK_BigOrderTable_Quarter CHECK ([QuarterNum] = ' + CAST(@Quarter AS VARCHAR(20)) + ');' EXECUTE sp_executesql @ConstraintSQL; --Switch on the partition into the table ALTER TABLE [part].[BigOrderTable] SWITCH TO [dbo].[BigOrderTable] PARTITION @PartNo; PRINT 'Partition (' + CAST(@PartNo AS VARCHAR(20)) + ') for @Quarter = ' + CAST(@Quarter AS VARCHAR(20)) + ' has been cleaned.' GO
In that case, I have got one stored procedure per partitioned table.
But there is no reason using dynamic SQL and create a generic procedure which would create cloned table automatically when needed.
Let’s test the procedure.
--Truncate selected partition: EXEC [dbo].[TruncatePart_BigOrderTable] @Quarter = 201702 --Check tables SELECT * FROM [dbo].[BigOrderTable] WHERE [QuarterNum] = 201702; SELECT partition_number, rows FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID) = 'BigOrderTable';
That does work!
Very efficiently and works for the older version of SQL Server as well. Great.
Let me know if that helps you in any way or tell me about your experiences in similar circumstances.
About author
You might also like
DevOps workshop during the SQLDay 2018 – SQLPlayer Team
Good news! We (Kamil and I ) are going to have a full day workshop during the upcoming SQLDay 2018 in Wrocław. The workshop is dedicated to DevOps in SQL Server.
CSV reader from SDU Tools
Greg Low from SQL Down Under released a new version of his tool called “SDU Tools”. The tool contains many interesting and useful functions and stored procedures you can use
sp_send_dbmail fails with query result attached as file
Sometimes a message coming from SQL Server engine can be misleading and confusing. I had such cases many times and decided to put a post every time I meet it
1 Comment
Partition switching w SQL Server - Seequality
November 02, 00:11[…] które dokonają dokładnie tego samego (jeden z nich został opisany przez Kamila Nowińskiego tutaj). Przypuśćmy, że chcemy teraz przełączyć partycję z danymi za rok 2016 z tabeli Source do […]