รู้จักกับ Temporal Table

อีกหนึ่งคุณสมบัติ ของ Microsoft SQL Server 2016 ที่ผู้เขียนให้ความสนใจ ก็คือ Temporal Table หรืออีกชื่อหนึ่ง ก็คือ System-Versioned Table ขอผู้อ่านอย่าสับสนกับฐานข้อมูล TempDB ถึงชื่อคล้ายกัน แต่ไม่มีความเกี่ยวข้องหรือลักษณะคล้ายกันแต่อย่างใด กลไกของ Temporal Table นั้นจะมีการเก็บประวัติการเปลี่ยนแปลงข้อมูล ของ Table เอาไว้ สำหรับ Temporal Table นั้นมีอยู่ในมาตรฐาน ANSI SQL 2011 โดย Microsoft SQL Server 2016 ได้บรรจุมาตรฐานนี้ลงไปในผลิตภัณฑ์อย่างสมบูรณ์แบบ

ประโยชน์ของ Temporal Table มีดังนี้

1. เก็บการเปลี่ยนแปลงของข้อมูลเอาไว้ใช้ในการ Audit หรือทำ Computer Forensics

2. สามารถใช้สร้างข้อมูลในอดีตกลับคืนมาได้

3. นำมาคำนวณหาแนวโน้ม ของการเปลี่ยนแปลงได้

4. ใช้สร้าง Slow Changing Dimension (SCD) ในกรณีสร้าง Data Warehouse

5. ใช้กู้คืนข้อมูลกลับมา ในกรณีที่ข้อมูลเสียหาย

องค์ประกอบของ Temporal Table

เราจะรู้ได้ว่าข้อมูลไหนเป็นข้อมูลในอดีตโดยการเพิ่มคอลัมน์ที่มีชนิดข้อมูล เป็น datetime2 เข้าไป 2 คอลัมน์ ดังนี้

• คอลัมน์ที่ 1 (SysStartTime): เก็บเวลาเริ่มต้นเข้าสู่ Temporal Table ของแถวข้อมูลนั้น

• คอลัมน์ที่ 2 (SysEndTime): เก็บเวลาสิ้นสุดการอยู่ใน Temporal Table

การสร้าง Temporal Table

การสร้างตารางให้เป็น Temporal Table นั้นจำเป็นต้อง

1. ตารางดังกล่าวต้องมี Primary Key

2. ต้องมี 2 คอลัมน์ที่มีชนิดข้อมูลเป็น datetime2 เพื่อเก็บ SysStartTime และ SysEndTime

3. ไม่สามารถใช้งาน INSTEAD of Trigger บน Temporal Table และ History Table ได้ แต่ใช้ได้เฉพาะ AFTER Trigger บน Temporal Table เท่านั้น

4. ไม่สามารถใช้งาน In-Memory OLTP บนตารางนี้ได้

5. History Table ต้องไม่บังคับ Constraints ใด ๆ

6. ไม่สามารถใส่ค่าและปรับปรุงค่าในคอลัมน์ SysStartTime และ SysEndTime ได้เองโดยตรง

7. ไม่สามารถปรับปรุงแก้ไขข้อมูลที่อยู่ใน History Table ได้เองโดยตรง

ตัวอย่าง CREATE TABLE สำหรับ Temporal Table

CREATE TABLE HR.Employees
(
  EmpID       int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Employees PRIMARY KEY
, LastName    nvarchar(30) NOT NULL
, FirstName   nvarchar(30) NOT NULL
, Title       nvarchar(30) NOT NULL
, Salary      money NOT NULL
, Birthdate   datetime NOT NULL
, Hiredate    datetime NOT NULL
, Address     nvarchar(60) NOT NULL
, City        nvarchar(15) NOT NULL
, Region      nvarchar(15) NOT NULL
, ZipCode     nvarchar(10) NOT NULL
, Country     nvarchar(15) NOT NULL
, Phone       nvarchar(24) NOT NULL
, StartDate   datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, EndDate     datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH (SYSTEM_VERSIONING ON (HISTORY_TABLE = HR.EmployeesHistory));

จะเห็นว่าภายในตาราง HR.Employees (Temporal Table) มีตาราง HR.EmployeesHistory (History Table) ซ้อนอยู่อีกชั้นหนึ่ง และโครงสร้างของตารางทั้งสองเหมือนกันทุกประการ

ทดสอบ Insert ข้อมูล

INSERT INTO HR.Employees (LastName,FirstName,Title,Salary,Birthdate,Hiredate,Address,City,Region,ZipCode,Country,Phone)
VALUES
('Davis','Sara','CEO',18500.00,'Dec 12,1988','May 01,2012','7890 20th Ave','Seattle','WA','10003','USA','(206)555-0101')
,('Funk','Do','Vice President',15000.00,'Feb 19,1982','Aug 14,2012','9012 W Capital Way','Tacoma','WA','10001','USA','(206)555-0100')
,('Lew','Judy','Sales Manager',14500.00,'Aug 30,1993','Mar 1,2013','2345 Moss Bay Blvd','Kirkland','WA','10007','USA','(206)555-0103');

เมื่อ Insert ข้อมูลเข้าไป 3 แถว ข้อมูลจะถูกบันทึกใน HR.Employees (Temporal Table) โดยคอลัมน์ StartDate (SysStartTime) จะบันทึกเวลาที่ทำการ Insert ข้อมูล ส่วนคอลัมน์ EndDate (SysEndTime) จะบันทึกเวลาเป็นค่าสูงสุดของ datetime2 คือ 9999-12-31 23:59:59.9999999 ซึ่งหมายความว่าข้อมูลยังไม่สิ้นสุดการใช้งาน

ทดสอบ Update ข้อมูล

UPDATE HR.Employees
SET Salary = Salary * 1.1
WHERE HireDate < DATEADD(Year,-3,GETDATE());

ปรากฏว่ามี 2 แถวข้อมูลที่พนักงานถูกจ้างเข้ามาเกิน 3 ปี คือ EmpID เท่ากับ 1 และ 2 แถวข้อมูลที่ถูกปรับปรุงใน Temporal Table ได้บันทึกเวลาในคอลัมน์ StartDate (SysStartTime) เป็นเวลาใหม่ ส่วนใน History Table จะมีแถวข้อมูลเดิมก่อนการปรับปรุงเก็บไว้ โดยคอลัมน์ EndDate (SysEndTime) ได้บันทึกเวลาเดียวกับคอลัมน์ StartDate (SysStartTime) ใน Temporal Table เพราะเวลาที่ทำการ Update แถวข้อมูลใหม่ก็คือเวลาสิ้นสุดของข้อมูลเดิมนั่นเอง

ทดสอบ Delete ข้อมูล

DELETE HR.Employees
WHERE FirstName = 'Judy';

จะเห็นว่าแถวข้อมูลที่ถูกลบออกจาก Temporal Table ถูกบันทึกลงใน History Table พร้อมกับลงเวลาในส่วนของคอลัมน์ EndDate (SysEndTime) เป็นเวลาที่ถูกลบนั่นเอง

สรุป

จากผลการทดลองพบว่าการ Update และ Delete ทำให้เกิดการเก็บประวัติข้อมูลลงใน History Table โดยจะเก็บต่อ ๆ กันไปเป็น Time Series และเป็นประโยชน์ในหลาย ๆ ด้าน

ผู้เขียนหวังเป็นอย่างยิ่งว่าคุณสมบัติใหม่นี้ของ Microsoft SQL Server 2016 จะช่วยให้ผู้ที่ต้องการย้อนกลับไปดูการเปลี่ยนแปลงของข้อมูลผ่านช่วงเวลาต่าง ๆ ทำได้ดีขึ้นกว่าสมัยก่อนมาก