SQL Server System Versioned Table Scripts

Hello,

Here's some of my compiled T-SQL scripts to convert ordinary tables to System Versioned and data manipulation scripts. See below the scripts using ficticious tables and columns.

How To Convert An Ordinary Table To A System Versioned Table

Use TEST_DB
GO 
ALTER TABLE dbo.tblFileFolders
ADD
    StartTime DATETIME2 GENERATED ALWAYS AS ROW START
        CONSTRAINT DF_tblFileFolders_StartTime DEFAULT SYSUTCDATETIME(),

    EndTime DATETIME2 GENERATED ALWAYS AS ROW END
        CONSTRAINT DF_tblFileFolders_EndTime DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

    PERIOD FOR SYSTEM_TIME (StartTime, EndTime);

ALTER TABLE dbo.tblFileFolders
SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.tblFileFoldersHistory
    )
);

How To A Drop System Versioned Table

Use TEST_DB
GO 

-- 1. Disable system versioning
ALTER TABLE [dbo].[tblFileFolders] SET (SYSTEM_VERSIONING = OFF);
GO

-- 2. Drop the main (current) table
DROP TABLE [dbo].[tblFileFolders];
GO

-- 3. Optionally, drop the associated history table
DROP TABLE [dbo].[tblFileFoldersHistory];
GO

-- 1. Disable system versioning
ALTER TABLE [dbo].[tblFiles] SET (SYSTEM_VERSIONING = OFF);
GO

-- 2. Drop the main (current) table
DROP TABLE [dbo].[tblFiles];
GO

-- 3. Optionally, drop the associated history table
DROP TABLE [dbo].[tblFilesHistory];
GO

How To Rename a System Versioned Table Column

Use TEST_DB
GO 
ALTER TABLE dbo.tblFiles
SET (SYSTEM_VERSIONING = OFF);

EXEC sp_rename 'dbo.tblFiles.SizeMb', 'SizeKb', 'COLUMN';
EXEC sp_rename 'dbo.tblFilesHistory.SizeMb', 'SizeKb', 'COLUMN';

ALTER TABLE dbo.tblFiles
SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.tblFilesHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

How to Alter System Versioned Table Column Data Type

Use TEST_DB
GO 
-- 1. Disable system versioning

ALTER TABLE dbo.tblFiles
SET (SYSTEM_VERSIONING = OFF);

-- 2. Alter the current table
ALTER TABLE dbo.tblFiles
ALTER COLUMN ContentType NVARCHAR(MAX) NOT NULL;

-- 3. Alter the history table
ALTER TABLE dbo.tblFilesHistory
ALTER COLUMN ContentType NVARCHAR(MAX) NOT NULL;

-- 4. Re-enable system versioning
ALTER TABLE dbo.tblFiles
SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.tblFilesHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

How To Truncate System Versioned Tables

USE TEST_DB 
GO

ALTER TABLE dbo.tblFiles
SET (SYSTEM_VERSIONING = OFF);

TRUNCATE TABLE dbo.tblFiles;
TRUNCATE TABLE dbo.tblFilesHistory;

ALTER TABLE dbo.tblFiles
SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.tblFilesHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Cheers!