Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Dynamically Truncate a Partitioned Table

By Steven Rao,

Table partitioning is a very useful and advanced database feature. SQL Server has supported table partitioning since SQL Server 2005. However, I am puzzled about why it does not provide us with a built-in T-SQL statement such as
ALTER TABLE partionedTableName 
Oracle provides a similar SQL statement, allowing us to truncate all data with a specific partition from a table.
You can find stored procedures and scripts online which does similar thing. For example, there is an implementation of table partition truncation at: However, I find it is more entertaining to write my own version. This article shows you one way of truncating a table partition by using a stored procedure usp_Util_TruncatePartition.
The basic steps required to truncate a table partition are:
  • Create a compatible clone table for the source table with the same column names and same column data types.
  • Truncate the source table partition by swapping out the content of the source table partition with the empty clone table. This can be achieved by using ALTER TABLE SWITH PARTITION statement. 
  • Drop the clone table
This sounds simple, but there are lot of details that matter. We have some goals and restrictions. 
  • We want to do this with any partitioned table, not just one specific partitioned table. 
  • The clone table has to be located at the same file group as the source table partition to be swapped out (or truncated).
  • The clone table has to have exactly same compression setting with the source table partition
  • The clone table has to have compatible clustered index with the source table partition
  • After truncating, the old data from original partition has to be dropped to make sure the space is freed up properly
  • The clone table and clustered index need to have a dynamic name to avoid name conflicts.
Let's make some assumptions so that our code is easier to understand, and we can quickly outline the key ideas:
  • The partition type is FOR LEFT
  • The partition function parameter data type is integer.
These limitations can be easily removed by some very slight changes with the implementation of the procedure.
First let's review the parameters of the procedure usp_Util_TruncatePartition:
  • @tableName varchar(255) - is the name of the table which is partitioned
  • @partitionValue int - is the partition value at which we want to truncate the data. I found out it is more intuitive to use partition value instead of partition number as parameter. By the way, you can get the partition number of a partition value using $PARTITION.paritionFunction 
  • @schemaName varchar(255)='dbo' - is the schema name of the table, default to 'dbo' of course. 
Assume we have a database named [TEST], the following is the complete code of the procedure usp_Util_TruncatePartition:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp_Util_TruncatePartition]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [usp_Util_TruncatePartition]
-- By Steven Rao
-- Date: June 13, 2013
-- declare @code int
-- A generic procedure to truncate any given partition from a partitioned table
-- Limitation: cannot deal with some data types, such as xml
-- Basic assumption and current limitation: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition parameter has integer type
-- Change history
-- Date Description
-- 2013-07-26 Fix schema name and computed column issues
-- It is now also working both left and right partition type
CREATE PROCEDURE [usp_Util_TruncatePartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
-- Is table partitioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
-- find the partition number for the partion value and other information for table partition 
-- partition FOR RIGHT and boundary_id begins with id 1
,@partitionNumber=case when pf.boundary_value_on_right=1 then rv.boundary_id+1 else rv.boundary_id end
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s 
INNER JOIN sys.tables t 
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN  sys.indexes i 
ON (i.object_id = t.object_id 
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN  sys.index_columns  ic 
ON (-- identify partioned column
ic.partition_ordinal > 0 
and ic.index_id = i.index_id 
and ic.object_id = t.object_id)
INNER JOIN  sys.columns c 
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id  
WHERE -- only look for heap or clustered index 
i.index_id IN (0, 1)  
-- need exact match
AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.' 
return 100
end else
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType 
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16)) 
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
-- create clone table on same file group, drop it if already exists
-- we don't need to take care of FK and no clustered for our case
-- No clustered index is not needed
-- taking care of columns and primary key
-- Ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000),
        @createCloneTable varchar(8000), 
        @primaryKeyName varchar(255), 
        @dropPrimaryKey varchar(8000), 
        @primayKeyNamePlaceHolder varchar(255), 
        @hasPrimaryKey tinyint, 
        @switchPartition varchar(1024)
-- A place holder for primary key which will be replace when the value is known
 ,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
 ,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
 +'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13) +'END'
 ,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
 +'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
 ,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
  @hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
 ,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
 ,@createCloneTable= 'CREATE TABLE ' +@schemaName+'.'+@CloneTableName
  + '(' + o.list + ')'
  + ' ON ['+@partitionFileGroupName+']'
  + CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
  +'ALTER TABLE ' +@schemaName+'.'+ @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
  + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
 from sys.schemas s 
  inner join sys.objects so 
   on s.schema_id=so.schema_id 
  cross apply
' ['+column_name+'] '
-- deal with computed columns
+ case when cc.is_computed=1 then ' AS ('+cc.definition+')'+case when cc.is_persisted=1 then ' PERSISTED' else '' end + ', ' else
-- non computed columns case
case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
 select id 
  from syscolumns
  where object_name(id)='TestTruncatePartition'
  and name=column_name
  and columnproperty(id,name,'IsIdentity') = 1
 then 'IDENTITY(' + cast(ident_seed('TestTruncatePartition') as varchar) + ',' + cast(ident_incr('TestTruncatePartition') as varchar) + ')'
 else '' end
+ ' '
+ (case when c.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
from information_schema.columns c left join sys.computed_columns cc on
OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)=cc.object_id and
where c.table_name = AND c.TABLE_SCHEMA=@schemaName
 order by ordinal_position
  ) o (list)
 left join information_schema.table_constraints tc
  on tc.Table_name = so.Name
  AND tc.Constraint_Type = 'PRIMARY KEY'
  AND tc.TABLE_SCHEMA=@schemaName
 cross apply
  (select '[' + Column_Name + '], '
   FROM information_schema.key_column_usage kcu 
   WHERE kcu.Constraint_Name = tc.Constraint_Name and kcu.TABLE_SCHEMA=@schemaName
   ) j (list)
  -- find out if the primary key is clustered
  left join sys.indexes i
   on so.object_id=i.object_id
   -- index_id 1 means clustered
   -- constraint name is primary key name which is same as the index name
   AND i.index_id=1 AND and tc.TABLE_SCHEMA=@schemaName 
and so.type = 'U'
and NOT IN ('dtproperties')
select @error=@@ERROR,
       @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
  select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
-- 1: Row compression
-- 2: Page compression
if @compression=1
  set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
else if @compression=2
  set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
-- taking care of clustered index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
-- we may not need to drop the index? just in case
declare @indexColums varchar(256),
        @indexName varchar(256), 
        @indexUnique varchar(8), 
        @hasNonPrimaryKeyClusterdIndex tinyint, 
        @createClusteredIndex varchar(8000), 
        @dropClusteredIndex varchar(8000)
select @indexColums='(',
-- whenever we have result from here, we do have clusterd index
 ,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
 from sys.schemas s
  INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
  INNER JOIN sys.indexes i
   ON (i.object_id = t.object_id
   -- looking for clustered index ONLY
   and i.index_id=1
   -- ignore primary key, which is taken care of above already
   and i.is_primary_key=0)
    select when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
     from sys.index_columns ic
      INNER JOIN sys.columns c
       ON c.column_id=ic.column_id 
      and c.object_id=ic.object_id
     where ic.index_id = i.index_id 
       and ic.object_id = t.object_id
     order by ic.key_ordinal
    FOR XML PATH('')
    ) ic(columnList)
select @error=@@ERROR,
       @LogHeader=convert(char(23), @Now, 126),
       @hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
  select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
  +'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
  ,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
if (@hasPrimaryKey=1)
  select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
  if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
  select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
  if (@error<>0) goto ERROR
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
 if (@hasNonPrimaryKeyClusterdIndex=1)
   select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
   if (@error<>0) goto ERROR
if (@compression IN (1,2))
  select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
  if (@error<>0) goto ERROR
-- now do the partition switch to truncate data
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
-- now drop the coloum table again
if (@hasPrimaryKey=1)
  select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
  if (@error<>0) goto ERROR
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
print @errMsg
return @returnCode
Now let's go over some of the key steps in the implementation detail. First, we find out the table partition information and the partition number of the partition value. These values are needed to generate the dynamic SQL statement used later.
sys.index_columns.partition_ordinal is one key field that is used to see if the corresponding table is partitioned. 
sys.partition_range_values.boundary_id can be used to match @partitionValue provided. Also note that we need only to look at sys.indexes where index_id is 0 or 1 since we don't need any information from non-clustered index(es).
We should abort the procedure if the table is not partitioned or the partition value is not in the current partition range list of the table.
Next we find out all the column names and data types of the partition table. We also need to find out if the partition table has a primary key. If it does, we need to find out the primary key name and all the columns of the primary key. An important detail here is that we need to find out if the index based on the primary key is clustered.
Then we need to find out the compression setting of the partition to be truncated and make sure the clone table has the same compression setting.
Last  we need to find out if the table has a clustered index that was not automatically created from the primary key. If it does, we find out the details (including index name and column name, descending or ascending order ) of this index. It is important to note that we can only have zero or one clustered key for each table. The combination of cross apply and FOR XML is used to generate the column list string. We may just check clustered key and ignore the primary key, but it is nice to make sure they have same primary key if the original table has a primary key.
Once we have all this information, we create dynamic query statements to:
  • create a clone table with a primary key (if needed)
  • create a clustered index apart from the primary key (if needed)
  • set the correct compression option on the clone table
  • switch the partition to truncate the table from the table partition
  • drop the clone table which contains the original data from the table partition.
To make the code more robust we drop the clone table before creating it.
Let's see how usp_Util_TruncatePartition can be used to quickly truncate a data from a partitioned table TestTruncatePartition. We assume the data is partitioned with 4 ranges:
  • partition number 1: value<=100
  • partition number 2: value>100 and value<=200
  • partition number 3: value>200 and value<=300
  • partition number 4: value>300

Here is the code to set up this table

CREATE PARTITION FUNCTION [pf_TestTruncatePartition](int)
  RANGE LEFT FOR VALUES (100, 200, 300)
CREATE PARTITION SCHEME [ps_TestTruncatePartition] AS PARTITION [pf_TestTruncatePartition] 
CREATE TABLE [dbo].[TestTruncatePartition](
 [PartitionParameter] [int] NOT NULL,
 [RowID] [int] NOT NULL,
 [RowValue] [varchar](50) NULL,
 [PartitionParameter] ASC,
 [RowID] ASC
 ) ON [ps_TestTruncatePartition]([PartitionParameter])
 ) ON [ps_TestTruncatePartition]([PartitionParameter])
Next we insert 1000 rows into partition number 2 and verify the number of rows inserted:
insert into TestTruncatePartition
  ([PartitionParameter], [RowID], [RowValue])
    200 as [PartitionParameter]
   ,number as RowID
   ,CAST(number as varchar(16)) as RowValue 
  from master..spt_values 
  where type='P' 
  and number between 1 and 1000
SELECT COUNT(*) AS [RowCountBeforeTruncateTablePartition] 
 FROM TestTruncatePartition 
 WHERE [PartitionParameter]>100
 AND [PartitionParameter]<=200
We see the results of this below:
Now let's use usp_Util_TruncatePartition to truncate partition number 2 of the table TestTruncatePartition:
DECLARE @ReturnCode int

EXECUTE @ReturnCode = [TEST].[dbo].[usp_Util_TruncatePartition] 
SELECT @ReturnCode AS ReturnCode
SELECT COUNT(*) AS [RowCountAfterTruncateTablePartition] 
FROM TestTruncatePartition 
WHERE [PartitionParameter]>100
AND [PartitionParameter]<=200
Here are the results
In summary, if you have a partitioned table, this article provides you with a ready to use stored procedure usp_Util_TruncatePartition, which can dynamically and efficiently truncate any partition of that table. This is very useful when you have many tables with large partitions of data, and you want to get rid of this data quickly. 


TableIsParitioned.jpg | usp_Util_TruncatePartitionSC.sql | usp_Util_TruncatePartitionSC_Test.sql
Total article views: 3928 | Views in the last 30 days: 20
Related Articles

Primary key - Integer? or Varchar?

Integer / Varchar, which one is best as primary key


primary key violation error

primary key violation error


Partition key should be part of Primary key

Partition key should be part of Primary key


Error turning Varchar into Numeric

Error turning Varchar into Numeric


Varchar column as partitioning key

How is the range determined when using a varchar column as the partitioning key


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones