Does anyone have or know of stored procedure that will do generic temporal table manipulation so that history and main table are both truncated?

You have to alter it to set system_versioning = off, then drop the history table, then truncate your main table, then re-enable versioning on your main table. I was going to build a generic stored procedure to do this for any database/schema/table name passed to it but sure be nice if someone already had one. I never like re-inventing the wheel if I can avoid it.

  • You don't need to drop the history table. Once they are unlinked you can just truncate it. Then give the same name when reenabling the temporal table functionality. So you preserve any customised indexes or file group placement etc – Martin Smith Jan 30 '18 at 21:24
  • MS docs has an example SP to clean temporal tables that would be a good starting point. Just add additional code to clear the parent table as well. – LowlyDBA - John M Jan 30 '18 at 21:27
  • Dang...that was too easy....I'll tell my manager it took weeks to figure out ;-) Thanks Martin and LowlyDBA!!!! – Saxman Jan 30 '18 at 21:42
  • I have a few solutions here for 2016; if you can move to 2017, there's a setting. – Aaron Bertrand Jan 30 '18 at 23:51

MSSQL documentation has a good sample stored procedure (though far from perfect, as Martin noted in the comments about how it does not use QUOTENAME, for one) for cleaning a temporal table:

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;  

CREATE PROCEDURE sp_CleanupHistoryData  
         @temporalTableSchema sysname  
       , @temporalTableName sysname  
       , @cleanupOlderThanDate datetime2  
    DECLARE @disableVersioningScript nvarchar(max) = '';  
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';  
    DECLARE @enableVersioningScript nvarchar(max) = '';  

DECLARE @historyTableName sysname    
DECLARE @historyTableSchema sysname    
DECLARE @periodColumnName sysname    

/*Generate script to discover history table name and end of period column for given temporal table name*/  
EXECUTE sp_executesql   
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name  
        FROM sys.tables t1   
           JOIN sys.tables t2 on t1.history_table_id = t2.object_id  
        JOIN sys.schemas s on t2.schema_id = s.schema_id  
            JOIN sys.periods p on p.object_id = t1.object_id  
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id  
                 t1.name = @tblName and s.name = @schName'  
                , N'@tblName sysname  
                , @schName sysname  
                , @hst_tbl_nm sysname OUTPUT  
                , @hst_sch_nm sysname OUTPUT  
                , @period_col_nm sysname OUTPUT'  
                , @tblName = @temporalTableName  
                , @schName = @temporalTableSchema  
                , @hst_tbl_nm = @historyTableName OUTPUT  
                , @hst_sch_nm = @historyTableSchema OUTPUT  
                , @period_col_nm = @periodColumnName OUTPUT   

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL  
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1  

/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */  
SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'  
SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']   
     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''   
SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']   
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '   

    EXEC (@disableVersioningScript);  
    EXEC (@deleteHistoryDataScript);  
    EXEC (@enableVersioningScript);  

Just add a few extra lines to handle removing data from the parent table and you should be in business.

Bonus: it uses sysname for variables which I am a huge proponent of but is generally an overlooked datatype!

  • 2
    It should also use QUOTENAME. Also I'm not seeing why the first bit is using sp_executesql though maybe there is some concatenation I'm missing on mobile. – Martin Smith Jan 31 '18 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.