Temporal Tables

Định nghĩa

Temporal Tables (còn gọi là system-versioned temporal tables) là tính năng được giới thiệu trong SQL Server 2016, theo chuẩn SQL:2011. Temporal tables tự động lưu trữ toàn bộ lịch sử thay đổi của dữ liệu vào một history table riêng, cho phép query dữ liệu tại bất kỳ thời điểm nào trong quá khứ.

Cách hoạt động

• Mỗi row trong temporal table có 2 datetime2 columns xác định khoảng thời gian hiệu lực: ValidFrom và ValidTo
• Khi UPDATE hoặc DELETE: SQL Server tự động ghi bản cũ vào history table với ValidTo = current_time
• Khi INSERT: ValidFrom = current_time, ValidTo = '9999-12-31 23:59:59.9999999' (max datetime2)
• Không cần trigger — hoàn toàn tự động và atomic

Tạo Temporal Table

Tạo từ đầu

-- Tạo temporal table với explicit history table name
CREATE TABLE dbo.Employees (
    EmployeeID      INT NOT NULL PRIMARY KEY,
    Name            NVARCHAR(100) NOT NULL,
    Department      NVARCHAR(50) NOT NULL,
    Salary          DECIMAL(15, 2) NOT NULL,
    ManagerID       INT NULL,
    -- Period columns: MUST be datetime2, NOT NULL
    ValidFrom       DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo         DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    -- Declare period
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.Employees_History,
        DATA_CONSISTENCY_CHECK = ON  -- Kiểm tra tính nhất quán khi enable
    )
);

-- Tạo với auto-generated hidden period columns (ẩn khỏi SELECT *)
CREATE TABLE dbo.Products (
    ProductID       INT NOT NULL PRIMARY KEY,
    ProductName     NVARCHAR(100) NOT NULL,
    Price           DECIMAL(10, 2) NOT NULL,
    IsActive        BIT NOT NULL DEFAULT 1,
    -- Hidden period columns (không xuất hiện trong SELECT *)
    ValidFrom       DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo         DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
-- SQL Server tự tạo history table với tên MSSQL_TemporalHistoryFor_<object_id>

Index mặc định của History Table

SQL Server tự động tạo một clustered index trên history table dựa trên period columns:

-- History table structure được tạo tự động:
-- dbo.Employees_History với clustered index trên (ValidFrom, ValidTo)
-- Không có PRIMARY KEY constraint

-- Xem history table
SELECT * FROM dbo.Employees_History ORDER BY ValidFrom;

-- Kiểm tra indexes trên history table
SELECT i.name, i.type_desc, STRING_AGG(c.name, ', ') AS key_columns
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('dbo.Employees_History')
GROUP BY i.name, i.type_desc;

Convert Existing Table thành Temporal Table

-- Bước 1: Thêm period columns vào table hiện có
ALTER TABLE dbo.Customers
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    CONSTRAINT DF_Customers_ValidFrom DEFAULT SYSUTCDATETIME(),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    CONSTRAINT DF_Customers_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

-- Bước 2: Enable system versioning
ALTER TABLE dbo.Customers
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Customers_History));

-- Kiểm tra kết quả
SELECT 
    name,
    temporal_type_desc,
    history_table_id,
    OBJECT_NAME(history_table_id) AS history_table_name
FROM sys.tables
WHERE name = 'Customers';

Querying Historical Data

FOR SYSTEM_TIME AS OF

Trả về rows có hiệu lực tại thời điểm cụ thể (ValidFrom <= t AND ValidTo > t):

-- Dữ liệu tại thời điểm cụ thể
SELECT EmployeeID, Name, Department, Salary, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2023-06-01 00:00:00';

-- Dùng với biến
DECLARE @asOf DATETIME2 = DATEADD(MONTH, -3, SYSUTCDATETIME());
SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF @asOf;

FOR SYSTEM_TIME FROM…TO

Trả về rows có any overlap với khoảng thời gian [start, end) — không bao gồm end:

-- Rows có hiệu lực bất kỳ lúc nào trong khoảng 2023 Q1
SELECT EmployeeID, Name, Salary, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME FROM '2023-01-01' TO '2023-04-01'
ORDER BY EmployeeID, ValidFrom;
-- Điều kiện: ValidFrom < '2023-04-01' AND ValidTo > '2023-01-01'

FOR SYSTEM_TIME BETWEEN…AND

Tương tự FROM…TO nhưng bao gồm end boundary (inclusive):

-- BETWEEN là inclusive cả 2 đầu
SELECT EmployeeID, Name, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-12-31 23:59:59.9999999';
-- Điều kiện: ValidFrom <= '2023-12-31...' AND ValidTo > '2023-01-01'

FOR SYSTEM_TIME CONTAINED IN

Chỉ trả về rows hoàn toàn nằm trong khoảng thời gian (ValidFrom >= start AND ValidTo <= end):

-- Rows tồn tại trong DB hoàn toàn trong khoảng thời gian này
-- Hữu ích để tìm rows đã bị xóa trong period
SELECT EmployeeID, Name, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME CONTAINED IN ('2023-01-01', '2024-01-01');
-- Điều kiện: ValidFrom >= '2023-01-01' AND ValidTo <= '2024-01-01'

FOR SYSTEM_TIME ALL

Trả về tất cả rows từ cả current và history table:

-- Toàn bộ lịch sử của tất cả employees
SELECT EmployeeID, Name, Department, Salary, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
ORDER BY EmployeeID, ValidFrom;

-- Xem lịch sử thay đổi lương của một employee
SELECT EmployeeID, Name, Salary, ValidFrom, ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 42
ORDER BY ValidFrom;

Các Use Cases phổ biến

Audit Trail

-- Xem mọi thay đổi của một row
SELECT 
    EmployeeID,
    Name,
    Department,
    Salary,
    CASE 
        WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 'CURRENT'
        ELSE 'HISTORICAL'
    END AS row_status,
    ValidFrom AS changed_at,
    ValidTo AS valid_until
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 42
ORDER BY ValidFrom;

Point-in-Time Reporting

-- Financial report tại thời điểm cuối tháng trước
DECLARE @reportDate DATETIME2 = DATEADD(SECOND, -1, 
    DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

SELECT 
    e.EmployeeID,
    e.Name,
    e.Department,
    e.Salary
FROM dbo.Employees FOR SYSTEM_TIME AS OF @reportDate e
WHERE e.Department = 'Engineering'
ORDER BY e.Name;

Slowly Changing Dimensions (SCD Type 2)

-- Temporal table thay thế cho SCD Type 2 manual implementation
-- Không cần EffectiveDate, ExpirationDate, IsCurrent columns thủ công

-- Xem dimension values tại thời điểm cụ thể để join với fact table
SELECT 
    f.SaleAmount,
    f.SaleDate,
    p.ProductName,
    p.Price AS price_at_sale_time
FROM dbo.SalesFact f
JOIN dbo.Products FOR SYSTEM_TIME AS OF f.SaleDate p 
    ON f.ProductID = p.ProductID;

History Table

Cấu trúc và Indexes

-- History table không có:
-- - PRIMARY KEY
-- - UNIQUE constraints
-- - FOREIGN KEY constraints
-- - Triggers
-- - DEFAULT constraints

-- Chỉ có 1 clustered index tự động tạo trên (ValidTo, ValidFrom)
-- HOẶC (ValidFrom, ValidTo) tùy SQL Server version

-- Thêm index vào history table để optimize historical queries
CREATE INDEX IX_Employees_History_EmployeeID_Period
ON dbo.Employees_History (EmployeeID, ValidFrom, ValidTo);

-- Xem dữ liệu trong history table trực tiếp
SELECT * FROM dbo.Employees_History WHERE EmployeeID = 42;
-- Lưu ý: Không có syntax FOR SYSTEM_TIME khi query history table trực tiếp

Data Retention cho History Table

-- SQL Server 2017+: Cấu hình retention period để tự động cleanup history
ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = ON (
    HISTORY_RETENTION_PERIOD = 1 YEAR  -- Giữ 1 năm lịch sử
));
-- Các options: INFINITE (default), DAYS, WEEKS, MONTHS, YEARS

-- Xem cấu hình retention
SELECT 
    name,
    history_retention_period,
    history_retention_period_unit_desc
FROM sys.tables
WHERE temporal_type = 2  -- 2 = SYSTEM_VERSIONED_TEMPORAL
  AND name = 'Employees';

Disable và Re-Enable System Versioning

-- Disable system versioning (cần cho DDL changes)
ALTER TABLE dbo.Employees SET (SYSTEM_VERSIONING = OFF);
-- Sau khi disable: table vẫn tồn tại, history table vẫn tồn tại nhưng không còn tự động update

-- Thực hiện DDL changes trên current table
ALTER TABLE dbo.Employees 
ADD Email NVARCHAR(200) NULL;

-- Cũng cần update history table structure
ALTER TABLE dbo.Employees_History 
ADD Email NVARCHAR(200) NULL;

-- Re-enable system versioning
ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.Employees_History,
    DATA_CONSISTENCY_CHECK = ON
));

Temporal Table Limitations

-- Lỗi sẽ xảy ra:
TRUNCATE TABLE dbo.Employees;  
-- Error: Cannot truncate table 'dbo.Employees' because it is a system-versioned temporal table

-- Phải dùng DELETE thay thế
DELETE FROM dbo.Employees WHERE EmployeeID = 999;
-- Hoặc nếu muốn xóa nhiều: DELETE without WHERE (chậm hơn nhưng cho phép)

Temporal Table + DDL Management

-- Xem tất cả temporal tables trong database
SELECT 
    t.name AS current_table,
    ht.name AS history_table,
    t.temporal_type_desc,
    t.history_retention_period,
    t.history_retention_period_unit_desc
FROM sys.tables t
JOIN sys.tables ht ON t.history_table_id = ht.object_id
WHERE t.temporal_type = 2
ORDER BY t.name;

-- Tạo script để backup temporal configuration
SELECT 
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + 
    ' SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ' + 
    QUOTENAME(SCHEMA_NAME(ht.schema_id)) + '.' + QUOTENAME(ht.name) + '));' AS enable_script
FROM sys.tables t
JOIN sys.tables ht ON t.history_table_id = ht.object_id
WHERE t.temporal_type = 2;

Temporal + Always On Availability Groups

-- Temporal tables hoạt động tốt với Always On AG:
-- - Current table và history table đều được replicate
-- - FOR SYSTEM_TIME queries có thể chạy trên readable secondary
-- - Minimal overhead vì không cần trigger

-- Trên secondary replica (readable):
-- Có thể query lịch sử nhưng KHÔNG thể write
SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF '2023-01-01';
-- Works on secondary!

-- Best practice: Tạo nonclustered indexes trên history table cho historical query performance
CREATE INDEX IX_Employees_History_Department_Period
ON dbo.Employees_History (Department, ValidFrom, ValidTo)
INCLUDE (Name, Salary);

Q&A theo Cấp Độ

Junior Level

Q: Temporal Table là gì và tại sao nên dùng thay vì trigger + audit table thủ công?

A: Temporal Table là bảng có tính năng system-versioning tích hợp sẵn trong SQL Server 2016+. SQL Server tự động lưu lịch sử thay đổi vào history table khi có write operations. Dùng thay vì trigger vì:

• Zero maintenance code (không cần viết trigger)
• Atomic với transaction gốc (trigger có thể fail riêng lẻ)
• Có syntax query đặc biệt FOR SYSTEM_TIME AS OF rất thuận tiện
• Hiệu năng tốt hơn (không cần trigger execution overhead)

Q: Sự khác biệt giữa ValidFrom và ValidTo trong temporal table?

A: ValidFrom = thời điểm row này bắt đầu có hiệu lực (UTC, auto-set khi INSERT hoặc UPDATE). ValidTo = thời điểm row này hết hiệu lực. Row hiện tại có ValidTo = '9999-12-31...'. Row trong history table có ValidTo = thời điểm nó bị replace/delete.

Q: FOR SYSTEM_TIME AS OF và FOR SYSTEM_TIME ALL khác nhau thế nào?

A: AS OF 'datetime' chỉ trả về rows có hiệu lực tại thời điểm đó (như snapshot). ALL trả về mọi rows từ cả current table và history table — toàn bộ lịch sử.

Mid Level

Q: Tại sao không thể TRUNCATE một temporal table? Giải pháp thay thế?

A: TRUNCATE không cho phép vì SQL Server cần maintain tính nhất quán giữa current table và history table. TRUNCATE bỏ qua triggers và row-level tracking. Giải pháp:

1. DELETE FROM dbo.TableName — xóa từng row (chậm nhưng an toàn, ghi history)
2. Nếu muốn xóa cả history: Tắt system versioning, TRUNCATE cả hai tables, re-enable
3. ALTER TABLE ... SET (SYSTEM_VERSIONING = OFF); TRUNCATE TABLE ...; TRUNCATE TABLE ..._History; ALTER TABLE ... SET (SYSTEM_VERSIONING = ON ...);

Q: Làm sao tối ưu performance của historical queries trên temporal table?

A:

1. Index trên history table: SQL Server tự tạo clustered index trên (ValidTo, ValidFrom) hoặc (ValidFrom, ValidTo). Thêm nonclustered indexes cho các common query patterns (e.g., thêm EmployeeID, Department).
2. Partitioning history table: Partition theo ValidFrom để aging out old data và pruning trong queries.
3. Retention policy: Set HISTORY_RETENTION_PERIOD để tự cleanup data cũ, giảm history table size.
4. Avoid FOR SYSTEM_TIME ALL khi không cần: Query chỉ history table trực tiếp nếu chỉ cần historical data.

Senior Level

Q: Temporal Table khác CDC thế nào về mặt kiến trúc và khi nào nên chọn cái nào?

A: Architecture:

• Temporal: Synchronous, atomic với DML transaction. Write triggers immediate history insert trong same transaction.
• CDC: Asynchronous, reads transaction log after the fact. Change capture jobs run separately.

Chọn Temporal khi: Cần point-in-time queries trong application, compliance audit, slowly changing dimensions, không muốn dependency SQL Agent, simplicity.

Chọn CDC khi: Cần feed downstream systems (data warehouse, event bus), cần capture before-values cho deletes trong consumer systems, cần fine-grained column-level change tracking, multiple downstream consumers consume at different rates.

Kết hợp cả hai: Temporal trên OLTP + CDC để extract changes sang data platform. Không conflict nhau.

Q: Giải thích cách thiết kế một audit system enterprise-grade dùng Temporal Tables cho một hệ thống có 100+ tables cần audit?

A: Approach:

1. Selective auditing: Không phải tất cả 100 tables đều cần full temporal — phân loại tables theo sensitivity (PII, financial, config).

2. Standardized period column naming: Convention nhất quán (AuditFrom/AuditTo hoặc ValidFrom/ValidTo) để automation có thể xử lý.

3. Automated provisioning script:

-- Template để detect và enable temporal cho tables chưa có
SELECT 
    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.tables
WHERE temporal_type = 0  -- Non-temporal
    AND name IN (SELECT TableName FROM dbo.AuditConfig WHERE IsEnabled = 1);

4. Retention tiered: Bảng financial → 7 năm. Bảng session data → 90 ngày.

5. History table partitioning cho tables lớn:

-- Partition history table theo year để efficient range queries và archival
CREATE PARTITION FUNCTION pf_AuditYear (DATETIME2)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01', '2025-01-01');

6. Centralized history reporting view:

-- UNION ALL across all temporal history tables với consistent schema
CREATE VIEW dbo.AuditTrail AS
SELECT 'Employees' AS TableName, CAST(EmployeeID AS NVARCHAR) AS EntityID, 
       ValidFrom, ValidTo FROM dbo.Employees_History
UNION ALL
SELECT 'Products', CAST(ProductID AS NVARCHAR), ValidFrom, ValidTo 
FROM dbo.Products_History;

7. Schema change management: CI/CD pipeline phải automatically tắt system versioning, apply migration, update history table, re-enable. Dùng Flyway/Liquibase với custom callbacks.