อีกหนึ่งคุณสมบัติ ของ 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. ใช้กู้คืนข้อมูลกลับมา ในกรณีที่ข้อมูลเสียหาย

หมายเหตุ : 
สำหรับการสร้าง Data Warehouse สามารถเรียนรู้เพิ่มเติมได้ใน หลักสูตร SQL Server Business Intelligence ของ 9ExpertTraining

องค์ประกอบที่ต้องมีสองส่วนด้วยกันคือ Temporal Table ที่เก็บค่าปัจจุบันในแต่ละแถวข้อมูลเอาไว้ และ History Table ซึ่งเก็บข้อมูลในอดีตของแต่ละแถวข้อมูลเอาไว้ 

เราจะรู้ได้ว่าข้อมูลไหนเป็นข้อมูลในอดีตโดยการเพิ่มคอลัมน์ที่มีชนิดข้อมูล เป็น datetime2 เข้าไป 2 คอลัมน์ ดังนี้
คอลัมน์ที่ 1 : เก็บเวลาเริ่มต้นเข้าสู่ Temporal Table ของแถวข้อมูลนั้น เรียกว่า SysStartTime
คอลัมน์ที่ 2 : เก็บเวลาสิ้นสุดการอยู่ใน Temporal Table เรียกว่า SysEndTime

การสร้าง 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. ทั้ง Temporal Table (ข้อมูลปัจจุบัน) และ History Table (ข้อมูลอดีต) ไม่สามารถเป็น FileTable ได้
6. History Table (ข้อมูลอดีต) ต้องไม่บังคับ Constraints ใด ๆ
7. ไม่สามารถใส่ค่าและปรับปรุงค่าในคอลัมน์ SysStartTime และคอลัมน์ SysEndTime ได้เองโดยตรง
8. ไม่สามารถปรับปรุงแก้ไขข้อมูลที่อยู่ใน History Table (ข้อมูลอดีต) ได้เองโดยตรง

ตัวอย่างการสร้าง Temporal Table

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

 

จะได้ตารางออกมา 2 ตารางคือ HR.Employees (Temporal Table) และ HR.EmployeesHistory (History Table)
โดยที่คอลัมน์ StartDate ทำหน้าที่เป็น SysStartTime และคอลัมน์ EndDate ทำหน้าที่เป็น SysEndTime เราสามารถดูผลลัพธ์ของคำสั่งผ่าน SQL Server Management Studio (SSMS) ได้โดยเข้าไปที่ฐานข้อมูล Test DB ดังแสดง

จะเห็นว่าภายในตาราง HR.Employees (Temporal Table) มีตาราง HR.EmployeesHistory (History Table) ซ้อนอยู่อีกชั้นหนึ่ง และโครงสร้างของตารางทั้งสองเหมือนกันทุกประการ
เพื่อให้เข้าใจการทำงาน ผู้เขียนจะสาธิตการใช้งาน Temporal ที่สร้างขึ้น โดยการปรับปรุงข้อมูลผ่านคำสั่ง Insert, Update และ Delete
ทดสอบกรณี Insert ข้อมูล
ผู้เขียนทดลองทำการ Insert ข้อมูลด้วยคำสั่งต่อไปนี้

USE TestDB;
GO
 
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. E., Apt. 2A','Seattle', 'WA', '10003', 'USA','(206) 555-0101')
,('Funk', 'Do', 'Vice President, Sales', 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) และตาราง HR.EmployeesHistory (History Table) ได้ผลลัพธ์ดังนี้

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

ทดสอบกรณี Update ข้อมูล
ผู้เขียนทดลองทำการ Update ข้อมูลที่ได้ Insert เข้ามาก่อนหน้าด้วยคำสั่งต่อไปนี้

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

ปรากฏว่ามี 2 แถวข้อมูลที่พนักงานถูกจ้างเข้ามาเกิน 3 ปี คือ EmpID เท่ากับ 1 และ 2 เมื่อสืบค้นข้อมูลจากทั้งตาราง HR.Employees (Temporal Table) และตาราง HR.EmployeesHistory (History Table) ได้ผลลัพธ์ดังนี้

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

USE TestDB;
GO
 
UPDATE HR.Employees
SET Phone='(206) 505-9898'
WHERE FirstName='Sara' AND LastName='Davis';

คำสั่งก่อนหน้าเป็นการ Update ข้อมูลให้กับแถวข้อมูล EmpID=1 ซึ่งเคยปรับปรุงไปแล้วครั้งหนึ่ง และครั้งนี้เป็นการปรับปรุงครั้งที่สอง เมื่อสืบค้นข้อมูลจากทั้งตาราง HR.Employees (Temporal Table) และตาราง HR.EmployeesHistory (History Table) ได้ผลลัพธ์ดังนี้

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

ทดสอบกรณี Delete ข้อมูล
สุดท้ายผู้เขียนได้ทดลอง Delete ข้อมูลด้วยคำสั่งต่อไปนี้

USE TestDB;
GO
 
DELETE HR.Employees
WHERE FirstName='Judy' AND LastName='Lew';

เมื่อทำการ Delete ข้อมูลด้วยคำสั่งก่อนหน้าจะเป็นการลบแถวข้อมูลที่ EmpID เท่ากับ 3 ออกจากตาราง เมื่อสืบค้นข้อมูลจากทั้งตาราง HR.Employees (Temporal Table) และตาราง HR.EmployeesHistory (History Table) ได้ผลลัพธ์ดังนี้

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

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

SELECT * FROM HR.Employees  
FOR SYSTEM_TIME   
BETWEEN '2016-06-30 13:00' AND '2016-06-30 14:00'  
WHERE EmdID = 1 ORDER BY StartDate DESC;  

จะเป็นการสืบค้นประวัติข้อมูลของพนักงานที่มี EmpID เท่ากับ 1 ตั้งแต่เวลา 13.00 น. ไปจนถึง 14.00 น. ของวันที่ 30 มิถุนายน 2559 มีการปรับปรุงอะไรบ้าง ผลลัพธ์ที่ได้คือ

ผู้เขียนหวังเป็นอย่างยิ่งว่าคุณสมบัติใหม่นี้ของ Microsoft SQL Server 2016 จะช่วยให้ผู้ที่ต้องการย้อนกลับไปดูการเปลี่ยนแปลงของข้อมูลผ่านช่วงเวลาต่าง ๆ ทำได้ดีขึ้นกว่าสมัยก่อนมาก ที่มีความพยายามทำกันอาทิ การ Backup เก็บข้อมูลไว้ทุก ๆ สัปดาห์ ซึ่งจะได้ข้อมูลมาเพียงข้อมูลที่เปลี่ยนแปลงไปในแต่ละสัปดาห์ แต่ในระหว่างสัปดาห์เกิดการเปลี่ยนแปลงอะไรไปบ้างก็อาจทำไม่ได้ หรือการออกแบบ Slowly changing dimension Type 2 ที่จำเป็นต้องสร้าง Surrogate Key ขึ้นมาใช้เพื่อให้สามารถเพิ่มแถวข้อมูลที่มีการปรับปรุงข้อมูลเข้าไปเพิ่มใน Dimension  Table เดียวกันก็อาจไม่มีความจำเป็นอีกต่อไป

หมายเหตุ : 

• สำหรับการสร้าง Backup/Restore อย่างมีประสิทธิภาพ สามารถเรียนรู้เพิ่มเติมได้ใน หลักสูตร SQL Server Administration ของ 9ExpertTraining 
• การสร้าง Surrogate Key สามารถเรียนรู้เพิ่มเติมได้ใน หลักสูตร SQL Server Business Intelligence ของ 9ExpertTraining

Reference

• https://msdn.microsoft.com/en-us/library/dn935015.aspx