To use the SYSTEM_VERSIONING property to enable temporal tables for automatic history tracking in SQL Server, you need to follow these steps:
Step 1: Create a Temporal Table
When you create a table, you can define it as a temporal table by specifying the SYSTEM_VERSIONING option. A temporal table consists of two tables: the current table, which holds the current data, and the history table, which stores past data.
Example SQL for Creating a Temporal Table
Here’s a basic example of how to create a temporal table:
CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Position NVARCHAR(100),
Salary DECIMAL(10, 2),
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 = dbo.EmployeesHistory));
Explanation of the Components
1. Columns: You define the main columns for your table (e.g., EmployeeID, Name, etc.).
2. Period Columns:
- ValidFrom and ValidTo: These are the period columns that SQL Server uses to track the validity of the row. You must specify these columns with the GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END options, respectively.
3. PERIOD FOR SYSTEM_TIME: This clause specifies the two columns that define the temporal validity period of the row.
4. SYSTEM_VERSIONING = ON: This enables the system versioning feature.
5. HISTORY_TABLE: Optionally, you can specify a table name for the history table. If you don’t specify one, SQL Server will create a history table that has the same name as your current table, suffixed with “_History”.
Step 2: Insert, Update, and Delete Operations
Once the temporal table is created, you can perform standard SQL operations like INSERT, UPDATE, and DELETE. The history of changes will be automatically tracked in the associated history table.
Example Operations
-- Inserting data
INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES (1, 'Alice', 'Developer', 60000);
-- Updating data
UPDATE Employees
SET Salary = 70000
WHERE EmployeeID = 1;
-- Deleting data
DELETE FROM Employees
WHERE EmployeeID = 1;
Step 3: Querying the History Table
You can query the history table directly if you need to retrieve historical data:
SELECT * FROM dbo.EmployeesHistory;
Step 4: Querying Temporal Data
You can also query the temporal data from the current table and get historical data using the FOR SYSTEM_TIME clause:
SELECT *
FROM Employees
FOR SYSTEM_TIME ALL; -- Retrieves all versions of the rows
Additional Considerations
- Performance: Keep in mind that while temporal tables simplify history tracking, they may introduce overhead for large datasets.
- Data Retention: You might want to implement a strategy for managing the history data over time (e.g., archiving old records).
- Compatibility: Ensure that your SQL Server version supports temporal tables (SQL Server 2016 and later).
By following these steps and guidelines, you can effectively implement and manage temporal tables using the SYSTEM_VERSIONING property in SQL Server.