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 TRUNCATE PARTITION p
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: http://www.sqlservercentral.com/scripts/Truncate+Table/69506. 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:
USE TEST
GO
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]
GO
--
-- By Steven Rao
-- Date: June 13, 2013
-- StevenZRAO@gmail.com
-- 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'
AS
BEGIN
--
-- 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
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
-- 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
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- 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)
AND s.name=@schemaName
AND t.name=@tableName
-- 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)
begin
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
begin
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))
end
--
-- 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)
select
-- A place holder for primary key which will be replace when the value is known
@primayKeyNamePlaceHolder='$$PK_NAME$$'
,@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
select
@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 '
+ CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
+ ' (' + 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
(SELECT
' ['+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
data_type+
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 '
+ case when c.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ c.COLUMN_DEFAULT ELSE '' END + ', '
end
from information_schema.columns c left join sys.computed_columns cc on
OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)=cc.object_id and c.COLUMN_NAME=cc.name
where c.table_name = so.name AND c.TABLE_SCHEMA=@schemaName
order by ordinal_position
FOR XML PATH('')
) 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
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) 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 tc.CONSTRAINT_NAME=i.name and tc.TABLE_SCHEMA=@schemaName
where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end
-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end
else if @compression=2
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- 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='(',
@indexUnique='',
@indexName='',
@hasNonPrimaryKeyClusterdIndex=0,
@dropClusteredIndex='',
@createClusteredIndex=''
select
-- whenever we have result from here, we do have clusterd index
@hasNonPrimaryKeyClusterdIndex=1
,@indexColums=@indexColums+ic.columnList
,@indexName=@cloneTableName+'_'+i.name
,@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)
CROSS APPLY
(
select
c.name+case 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)
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126),
@hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
+char(13)+'BEGIN'+char(13)
+'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
+char(13)+'END'
,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
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)
begin
EXEC(@dropPrimaryKey)
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
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
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
end
EXEC(@dropCloneTable)
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
EXEC(@createCloneTable)
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)
begin
EXEC(@createClusteredIndex)
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
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
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
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
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)
begin
EXEC(@dropPrimaryKey)
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
end
--
-- 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)
--begin
--EXEC(@dropClusteredIndex)
--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
--end
EXEC(@dropCloneTable)
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
RETURN 0
ERROR:
print @errMsg
return @returnCode
END
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) AS RANGE LEFT FOR VALUES (100, 200, 300) GO CREATE PARTITION SCHEME [ps_TestTruncatePartition] AS PARTITION [pf_TestTruncatePartition] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) GO CREATE TABLE [dbo].[TestTruncatePartition]( [PartitionParameter] [int] NOT NULL, [RowID] [int] NOT NULL, [RowValue] [varchar](50) NULL, CONSTRAINT [PK_TestTruncatePartition] PRIMARY KEY CLUSTERED ( [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])
(
select
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
)
GO
SELECT COUNT(*) AS [RowCountBeforeTruncateTablePartition]
FROM TestTruncatePartition
WHERE [PartitionParameter]>100
AND [PartitionParameter]<=200
GO 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] @tableName='TestTruncatePartition' ,@partitionValue=200 ,@schemaName='dbo' SELECT @ReturnCode AS ReturnCode GO SELECT COUNT(*) AS [RowCountAfterTruncateTablePartition] FROM TestTruncatePartition WHERE [PartitionParameter]>100 AND [PartitionParameter]<=200 GO
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.
