Fast delete using dynamic partition switching in T-SQL

It is impossible to drop a specific partition, from last month for example. Also, as one cant place a where clause on a truncate statement, a delete from table where x=y would be logged and hence very slow. However, one can switch data from a specific partition into a another table and then truncate that table.

The following is a dynamic method of doing this for any table. This sp must be created first as it is called by another sp that does the work. It creates a table with the same schema as the table from which the partition is to be deleted. The data to be deleted is switched into the created table. Note: the switch out table must be permanent (unfortunately you cannot switch to a #tmp table) so one must check for schema locks.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Harry McCarney
-- Object: mnt_GetSQLForSwitchOutTable
-- =============================================
ALTER PROCEDURE [dbo].[mnt_GetSQLForSwitchOutTable] 
(
    @TableName SYSNAME,
    @NewTableName SYSNAME = NULL,
    @Output varchar(max) output
)
AS
BEGIN

    DECLARE @MainDefinition TABLE
    (
        FieldValue VARCHAR(200)
    )

    DECLARE @DBName SYSNAME
    DECLARE @ClusteredPK BIT
    DECLARE @TableSchema NVARCHAR(255)
    SET @DBName = DB_NAME(DB_ID())

    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

    DECLARE @ShowFields TABLE
    (
        FieldID INT IDENTITY(1,1),
        DatabaseName VARCHAR(100),
        TableOwner VARCHAR(100),
        TableName VARCHAR(100),
        FieldName VARCHAR(100),
        ColumnPosition INT,
        ColumnDefaultValue VARCHAR(100),
        ColumnDefaultName VARCHAR(100),
        IsNullable BIT,
        DataType VARCHAR(100),
        MaxLength INT,
        NumericPrecision INT,
        NumericScale INT,
        DomainName VARCHAR(100),
        FieldListingName VARCHAR(110),
        FieldDefinition CHAR(1),
        IdentityColumn BIT,
        IdentitySeed INT,
        IdentityIncrement INT,
        IsCharColumn BIT
    )

    DECLARE @HoldingArea TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000),
        FldValue CHAR(1) DEFAULT(0)
    )

    DECLARE @PKObjectID TABLE
    (
        ObjectID INT
    )

    DECLARE @Uniques TABLE
    (
        ObjectID INT
    )

    DECLARE @HoldingAreaValues TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000),
        FldValue CHAR(1) DEFAULT(0)
    )

    DECLARE @Definition TABLE
    (
        DefinitionID SMALLINT IDENTITY(1,1),
        FieldValue VARCHAR(200)
    )


    INSERT INTO @ShowFields
    (       
        DatabaseName,
        TableOwner,
        TableName,
        FieldName,
        ColumnPosition,
        ColumnDefaultValue,
        ColumnDefaultName,
        IsNullable,
        DataType,
        MaxLength,
        NumericPrecision,
        NumericScale,
        DomainName,
        FieldListingName,
        FieldDefinition,
        IdentityColumn,
        IdentitySeed,
        IdentityIncrement,
        IsCharColumn
    )

    SELECT
        DB_NAME(),
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        CAST(ORDINAL_POSITION AS INT),
        COLUMN_DEFAULT,
        dobj.name AS ColumnDefaultName,
        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
        DATA_TYPE,
        CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
        CAST(NUMERIC_PRECISION AS INT),
        CAST(NUMERIC_SCALE AS INT),
        DOMAIN_NAME,
        COLUMN_NAME + ',',
        '' AS FieldDefinition,
        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
        CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
        CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
        CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
    FROM
        INFORMATION_SCHEMA.COLUMNS c
        JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
        LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
        JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name

        LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'


    WHERE c.TABLE_NAME = @TableName
    ORDER BY
        c.TABLE_NAME, c.ORDINAL_POSITION

    SELECT TOP 1 @TableSchema = TableOwner
    FROM @ShowFields

    INSERT INTO @HoldingArea (Flds) VALUES('(')

    INSERT INTO @Definition(FieldValue)
    VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)

    INSERT INTO @Definition(FieldValue)
    VALUES('(')

    INSERT INTO @Definition(FieldValue)
        SELECT
                CHAR(10) + FieldName + ' ' +
                                    CASE
                                        WHEN DomainName IS NOT NULL  THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                        ELSE UPPER(DataType) +
                                            CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
                                            CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
                                            CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
    										 CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END end
        FROM    @ShowFields 

INSERT INTO @MainDefinition(FieldValue)
            SELECT FieldValue FROM @Definition
            ORDER BY DefinitionID ASC


        SELECT @output  = isnull(@output,'') + FieldValue FROM @MainDefinition


END

The next sp calls the sp above and does the deleting. It removes data from partitions older than the number of days spcified in the input paramter. This is much faster than querying the tables and issuing deletes. Using switch and truncate we take no locks and perform no scans/seeks. Specific partitions cant be explicitly dropped/truncated.

We switch data into a difffernet table: Table_SwitchOut and truncate it from there. Switch statements require DDL and we may want to switch multiple partitions This means dynamically running DDL alter table statements which is perhaps not ideal. I have also created dynamic structure in case job fails one night or we only want to run it once a a week.

Also if we stop collecting data it will keep the last @DaysPartitionsToKeep days collected data. The sp checks that the partitions are actually weekly or monthly by querying system tables. This double check ensures we don’t delete data erroneously if the partition function is altered.

-- =============================================
-- Author:      Harry McCarney
-- Object: mnt_Truncate_Data_From_Partitions

-- =============================================

ALTER PROC [dbo].[mnt_Truncate_Data_From_Partitions] 
@Table_Name varchar (100),
@DaysPartitionsToKeep INT ---Number of days data to keep aassuming daily partition. 
AS

SET NOCOUNT ON
SET transaction ISOLATION level read uncommitted

DECLARE @no       INT,
        @i        INT,
        @sql      VARCHAR(MAX),
        @truncate VARCHAR(500),
    	@Table_Name_SwitchOut varchar(100),
    	@now BIGINT,
    	@PartionCheck INT,
    	@PartionScheme varchar(100),
    	@PartitionsToKeep INT,
    	@PartitionFunction varchar(100),
    	@Error varchar(1000),
    	@CreateSwitchOutTableCMD varchar(max),
    	@DropExistingTableCMD varchar(max)

DECLARE @tab TABLE (ID INT identity, PartitionID INT)

IF (select object_ID(@Table_Name)) is null
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Table '+@Table_Name+' Not Found.'
RAISERROR(@Error ,10,1,'')with log
RETURN 
END

SELECT 
@Table_Name_SwitchOut = @Table_Name+'_SwitchOut', --All switch out Table have this format.
@truncate = 'truncate table dbo.'+@Table_Name_SwitchOut,-- Create dynamic truncate as must be run after each switch. Switch out table must be empty before switch.
@now = dbo.datetime2uts_id(getdate()), @DaysPartitionsToKeep = 14, ---Get Id for Current Date
@DropExistingTableCMD = 'if (select object_id('''+@Table_Name_SwitchOut+''')) is not null drop table '+ @Table_Name_SwitchOut

exec mnt_GetSQLForSwitchOutTable @table_name, @Table_Name_SwitchOut,@CreateSwitchOutTableCMD output
exec (@DropExistingTableCMD)

print @CreateSwitchOutTableCMD
exec (@CreateSwitchOutTableCMD)


IF (select object_ID(@Table_Name_SwitchOut)) is null
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Table '+@Table_Name_SwitchOut+' Not Created.'
RAISERROR(@Error,10,1,'')with log
RETURN 
END

SELECT 
@PartionScheme = ps.[name],
@PartitionFunction = pf.[name]
FROM sys.partitions p
JOIN sys.indexes i
   ON p.[object_id] = i.[object_id]
   AND p.index_id = i.index_id
JOIN sys.data_spaces ds
   ON i.data_space_id = ds.data_space_id
JOIN sys.partition_schemes ps
 ON ds.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf
   ON ps.function_id = pf.function_id
WHERE p.[object_id] = OBJECT_ID(@Table_Name)

SELECT @PartitionsToKeep = CASE @PartionScheme
    WHEN   'PS_daily' THEN @DaysPartitionsToKeep 
    WHEN 'PS_weekly' THEN @DaysPartitionsToKeep/7
    ELSE -1 END

IF @PartitionsToKeep = -1
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Unknown Partition Scheme '+@PartionScheme +' Not Found.'
RAISERROR(@Error,10,1,'')with log
RETURN 
END

---A little check to ensure the partioning function ranges hasnt changed, for example ,from daily to 
--weekly. 

SELECT @PartionCheck = DATEDIFF(d,MIN(dbo.uts_id2datetime([value],1)), 
MAX(dbo.uts_id2datetime([value],1)))+1
FROM(
SELECT TOP (@PartitionsToKeep) CAST([value] as BIGINT)[value]
FROM  sys.partition_range_values  rv
JOIN sys.partition_functions f
    ON rv.function_id = f.function_id
WHERE name = @PartitionFunction
AND [value] < @now
)x


IF ISNULL(@PartionCheck,9999)  > @DaysPartitionsToKeep 
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Partition function is inconsistent and a truncate would only keep '+
    													cast(ISNULL(@PartionCheck,0)as varchar(10))+' days data.'
RAISERROR(@Error ,10,1,'')
RETURN 
END

---Now we do the work
INSERT @tab
SELECT distinct Partition_Number -- partition numbers for delete
FROM   sys.partitions
WHERE  [OBJECT_ID]= OBJECT_ID(@Table_Name)
   AND Partition_Number + @PartitionsToKeep <=
       (SELECT MAX(Partition_Number) --Latest partition with rows.
       FROM    sys.partitions
       WHERE   [OBJECT_ID] = OBJECT_ID(@Table_Name)
       AND [Rows]    > 0
       )
and   [Rows]    > 0



SET @no   = SCOPE_IDENTITY()
SET @i    =1
WHILE @i <= @no -- Loop through partition number generating and excuting alter and truncate.
BEGIN

        SELECT @sql = 'ALTER TABLE '+@Table_Name+'  SWITCH PARTITION '+ CAST(PartitionID AS VARCHAR(10))+ ' TO '+@Table_Name_SwitchOut 
        FROM   @tab
        WHERE  ID = @i
    	EXEC (@sql) --Alter table switch
       EXEC (@truncate) -- Truncate switch table
    	print @sql
    	print @truncate
SET @i = @i + 1
END

exec('drop table '+@Table_Name_SwitchOut)