In SQL Server, temporal tables (or system-versioned tables) allow you to track historical changes to your data automatically. This functionality relies on using two special types of columns: GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END. These columns are crucial for managing the temporal aspects of the table, specifically to track when each row was valid.
How to Use GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END
1. Define the Temporal Table:
When creating a temporal table, you must specify two columns that will store the start and end times of each record. This is typically done in the table's definition.
Here's an example of how to create a temporal table:
CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
Period AS ([ValidFrom] TO [ValidTo]), -- Optional: to create a period column
VALID FOR SYSTEM_TIME ALL -- Specifies the system versioning
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
In this example:
- ValidFrom is defined with GENERATED ALWAYS AS ROW START, which means that when a new row is inserted, this column will automatically be set to the current date and time.
- ValidTo is defined with GENERATED ALWAYS AS ROW END, which by default is set to 9999-12-31 23:59:59.9999999 to represent future validity until the record is updated.
- The VALID FOR SYSTEM_TIME ALL clause specifies that this table will maintain a complete history of changes.
2. Inserting Data:
When you insert data into the Employees table, you don't need to explicitly provide values for the ValidFrom and ValidTo columns. They will be automatically populated by SQL Server:
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe'),
(2, 'Jane', 'Smith');
After executing this insert statement, ValidFrom will have the current date and time for both rows, and ValidTo will be set to 9999-12-31 23:59:59.9999999.
3. Updating Data:
When you update a record, SQL Server will automatically close the current version of the row and create a new version with updated values. Here’s how it works:
UPDATE Employees
SET LastName = 'Doe-Johnson'
WHERE EmployeeID = 1;
This update will:
- Update the LastName for EmployeeID 1.
- Set the ValidTo of the original row (before the update) to the current date and time (indicating when it became invalid).
- Create a new row with the same EmployeeID, the updated LastName, and set ValidFrom to the current timestamp, with ValidTo remaining at 9999-12-31 23:59:59.9999999.
4. Querying the History:
You can see the historical data stored in the history table defined in the SYSTEM_VERSIONING clause. For example, to view all historical records, you would run:
SELECT *
FROM EmployeesHistory;
This allows you to track changes and see what the values were at any point in time.
Considerations
- Time Zone: Ensure you are aware of the server's time zone settings because the DATETIME2 type stores the date and time without time zone awareness.
- Cleanup: Over time, the history table can grow significantly in size, especially for tables that are frequently updated. Database management and cleanup strategies should be implemented as necessary.
- Performance: Using temporal tables does incur some additional overhead, but it can greatly simplify auditing and reporting features.
By using GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, SQL Server enables a straightforward mechanism to track the historical changes of the records without requiring intricate management of timestamps.