To convert an existing table into a temporal table in SQL Server, you can use the ALTER TABLE statement along with the specification of system-versioning. A temporal table allows you to track historical changes to data in the table over time by automatically storing the changes in a separate history table.
Here’s how you can convert an existing table to a temporal table:
Prerequisites
1. The existing table must have a primary key.
2. You should have two datetime2 columns to store the period of validity for the records, often named ValidFrom and ValidTo.
Example SQL Steps
1. Add the Necessary Columns: First, you'll need to add the ValidFrom and ValidTo columns to your existing table.
2. Set Up the History Table: When you enable system-versioning, SQL Server automatically creates a history table unless specified otherwise.
3. Alter the Table: Use the ALTER TABLE statement to set up system-versioning.
Here’s a step-by-step example:
Assume you have an existing table named Employees.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Position NVARCHAR(100),
Salary DECIMAL(10, 2)
);
Step 1: Add Valid From and Valid To Columns
ALTER TABLE Employees
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
Step 2: Enable System-Versioning
Now you can alter the Employees table to enable system-versioning:
ALTER TABLE Employees
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
Important Points
- History Table: The HISTORY_TABLE option specifies a custom name for the history table. If you omit it, SQL Server will generate a name automatically.
- Setting default values: The ValidFrom and ValidTo columns will have default behaviors set by the system for actual versioning, you don’t need to explicitly set values for these columns when inserting or updating rows in the main table.
- Data Migration: If you already have existing data in the table, you will need to consider how to populate the ValidFrom and ValidTo columns before enabling system-versioning, as they cannot be left as NULL.
Complete Example
Putting it all together, here’s how you can convert an existing table into a temporal table:
-- Add the required period columns
ALTER TABLE Employees
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
-- Enable system-versioning
ALTER TABLE Employees
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
This is how you can use the ALTER TABLE statement to convert an existing table into a temporal table in SQL Server.