Free SQL Help for Devs, DBAs, and the Curious

SQL Server 2016 introduced Temporal Tables, a game-changing feature for tracking historical data changes. Temporal tables, also known as system-versioned tables, keep a full history of data changes and allow easy point-in-time analysis. This is an essential feature for database professionals, especially in industries like finance, healthcare, and insurance where data auditing and change tracking are critical.

Creating a temporal table in SQL Server is straightforward. Two tables are involved: the current table (also known as the base table) and the history table. The base table holds the current data, while the history table stores all changes to the data. SQL Server automatically manages the history table for us, ensuring that any INSERT, UPDATE, or DELETE operation on the base table is reflected in the history table.

-- T-SQL code for creating a temporal table
CREATE TABLE Customers
(
    CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
    Name nvarchar(100) NOT NULL,
    Email nvarchar(100) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

In MySQL 5.7, similar functionality can be achieved using the system versioning feature. This feature, available from MySQL 5.7.35, allows creating tables that maintain row versions automatically. This allows the DBMS to query data at any point in time, just like SQL Server's temporal tables.

-- MySQL code for creating a versioned table
CREATE TABLE Customers
(
    CustomerID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    SysStartTime TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    SysEndTime TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
    PRIMARY KEY (CustomerID)
)
WITH SYSTEM VERSIONING;

IBM's DB2 11.1 also provides a similar feature called temporal tables. Like SQL Server and MySQL, DB2's temporal tables allow database professionals to keep track of data changes over time. The syntax to create a temporal table in DB2 is similar to SQL Server and MySQL, with a few variations.

Azure SQL, Microsoft's cloud-based database service, also supports temporal tables. The process of creating and using temporal tables in Azure SQL is identical to SQL Server, which makes it easier for database professionals to transition their applications and data to the cloud.

In conclusion, temporal tables are a powerful feature for tracking and auditing data changes over time. They are supported in SQL Server 2016 and later, MySQL 5.7.35 and later, DB2 11.1, and Azure SQL. By understanding and leveraging this feature, database professionals can ensure data integrity, facilitate better decision-making, and meet regulatory requirements for data auditing.