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.