Mô hình bảng dữ liệu thời gian trong SQL SERVER

10:08 - 25/09/2022  |  737 lượt xem

Chia sẻ
Ngày nay, không ai còn xa lạ với các hệ thống chương trình trên máy tính, nhưng một trong những thành tố quan trọng làm nên sự sống động của chúng đó là nguồn dữ liệu, các nguồn dữ liệu này được tổ chức lại và gọi chung là cơ sở dữ liệu (CSDL). Người sử dụng tra cứu các thông tin trên chương trình thông qua việc truy xuất CSDL. Theo thời gian và việc bùng nổ thông tin làm tăng dung lượng của các CSDL, dẫn đến một vấn đề khó khăn là truy xuất dữ liệu sẽ chậm dần. Để giải quyết vấn đề này, các CSDL lớn thường chia làm 2 cụm lưu trữ riêng tạm gọi là “Dữ liệu hiện tại” và “Dữ liệu lịch sử”.
Mô hình bảng dữ liệu thời gian trong SQL SERVER

Ảnh minh họa

Các dữ liệu hiện tại nhỏ hơn, nhưng được tra cứu với tần suất rất lớn, thường xuyên như các văn bản trong vòng một vài tháng, hoặc các hóa đơn tiền điện trong một năm... Ngược lại các dữ liệu lịch sử thì rất lớn nhưng lại tra cứu với tần suất khá thấp như các công văn của các năm trước… Việc tổ chức như vậy vừa đảm bảo được vấn đề hiệu năng truy xuất, vừa đảm bảo được dữ liệu gọn nhẹ trong dữ liệu hiện tại. Hàng tháng, người quản trị cần chuyển dữ liệu hiện tại qua phần lịch sử để duy trì hệ thống hoạt động. Tuy nhiên, công việc này tốn khá nhiều thời gian và khó khăn vì phải thao tác trực tiếp trên CSDL.

Phiên bản SQL Server version 2016 (hoặc Azure SQL Database) về sau đã bổ sung một loại bảng dữ liệu kiểu Temporal table (tạm dịch là bảng thời gian). Với kiểu bảng dữ liệu này, cho phép người quản trị thực hiện nhiều thao tác tra cứu, phục hồi dữ liệu và đặc biệt là không nhất thiết phải tổ chức thành 2 cụm dữ liệu như nêu trên mà vẫn đảm bảo được hiệu năng truy xuất và gọn nhẹ trong dữ liệu hiện tại.

Kiểu bảng dữ liệu này được tổ chức trong CSDL như sau:

Chức năng bảng

Cho phép cung cấp chính xác thông tin dữ liệu của bảng tại bất kỳ thời điểm nào.

Loại bảng được thiết kế để lưu giữ lịch sử đầy đủ về các thay đổi dữ liệu, cho phép dễ dàng phân tích theo thời gian.

Cấu trúc bảng

Mỗi bảng có hai cột được xác định rõ ràng, mỗi cột có kiểu dữ liệu datetime2. Các cột này được gọi là cột thời kỳ. Các cột khoảng thời gian này được hệ thống sử dụng riêng để ghi lại khoảng thời gian có hiệu lực cho mỗi hàng, bất cứ khi nào một hàng được sửa đổi.

Bảng chính lưu trữ dữ liệu là Current Table (tạm gọi là bảng hiện tại).

Ngoài các cột thời gian này, bảng thời gian cũng chứa tham chiếu đến một bảng khác có lược đồ được nhân đôi là History Table (tạm gọi là bảng lịch sử).

Hệ thống sử dụng bảng lịch sử để tự động lưu trữ phiên bản dữ liệu trước của reccord mỗi được cập nhật hoặc xóa.

Trong quá trình tạo bảng tạm thời, người dùng có thể chỉ định bảng lịch sử hiện có  hoặc để hệ thống tạo bảng lịch sử mặc định.

Cơ chế hoạt động

Dữ liệu được hệ thống triển khai dưới dạng một cặp bảng, một bảng hiện tại và một bảng lịch sử. Trong mỗi bảng này, hai cột datetime2 bổ sung được sử dụng để xác định khoảng thời gian có hiệu lực cho mỗi hàng:

Cột bắt đầu khoảng thời gian: Hệ thống ghi lại thời gian bắt đầu cho hàng trong cột này, thường được ký hiệu là cột BatDau.

Cột kết thúc giai đoạn: Hệ thống ghi lại thời gian kết thúc cho hàng trong cột này, thường được ký hiệu là cột KetThuc.

Bảng hiện tại chứa giá trị hiện tại cho mỗi hàng. Bảng lịch sử chứa từng giá trị trước đó cho mỗi hàng (nếu có), và thời gian bắt đầu và thời gian kết thúc cho khoảng thời gian mà nó hợp lệ.

CREATE TABLE BangTest

(

MaSo int NOT NULL PRIMARY KEY CLUSTERED

, HoTen nvarchar(50) NOT NULL

, QueQuan varchar(100) NOT NULL

, BatDau datetime2 GENERATED ALWAYS AS ROW START

, KetThuc datetime2 GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (BatDau, KetThuc)

 )

WITH (SYSTEM_VERSIONING = ON)

Khi INSERTS: Hệ thống đặt giá trị cho cột BatDau thành thời điểm bắt đầu của giao dịch hiện tại (theo múi giờ UTC) dựa trên đồng hồ hệ thống và gán giá trị cho cột KetThuc thành giá trị lớn nhất là ‘9999-12-31’. Điều này đánh dấu hàng là mở.

Khi UPDATE: Hệ thống lưu trữ giá trị trước đó của hàng trong bảng lịch sử và đặt giá trị cho cột KetThuc thành thời điểm bắt đầu của giao dịch hiện tại dựa trên đồng hồ hệ thống. Điều này đánh dấu hàng là đã đóng, với khoảng thời gian được ghi lại mà hàng đó hợp lệ. Trong bảng hiện tại, hàng được cập nhật với giá trị mới và hệ thống đặt giá trị cho cột BatDau thành thời gian bắt đầu cho giao dịch dựa trên đồng hồ hệ thống. Giá trị cho hàng được cập nhật trong bảng hiện tại cho cột KetThuc vẫn là giá trị tối đa ‘9999-12-31’.

XÓA: Hệ thống lưu trữ giá trị trước đó của hàng trong bảng lịch sử và đặt giá trị cho cột KetThuc thành thời điểm bắt đầu của giao dịch hiện tại dựa trên đồng hồ hệ thống. Điều này đánh dấu hàng là đã đóng, với khoảng thời gian được ghi lại mà hàng trước đó hợp lệ. Trong bảng hiện tại, hàng bị xóa. Các truy vấn của bảng hiện tại sẽ không trả về hàng này. Chỉ các truy vấn xử lý dữ liệu lịch sử mới trả về dữ liệu mà một hàng được đóng.

MERGE: Thao tác hoạt động chính xác như thể có ba câu lệnh (một INSERT, một UPDATE và / hoặc một DELETE) được thực thi, tùy thuộc vào những gì được chỉ định là các hành động trong câu lệnh MERGE

Thời gian được ghi lại trong các cột datetime2 của hệ thống dựa trên thời gian bắt đầu của chính giao dịch. Ví dụ: tất cả các hàng được chèn trong một giao dịch sẽ có cùng thời gian UTC được ghi trong cột tương ứng với thời điểm bắt đầu khoảng thời gian SYSTEM_TIME

Truy xuất được dữ liệu

Câu lệnh SELECT * FROM … có một mệnh đề mới FOR SYSTEM_TIME với năm mệnh đề phụ cụ thể theo thời gian để truy vấn dữ liệu trên bảng hiện tại và bảng lịch sử.

Cú pháp câu lệnh SELECT mới này được hỗ trợ trực tiếp trên một bảng, được truyền thông qua nhiều phép nối và thông qua các khung nhìn trên đầu nhiều bảng tạm thời (các bạn tìm hiểu thêm, trong phạm vị bài này không mô tả).

Khi bạn truy vấn bằng mệnh đề FOR ​​SYSTEM_TIME sử dụng một trong năm mệnh đề phụ, dữ liệu lịch sử từ bảng tạm thời sẽ được đưa vào như hình bên dưới.

SELECT * FROM BangTest

  FOR SYSTEM_TIME

    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-04-01 00:00:00.0000000'

      WHERE MaSo = 1000 ORDER BY BatDau

Nguyễn Lộc

10:08 - 25/09/2022  |  737 lượt xem

Chia sẻ

TIN BÀI ĐỌC NHIỀU