Introduction

Understanding the Script

First, we are checking if the schema with the name HR already exists. If it does, we make use of the same and do nothing. However, if it does not exist, we create one.

-- Step 1: Create schema if it does not exist
IF NOT EXISTS (
    SELECT 1
    FROM sys.schemas
    WHERE name = 'HR'
)
BEGIN
    EXEC ('CREATE SCHEMA HR');
END;

Next, we will create the temporal table. The following snippet creates a table named EmployeePay inside the HR schema. Its fully qualified name would be HR.EmployeePay. The entire block of code inside the parentheses hold the column definitions and the table rules.

• EmployeeID has been declared as a primary key. It is a numeric column which should mandatorily be populated
• EmployeeName is a varchar column which can store maximum of 100 characters, enough for an employee's name. This should also be mandatorily populated
• Department is a varchar column which can store maximum of 50 characters, enough for an employee's department name. This should also be mandatorily populated
• HourlyRate is a decimal column which can store the hourly compensation of an employee with precision up to 2 decimal places
• SysStartTime and SysEndTime are the temporal period columns in datetime2 format as we discussed above
  • They both are DATETIME2 columns which store precise date and time
  • GENERATED ALWAYS means SQL Server, not our application, controls the value
  • SysStartTime uses AS ROW START which identifies this as the beginning of the row’s validity period
  • SysEndTime uses AS ROW END which identifies it as the end of the validity period
  • HIDDEN keyword keeps these columns out of result set generated by using SELECT * query run on this table
• PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) - This statement tells the SQL Server that:
  • SysStartTime is the start of the validity period
  • SysEndTime is the end of the validity period

CREATE TABLE HR.EmployeePay
(
    EmployeeID INT NOT NULL PRIMARY KEY CLUSTERED,
    EmployeeName VARCHAR(100) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    HourlyRate DECIMAL(10, 2) NOT NULL,

    -- Temporal period columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)

Note: For an active row, SQL Server uses a far-future value in SysEndTime column to indicate that the row is still current. When the row is updated or deleted, SQL Server sets SysEndTime value to the time when that version ended (or the operation on that row data was performed). A temporal table must have exactly one PERIOD FOR SYSTEM_TIME definition using the two DATETIME2 columns.

Finally, we will enable the automatic history tracking.

• SYSTEM_VERSIONING = ON turns the table into a system-versioned temporal table. From that point onwards, SQL Server automatically manages the history (or versioning of records).
• We define the name of the history table that our SQL Server should use.
• HR.EmployeePay is the current table which will store the active/in-use data in real time.
• HR.EmployeePayHistory is the temporal table which will store the older versions of the rows of HR.EmployeePay (which are obsolete/deleted).
• We do not need to manually create this history table: HR.EmployeePayHistory at all. SQL Server creates it when this statement is executed, provided the table name is available for creation.
• The history table must stay structurally aligned with the current table, and SQL Server manages it for us.

WITH
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = HR.EmployeePayHistory
    )
);

DDL Script

The entire consolidated script becomes as:

-- Step 1: Create schema if it does not exist
IF NOT EXISTS (
    SELECT 1
    FROM sys.schemas
    WHERE name = 'HR'
)
BEGIN
    EXEC ('CREATE SCHEMA HR');
END;

-- Step 2: Create system-versioned temporal table
CREATE TABLE HR.EmployeePay
(
    EmployeeID INT NOT NULL PRIMARY KEY CLUSTERED,
    EmployeeName VARCHAR(100) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    HourlyRate DECIMAL(10, 2) NOT NULL,

    -- Temporal period columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = HR.EmployeePayHistory
    )
);

Using the Temporal Table

Now that we have the actual table called HR.EmployeePay created in our database, the next obvious step is to populate it with some set of initial data. Once we have some initial data load populated into our actual table (referred here as HR.EmployeePay), we will then try updating/deleting one/few of such record(s) to see how SQL Server manages to maintain the history of those records.

Populating data into actual table

To start with the usage of temporal table and see how history is maintained there (i.e., in HR.EmployeePayHistory), we have to first insert a record into the actual data table (HR.EmployeePay). To do so, we can make use of a simple INSERT query as provided below. This query inserts 2 records into the HR.EmployeePay table.

INSERT INTO HR.EmployeePay (EmployeeID, EmployeeName, Department, HourlyRate)
VALUES 
(101, 'Alex Smith', 'Engineering', 45.00),
(102, 'Sara Connor', 'Marketing', 38.50);

After running the above DML (Data Manipulation Language) query, we can execute a simple SELECT query to check the data on our HR.EmployeePay table as below:

SELECT EmployeeID, EmployeeName, Department, HourlyRate, SysStartTime, SysEndTime 
FROM HR.EmployeePay;

The above query when executed, should generate a similar output:

Note the result set above. We just discussed a few minutes back that for any active record (like the ones shown above in the result set), the SQL Server uses a far-future value as the row end time (i.e., SysEndTime here) to indicate that the row is still valid. In the above result set, we can see that the SysEndTime for both the rows have been set as 9999-12-31 which means the row end time has been set to 31st December 9999 which is the far-future value for the SQL Server. However, the row start time has been kept as the transaction timestamp in UTC.

If we execute a SELECT query on the HR.EmployeePayHistory table, we should see no records there yet. That is obvious because in our actual table: HR.EmployeePay we did not update or delete any record yet. So, running the below query:

SELECT * FROM HR.EmployeePayHistory;

generates no result set.

Updating data in the actual table

Let us assume that after a performance evaluation, at the end of the appraisal cycle, the employee Alex Smith receives a decent raise of $52.00 per hour. To do so, we can update the salary of Alex Smith on the HR.EmployeePay table using the following query:

UPDATE HR.EmployeePay
SET HourlyRate = 52.00
WHERE EmployeeID = 101;

Once the above query is executed, the record in the HR.EmployeePay gets updated against Alex Smith. But let us take a moment to understand what happened behind the scenes when we executed this update statement.

• SQL Server copies the existing state of employee 101 (Alex Smith) into HR.EmployeePayHistory
• It sets the history row's SysEndTime to the current transaction timestamp in UTC
• Next, it updates the active record in HR.EmployeePay with new hourly rate and sets its SysStartTime to current transaction timestamp in UTC

So, if we query the live (or actual) table and the history table using the following SELECT queries:

-- Current State
SELECT EmployeeID, HourlyRate, SysStartTime, SysEndTime 
FROM HR.EmployeePay 
WHERE EmployeeID = 101;

-- History State
SELECT EmployeeID, HourlyRate, SysStartTime, SysEndTime 
FROM HR.EmployeePayHistory 
WHERE EmployeeID = 101;

We should see following outputs:

Deleting data from the actual table

Finally, we will see what happens when we delete a data from the actual table. Assume that an employee exits from the company. In that case, the HR can decide to remove the info about the exited employee from the HR.EmployeePay table. In our example, let's consider Sara Cornor decides to exit from the company. We can record the same using a delete query as below:

DELETE FROM HR.EmployeePay
WHERE EmployeeID = 102;

When the above query is run, the following happens:

• SQL Server deletes the record from the HR.EmployeePay table
• It copies the record over to HR.EmployeePayHistory table and updates the SysEndTime there to the current timestamp in UTC

If we query the HR.EmployeePay table now, it will return no records for this employee id 102 since the record has been deleted from the live table. However, if we run the select query on HR.EmployeePayHistory table, it will fetch the historical record.

Querying historical data using FOR SYSTEM_TIME

Considering that our historical data in the history table now looks like this:

Now, we just understood how historical record is maintained by SQL Server automatically for our live table. However, employees who serve a company for a decade may see multiple salary changes or department changes during their tenure. So what if we want to see what was the department and hourly rate of employees at a particular time? In that case, we can make use of FOR SYSTEM_TIME AS OF <DATETIME> clause.

So, what if we want to see the details of the employees at a particular time in the historical moment, say at: 13th Sept., 2026 at 14:36:00.000 hours? To do so, we can make use of the following query:

SELECT EmployeeID, EmployeeName, Department, HourlyRate
FROM HR.EmployeePay
FOR SYSTEM_TIME AS OF '2026-09-13 14:36:00.000';

The output of the above query is:

If we take a closer look, we can see that the time we used was after Alex's pay raise (or hourly rate hike) and before Sara's exit. So, the record shows the hourly rate of Alex Smith as $52 and not $45. Also, we can see that Sara Connor was still in the company then. However, if we take the time to 14:58:00.000, which is after Sara Connor's exit, we should see the following output:

SELECT EmployeeID, EmployeeName, Department, HourlyRate
FROM HR.EmployeePay
FOR SYSTEM_TIME AS OF '2026-09-13 14:58:00.000';

Performance Tuning and Best Practices

In a real time production grade environment, the historical volume will grow large. It is important to focus on performance tuning for faster query executions. To achieve that, we can make use of the following best practices:

• History table indexing: We need to ensure that the history table has a clustered columnstore index (CCI) or a suitable clustered B-Tree index structured around high-cardinality period dates. It can be done using a query like this below:

  CREATE CLUSTERED COLUMNSTORE INDEX IX_EmployeePayHistory 
  ON HR.EmployeePayHistory;?

• Avoiding direct history writes: The system-versioning guarantees data non-repudiation. So we should never bypass history restrictions unless executing compliance/purge maintenance under controlled maintenance windows.
• Using UTC timestamps: SQL Server uses the UTC time for temporal system values (sysutcdatetime()). We should always handle the timezone conversions at the application reporting tier.

Conclusion

System-Versioned Temporal Tables provide an out-of-the-box, ANSI SQL-compliant solution for auditing and historical data tracking in the SQL Server. By automatically offloading UPDATE and DELETE records into a managed history table, they eliminate the need for error-prone custom triggers and complex schema logic.

Whether we need to answer point-in-time analytical queries using FOR SYSTEM_TIME AS OF or meet strict enterprise compliance requirements, temporal tables always deliver a low-maintenance, reliable foundation. When combined with proper indexing strategies (such as Clustered Columnstore Indexes) and automated retention policies, they offer a scalable way to preserve full data lineage without sacrificing read performance.