Drop a column from a temporal table

Question

I'm trying to drop a column from a temporal table but still keep the data in the history.
Simply using alter drop will delete the column from the history table as well so it won't do.

I've tried to set the system_versioning = off, drop it and then return it to be on but its creating a new history table and all the previes history is gone (I can't define the old history table to be the current because it contained another column - the one that I dropped). Is there a way to do it?

Answer 1

As I mentioned in the comments the two tables (the "live" and history table) must have the same definitions. If you DROP a column from the "live" table it is DROPed from the history table.

As such, if you want to retain that data don't DROP it. Instead UPDATE all the values to NULL, which might need an ALTER on the column to make it NULLable (and you may need to drop some CONSTRAINTs). Then you just omit the column in your workflows going forwards, unless you need it when querying the history table:

USE Sandbox;
GO
--Create history schema if needed.
--Note, I do not clean this up at the end.
IF NOT EXISTS (SELECT 1
               FROM sys.schemas 
               WHERE name = N'history')
    EXEC sys.sp_executesql N'CREATE SCHEMA history;';
GO
--Create sample table with history table
CREATE TABLE dbo.YourTable (YourID int IDENTITY(1,1) CONSTRAINT PK_YourPK PRIMARY KEY,
                            SomeDate date NOT NULL,
                            ToBeDropped varchar(10) NOT NULL,
                            SomeOtherValue varchar(20) NULL,
                            ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
                            ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
                            PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.YourTable));
GO
--SAmple data
INSERT INTO dbo.YourTable (SomeDate,
                           ToBeDropped,
                           SomeOtherValue)
VALUES(GETDATE(),'abc123','asdvaksd'),
      (GETDATE()-1,'def123','sdfgdfgfg'),
      (GETDATE()-2,'xyz456','assdvaksdfgsd'),
      (GETDATE()+1,'abc789','fgfghgfjgfjgfh');
GO
--Check data
SELECT YourID,
       SomeDate,
       ToBeDropped,
       SomeOtherValue
FROM dbo.YourTable;

GO
--Make some changes
UPDATE dbo.YourTable
SET ToBeDropped = 'qwe654'
WHERE YourID = 2;

UPDATE dbo.YourTable
SET ToBeDropped = 'asd456',
     SomeDate = GETDATE() + 3
WHERE YourID = 3;
GO
--Check data again
SELECT YourID,
       SomeDate,
       ToBeDropped,
       SomeOtherValue
FROM dbo.YourTable;

SELECT YourID,
       SomeDate,
       ToBeDropped,
       SomeOtherValue,
       ValidFrom,
       ValidTo
FROM history.YourTable

GO
--Change column to NULLable (if needed)
ALTER TABLE dbo.YourTable ALTER COLUMN ToBeDropped varchar(10) NULL;
GO
--UPDATE all rows to NULL
UPDATE dbo.YourTable
SET ToBeDropped = NULL;
/*
If you have a DEFAULT constraint on the column in the table, you'll need to
DROP that CONSTRAINT as well to ensure that they have the value NULL in
future INSERT statements.
If you have any CHECK CONSTRAINTS that reference the column too, you'll likely
need to ALTER/DROP those as well.
*/
GO
--Add a new row, old column now omitted
INSERT INTO dbo.YourTable (SomeDate,
                           SomeOtherValue)
VALUES(GETDATE()+7,'asdkjb65'); --Only 2 columns
GO
--SELECT, with column omited
SELECT YourID,
       SomeDate,
       --ToBeDropped,
       SomeOtherValue
FROM dbo.YourTable;
GO
--History with column included
SELECT YourID,
       SomeDate,
       ToBeDropped, --Can still be returned
       SomeOtherValue,
       ValidFrom,
       ValidTo
FROM history.YourTable;
GO
--Clean up
ALTER TABLE dbo.YourTable SET (SYSTEM_VERSIONING = OFF);

DROP TABLE history.YourTable;
DROP TABLE dbo.YourTable;
GO