Cannot delete rows from a temporal history table

Question

I've recently discovered temporal tables in SQL Server. I'd like to start using this functionality. However the biggest hurdle is not being able to delete records from it. Due to GDPR compliance this is an absolute must.

Deleting records from a history table obviously leads to the error:

Cannot delete rows from a temporal history table

So to be able to delete records from a history table I have to disable SYSTEM_VERSIONING, then delete, and then re-enable SYSTEM_VERSIONING. Unless there's another way I'm not aware of?

Since it's not possible to use GO's in a stored procedure/SqlCommand, how can I ensure deleting a history record does not mess with other transactions e.g. updates sent to the temporal table during deleting records from the history table will still result in records being added to the history table?

I've tried creating a stored procedure to wrap it in one transaction but this fails because the ALTER TABLE statement disabling the SYSTEM_VERSIONING is not executed yet leading to the same error.

CREATE PROCEDURE [dbo].[OrderHistoryDelete]
     (@Id UNIQUEIDENTIFIER)
AS
BEGIN
    BEGIN TRANSACTION

    ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF )
    -- No GO possible here obviously.

    DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) 
    WHERE [Id] = @Id

    ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[OrderHistory]))

    COMMIT TRANSACTION
END
GO

Solution 1

If you make the DELETE dynamic, your stored procedure will successfully ALTER the table, DELETE the records in question, and then ALTER it back.

CREATE PROCEDURE [dbo].[OrderHistoryDelete]
     (@Id UNIQUEIDENTIFIER)
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)

    BEGIN TRANSACTION

        ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF )

        SET @sql = 'DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) 
        WHERE [Id] = ''' + CAST(@Id AS VARCHAR(40)) + ''''
        EXEC (@sql)

        ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[OrderHistory]))

    COMMIT TRANSACTION
END

Solution 2

I have never have issues like this, but my stored procedure is a little bit different - I am getting CSV of records to be deleted, then STRING_SPLIT them and materialized in a temporary table. Then, this table is joined to the target history table.

Here is full working example (only one input value). First create the table and add some sample data:

DROP TABLE IF EXISTS [dbo].[StackOverflow];
GO

CREATE TABLE [dbo].[StackOverflow]
(
    [Col1] INT PRIMARY KEY
   ,[Col2] VARCHAR(32)
   ,[SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL 
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL 
   ,PERIOD FOR SYSTEM_TIME ([SysStartTime],[SysEndTime])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StackOverflowChanges));

GO

INSERT INTO [dbo].[StackOverflow] ([Col1], [Col2])
VALUES (1, 'value 1')
      ,(2, 'value 2')
      ,(3, 'value 3');

GO

UPDATE [dbo].[StackOverflow]
SET [Col2] = [Col2] + ' v2'

GO

SELECT *
FROM [dbo].[StackOverflow];

SELECT *
FROM [dbo].[StackOverflowChanges];

GO

Then, create the stored procedure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[usp_StackOverflow_Delete]
(
    @Col1 INT
)
AS
BEGIN;

    SET NOCOUNT, XACT_ABORT ON;

        DROP TABLE IF EXISTS #RecordsTobeDeleted; 

        CREATE TABLE #RecordsTobeDeleted
        (
            [Col1] INT
        );

        INSERT INTO #RecordsTobeDeleted ([Col1])
        VALUES (@Col1);

        BEGIN TRY

            BEGIN TRANSACTION;

                ALTER TABLE [dbo].[StackOverflow] SET ( SYSTEM_VERSIONING = OFF )

                DELETE [dbo].[StackOverflowChanges]
                FROM [dbo].[StackOverflowChanges] SOC
                INNER JOIN #RecordsTobeDeleted R
                    ON SOC.[Col1] = R.[Col1];

                ALTER TABLE [dbo].[StackOverflow] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[StackOverflowChanges]))
            
            COMMIT TRANSACTION;

        END TRY

        BEGIN CATCH 
  
            IF @@TRANCOUNT > 0
            BEGIN;
                ROLLBACK TRANSACTION;
            END;

            THROW;
            
        END CATCH

    SET NOCOUNT, XACT_ABORT ON;

END;

GO

and now delete a record from the history table:

EXEC  [dbo].[usp_StackOverflow_Delete] @Col1 = 1;
 

SELECT *
FROM [dbo].[StackOverflow];

SELECT *
FROM [dbo].[StackOverflowChanges];

Clean up:

ALTER TABLE [dbo].[StackOverflow] SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE IF EXISTS [dbo].[StackOverflow];
DROP TABLE IF EXISTS [dbo].[StackOverflowChanges];

Comments

[Zohar Peled]

History tables are readonly. You can't execute any DML statements on them. You can mimic versioning by using triggers on the main table, or by using soft deletes (meaning, keeping a bit column that will contain 1 only for active records). I'm not sure how to incorporate DDL statements with DML statements in a single transaction (never tried it). What is happening when you try to execute your current stored procedure?

[Zohar Peled]

GO is not a part of T-SQL. It's a batch separator used by SSMS (and other client tools).

[Vqf5mG96cSTT]

I would advise anyone using this approach to disable the data consistency check by adding DATA_CONSISTENCY_CHECK = OFF, otherwise performance issues might arise.

[HamsterWithPitchfork]

Unfortunately this won't work, if there is an indexed view sitting on top of this temporal table.

[Dai]

You should use sp_executesql instead of EXEC for Dynamic SQL.

[Adam]

@bdebaere are you able to give an example of setting DATA_CONSISTENCY_CHECK = OFF please? I've tried this on the ALTER TABLE ... (WITH DATA_CONSISTENCY_CHECK=OFF) statements for both the main table and the history table and it always shows syntax error. I've trawled online and can find a little info about this setting and its relation to DBCC CHECKCONSTRAINTS but I can't find any example of how to switch this off?

[Vqf5mG96cSTT]

@Adam ALTER TABLE [MyTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory], DATA_CONSISTENCY_CHECK = OFF))