Các nội dung chính
1. Định nghĩa
Temporal table là một loại bảng trong SQL Server có tính năng hỗ trợ lưu trữ dữ liệu tại nhiều khoảng thời gian trong cơ sở dữ liệu.
Một bảng dữ liệu thông thường chỉ lưu trữ những thay đổi dữ liệu của 1 bản ghi tại thời điểm gần nhất, trong khi bảng temporal có thể lưu trữ thay đổi dữ liệu của 1 bản ghi tại tất cả các khoảng thời gian, từ quá khứ tới thời điểm gần nhất.
1.1. Đặc điểm
- Lịch sử các bản ghi được hệ thống quản lý lại (nên temporal tables còn có tên gọi khác là system-versioned temporal table)
- Mỗi bảng temporal đều có 2 cột riêng biệt, ghi nhận thời gian bắt đầu và kết thúc của 1 bản ghi, được để ở định dạng là datetime2. Hệ thống sẽ sử dụng 2 cột này để ghi nhận lịch sử thay đổi của mỗi bản ghi.
- Hệ thống sẽ tự động tạo hoặc người dùng có thể tạo ra bảng history, có sơ đồ tương tự như bảng temporal. Hệ thống sử dụng bảng history để lưu trữ các bản ghi cũ mỗi khi 1 bản ghi trong bảng temporal được cập nhật hoặc bị xóa.
1.2. Ứng dụng của temporal table
Temporal table có thể được sử dụng trong các trường hợp như:
- Kiểm tra các thay đổi trong dữ liệu
- Xây dựng lại hệ thống cơ sở dữ liệu từ bất kì thời điểm nào trong quá khứ
- Quan sát xu hướng qua thời gian
- Duy trì SCD để quan sát và đưa ra quyết định trước khi thay đổi cách lưu trữ dữ liệu
- Khôi phục các phiên bản cũ khi dữ liệu bị lỗi
2. Khởi tạo temporal table
Có 3 cách để khởi tạo bảng temporal dựa trên cách bảng history được thiết lập:
- Bảng temporal và bảng history không định danh: người dùng thiết lập schema của bảng hiện có; và hệ thống tự động tạo ra bảng history tương ứng và đặt tên cho bảng đó.
- Bảng temporal và bảng history mặc định: người dùng thiết lập schema và tên cho bảng history; hệ thống tạo ra bảng history theo schema đó.
- Bảng temporal và bảng history do người dùng thiết lập: người dùng thiết lập bảng history phù hợp với nhu cầu và liên kết với bảng temporal khi khởi tạo.
Ngoài ra, còn có những cách để tạo bảng temporal từ những bảng current có sẵn.
Cách 1: Bảng temporal và bảng history không định danh
Ưu điểm:
- Nhanh, thường dùng trong các môi trường test hoặc sử dụng mẫu
- Khởi tạo đơn giản vì không yêu cầu param trong điều kiện System_versioning
- Ví dụ:
CREATE TABLE Employee
(
EmpID INT NOT NULL PRIMARY KEY CLUSTERED
, EmpName VARCHAR(50) NOT NULL
, DeptID INT NULL
, ParentDeptID INT NULL
, 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);Kết quả:
Chú ý:
- Khi khởi tạo bảng temporal bắt buộc phải xác định rõ Primary Key, và có một PERIOD FOR SYSTEM_TIME bao gồm 2 cột datetime2 gắn với GENERATED ALWAYS AS ROW START / END
- Cột PERIOD luôn phải được xác định là non-null, nếu không xác định rõ thì khởi tạo sẽ thất bại.
- Bảng history luôn phải có chung schema với bảng temporal, về số cột, tên cột, thứ tự và định dạng.
- Bảng history không định danh luôn được khởi tạo tự động với một schema tương tự như bảng temporal.
- Tên của bảng history dạng này luôn có định dạng như sau: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Suffix là tùy chọn và sẽ được thêm vào nếu phần đầu của tên không phải là duy nhất.
- Bảng history được khởi tạo dưới dạng bảng lưu trữ theo dòng.
- Clustered index (thứ tự dữ liệu được lưu trữ vật lý trong 1 bảng) mặc định được tạo ra cho bảng history và được tự động đặt tên là IX_<history_table_name>. Clustered index có chứa cả các cột PERIOD (end, start).
- Khởi tạo bảng temporal một cách tối ưu bộ nhớ: https://learn.microsoft.com/en-us/sql/relational-databases/tables/system-versioned-temporal-tables-with-memory-optimized-tables?view=sql-server-ver16
Cách 2: Bảng temporal và bảng history mặc định
Được dùng khi muốn kiểm soát việc đặt tên mà vẫn muốn để hệ thống tự động tạo bảng history.
CREATE TABLE Employee
(
EmpID INT NOT NULL PRIMARY KEY CLUSTERED
, EmpName VARCHAR(50) NOT NULL
, DeptID INT NULL
, ParentDeptID INT NULL
, 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.EmployeeHistory));Kết quả:
Chú ý:
Nguyên tắc khởi tạo tương tự như cách 1, kết hợp thêm một số nguyên tắc áp dụng cho bảng history được đặt tên
- Tên schema là điều kiện bắt buộc cho param HISTORY_TABLE
- Nếu không xác định schema, CREATE TABLE sẽ lỗi.
Cách 3: Bảng temporal và bảng history do người dùng thiết lập
Được sử dụng khi người dùng muốn xác định rõ một bảng history với các lựa chọn về bộ nhớ và index khác nhau khi query dữ liệu trong quá khứ.
Trong ví dụ dưới đây, một bảng history đã được khởi tạo theo schema của bảng temporal. Trong bảng history này, clustered index dưới dạng cột và nonclustered index dưới dạng dòng đã được khởi tạo để thuận tiện cho point lookups (point lookups query trả về 1 hoặc 1 số ít dòng riêng biệt). Một bảng temporal sẽ được khởi tạo sau khi khởi tạo xong bảng history này (được coi như một bảng history mặc định)
alter table dbo.employee SET (system_versioning = OFF)
drop table dbo.Employee
drop table dbo.EmployeeHistory
CREATE TABLE EmployeeHistory
(
EmpID INT NOT NULL
, EmpName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, ValidFrom DATETIME2 NOT NULL
, ValidTo DATETIME2 NOT NULL
);
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX_EmployeeHistory
ON EmployeeHistory;
CREATE NONCLUSTERED INDEX IX_EmployeeHistory_ID_PERIOD_COLUMNS
ON EmployeeHistory (ValidTo, ValidFrom, EmpID);
GO
CREATE TABLE Employee
(
EmpID int NOT NULL PRIMARY KEY CLUSTERED
, EmpName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, 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.EmployeeHistory));Kết quả:
Chú ý:
- Trong trường hợp dùng analytic queries trên dữ liệu phiên bản cũ có sử dụng các hàm aggregates hoặc window function, người dùng nên tạo Primary index là clustered theo dạng cột để tối ưu hóa hiệu suất.
- Trong trường hợp mục tiêu chính là kiểm tra các phiên bản cũ của một dòng trong bảng hiện tại, người dùng nên sử dụng bảng history lưu trữ dưới dạng dòng với clustered index.
- Bảng history không có primary key, foreign key, unique index, table constraint hoặc trigger. Bảng này cũng không được thiết lập để change data capture, change tracking, transactional hoặc merge replication.
Ngoài ra, chúng ta còn có thể tạo temporal table từ bảng có sẵn bằng cách sau:
Thêm version vào bảng non-temporal để tạo thành bảng temporal
Chúng ta có sẵn bảng People với thông tin như sau:
Bây giờ, chúng ta thêm 2 columns period vào bảng people
ALTER TABLE dbo.People
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GoSau khi khởi tạo, chúng ta có:
Tiếp theo, khởi tạo bảng temporal với bảng history mặc định.
ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PeopleHistory))
GOKết quả:
3. Query trên bảng temporal
Khi query (truy vấn) trên bảng current với dữ liệu mới nhất, người dùng vẫn sử dụng query như khi truy vấn các bảng thông thường khác. Những khi người dùng cần query dữ liệu phiên bản cũ được lưu trữ trên bảng history, cần dùng thêm lệnh FOR SYSTEM_TIME cùng với 5 subclauses khác như sau:
4. Một số ứng dụng quan trọng của bảng temporal
4.1. Point – in – time analysis (PIT)
Dễ dàng sử dụng các bản ghi đã thay đổi/bị xóa/update tại 1 khoảng thời gian bất kì trong quá khứ để phân tích.
Tìm hiểu thêm tại: https://bertwagner.com/posts/how-to-use-temporal-tables-for-easy-point-in-time-analysis/
Ví dụ 1: Sử dụng bộ dữ liệu AdventureWorks2016, bảng HumanResources.Employee đã được khởi tạo thành bảng temporal. Truy vấn dữ liệu trong 2 bảng Employee và bảng history của Employee, chúng ta có kết quả như sau:
Vì bảng Employee không có sự thay đổi nào trong dữ liệu, vậy nên bảng history không có dữ liệu. Update dữ liệu 1 bản ghi để quan sát sự thay đổi trong 2 bảng.
Trong bảng temporal, JobTitle của BusinessEntityID 1 đã được update thành Chairman, đồng thời trong bảng history cũng lưu trữ bản ghi cũ của BusinessEntityID 1, cùng với 2 cột ValidFrom và ValidTo (ngày 12-5-2023)
Query bảng temporal với thời điểm ngày 11-5-2023 (khi chưa thực hiện thay đổi dữ liệu), JobTitle của BusinessEntityID 1 vẫn là Chief Executive Officer.
VD 2: Phân tích quá trình thay đổi trạng thái giao hàng
Khi truy vấn bảng OrderTracking, bộ dữ liệu AdventureWorks 2016 để thực hiện kiểm tra tình trạng giao hàng, chúng ta có dữ liệu như sau:
Khi query bảng OrderTracking, chúng ta nhận được các bản ghi thể hiện trạng thái mới nhất của tình trạng giao hàng. Các trạng thái giao hàng được thể hiện đầy đủ trong bảng dữ liệu dưới đây:
Như vậy, với OrderTrackingID là 3, trong bảng OrderTracking chỉ thể hiện TrackingEventID là 6, tương đương với trạng thái Đơn hàng đã được giao. Tuy nhiên, trong trường hợp chúng ta có nhu cầu kiểm tra tất cả trạng thái giao hàng của 1 đơn hàng với mục đích đánh giá thời gian chuyển trạng thái giao hàng, chúng ta cần sử dụng tới các dữ liệu trong quá khứ. Giả sử chúng ta đã khởi tạo bảng temporal OrderTracking, vì vậy chúng ta có thể dễ dàng truy vấn lại lịch sử thay đổi trạng thái giao hàng:
SELECT * FROM Sales.OrderTracking
FOR system_time ALL
WHERE OrderTrackingID = 3
ORDER BY TrackingEventID
VD 3: Có bảng dữ liệu theo dạng bảng temporal về xếp hạng khách hàng CustomerRank vào năm 2022 như sau:
CREATE TABLE CustomerRank
(
CustomerID INT IDENTITY PRIMARY KEY,
Year INT,
Rank VARCHAR(40),
TotalDue INT,
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.CustomerRankHistory)
)
INSERT INTO CustomerRank (Year, Rank, TotalDue) VALUES(2020,'Normal',5000)
INSERT INTO CustomerRank (Year, Rank, TotalDue) VALUES(2020,'Normal',5500)
INSERT INTO CustomerRank (Year, Rank, TotalDue) VALUES(2020,'VIP',10000)
INSERT INTO CustomerRank (Year, Rank, TotalDue) VALUES(2020,'VVIP',20000)
UPDATE CustomerRank SET Year = 2021, Rank ='VIP', TotalDue = 11000 where CustomerID = 1
UPDATE CustomerRank SET Year = 2021, Rank ='Normal', TotalDue = 7000 where CustomerID = 2
UPDATE CustomerRank SET Year = 2021, Rank ='VVIP', TotalDue = 21000 where CustomerID = 3
UPDATE CustomerRank SET Year = 2021, Rank ='VIP', TotalDue = 14000 where CustomerID = 4
UPDATE CustomerRank SET Year = 2022, Rank ='VVIP', TotalDue = 25000 where CustomerID = 1
UPDATE CustomerRank SET Year = 2022, Rank ='VIP', TotalDue = 13000 where CustomerID = 2
UPDATE CustomerRank SET Year = 2022, Rank ='Normal', TotalDue = 9000 where CustomerID = 3
UPDATE CustomerRank SET Year = 2022, Rank ='VIP', TotalDue = 12000 where CustomerID = 4
SELECT * FROM CustomerRankTrong trường hợp cần truy vấn xếp hạng của khách hàng có CustomerID 1 để kiểm tra sự thay đổi về xếp hạng cho tới thời điểm hiện tại, chúng ta có bảng dữ liệu sau:
SELECT * FROM CustomerRank
FOR system_time ALL
WHERE CustomerID = 1
ORDER BY year
4.2. Slowly changing dimension
Temporal table có cơ chế hoạt động khá tương đồng với SDC type 4, cả 2 đều phân tách dữ liệu hiện tại và dữ liệu đã thay đổi trong 2 bảng current và history. Tuy nhiên, SDC type 4 yêu cầu kịch bản ETL phức tạp hơn vì người dùng cần quản lí cả 2 bảng, trong khi tạo bảng temporal nhanh gọn và query cũng đơn giản hơn.
SCD type 4 và bảng temporal có cơ chế hoạt động khá tương đồng, do đều phân tách dữ liệu hiện tại và dữ liệu đã thay đổi thành 2 bảng current và history. Tuy nhiên, khi sử dụng bảng temporal, người dùng chỉ cần thao tác trực tiếp trên SQL Server bằng cách dùng query, không yêu cầu xử lí ETL phức tạp, bảo trì dữ liệu hoặc sử dụng phần mềm thứ 3 nào khác.
5. Giới hạn và cân nhắc khi sử dụng
- Bảng temporal phải có Primary Key để liên kết các bản ghi giữa bảng hiện tại và bảng history, và bảng history không có primary key.
- Các cột System_time (ValidFrom, ValidTo) phải có định dạng là datetime2.
- Syntax của bảng temporal chỉ hoạt động đối với các bảng hoặc view temporal được lưu trữ trên database. Khi sử dụng external table hoặc table trên linked server, không thể dùng FOR clause hoặc period predicates (AS OF, BETWEEN, ALL,…) trực tiếp trong query.
- Khi định danh cho bảng history lúc khởi tạo, bắt buộc phải chỉ định rõ schema và tên bảng.
- Bảng history được mặc định theo chế độ Page Compression (https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-ver16)
- Nếu bảng current là bảng đã được chia nhỏ, bảng history sẽ được khởi tạo dựa trên 1 nhóm file mặc định, vì thiết lập chia nhỏ không được sao chép tự động từ bảng current sang bảng history.
- Bảng temporal và bảng history không có định dạng FileTable hoặc FileStream, vì 2 định dạng này cho phép người dùng ngoài SQL Server tác động đến data và system versioning không thể đảm bảo được an toàn dữ liệu. (https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-ver16)
- Bảng node hoặc bảng edge không thể được khởi tạo hoặc biến đổi thành bảng temporal (https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture?view=sql-server-ver16 )
- Mặc dù bảng temporal hỗ trợ định dạng blob data như (n)varchar(max), varbinary(max), (n)text, và image, nhưng định dạng này gây tốn chi phí lưu trữ đáng kể và ảnh hưởng tới hiệu suất tùy thuộc vào kích thước của chúng. Do đó khi thiết kế hệ thống, cần lưu ý khi sử dụng những loại data type này.
- Bảng history phải được khởi tạo trên cùng 1 database với bảng current. Query over linked servers không được hỗ trợ.
- Bảng history không có ràng buộc (primary key, foreign key, ràng buộc bảng hoặc cột)
- Indexed view không được hỗ trợ trong temporal query (query có sử dụng for system_time)
- Các câu lệnh INSERT và UPDATE không tác động được tới các cột period SYSTEM_TIME.
- TRUNCATE TABLE không được khỗ trợ khi SYSTEM_VERSIONING = ON
- Không thể thay đổi trực tiếp dữ liệu trong bảng history.
- Trigger INSTEAD OF không được thực hiện cả trên bảng history và bảng current để tránh ảnh hưởng tới logic DML. Trigger AFTER được thực hiện trên bảng current.
- Giới hạn trong kĩ thuật sao chép dữ liệu:
+ Availabillity groups: sử dụng được trên cả 2 loại bảng
+ Change data capture, change tracking: chỉ hỗ trợ trên bảng current
+ Snapshot và transactional replication: Chỉ hỗ trợ 1 publisher không sử dụng temporal và 1 subscriber có sử dụng temporal.
+ Merge replication: không hỗ trợ bảng temporal.
- Các query thông thường chỉ truy vấn trên bảng current. Để query trên bảng history, phải sử dụng temporal query.
- Phương thức index để tối ưu dung lượng lưu trữ và hiệu suất là clustered index dạng cột và/hoặc B-tree index dạng dòng trên bảng current, clustered index dạng cột trên bảng history.
- Những object/properties sau không được sao chép từ bảng current sang bảng history khi khởi tạo:
+ Period definition
+ Identity definition
+ Indexes
+ Statistics
+ Check constraints
+ Triggers
+ Partitioning configuration
+ Permissions
+ Row-level security predicates - Một bảng history không thể được thiết lập thành bảng current trong 1 chuỗi bảng history.
Mình đã giới thiệu và hướng dẫn thực hành khá chi tiết tới các bạn về tính năng Temporal Table trong SQL Server. Các bạn có thể thử thực hành và nếu làm thành công hay gặp trục trặc ở bước nào thì hãy phản hồi lại kết quả cho mình nhé.
Tìm hiểu thêm các tính năng trong SQL Server cùng mình tại đây.
