معرفی ویژگی Temporal Tables در SQl Server 2016

مقدمه معرفی ویژگی Temporal Tables

SQL Server 2016 یک نوع جدیدی از جدول‌ها به نام System-Versioned Temporal Table معرفی کرده است که تاریخچه کامل تغیرات را درون خود ذخیره می‌کند و مدیریت اعتبار داده با خود سیستم می‌باشد. در نسخه‌های قبل از SQL Server 2016 جدول‌های کاربری تنها قادر به ذخیره‌سازی آخرین تغییرات بوده‌اند و این قابلیت را نداشته‌اند که مقادیر قبل از حذف و یا بروز رسانی را نشان دهند. با استفاده از جدول زمانی (Temporal Table) شما قادر خواهید بود که همانند قبل آخرین وضعیت یک سطر از داده را مشاهده نمایید بعلاوه اینکه قادر خواهید تمام تاریخچه آن سطر را نیز داشته باشید و تمام این موارد با استفاده از موتور خود SQL Server مدیریت می‌شود.

ساختار Temporal Table

هر جدول System-Versioned Temporal باید دو ستون از نوع DateTime داشته باشد که بصورت صریح در تعریف جدول تعریف شده باشد؛ ستون Period Start برای ذخیره زمان شروع سطر و ستون Period End که برای ذخیره زمان پایان سطر توسط خود سیستم مورد استفاده قرار می‌گیرد.
جدول اصلی (Main Temporal Table) تنها آخرین نسخه سطرهای جدول را در خود نگهداری می‌کند. این جدول بصورت خودکار به جدول دیگری با همان ساختار متصل است که در آن تغییرات سطرها بصورت خودکار ذخیره می‌شود. این جدول، جدول تاریخچه (History) نام دارد. زمانیکه شما یک جدول زمانی (Temporal Table) ایجاد می‌کنید، جدول تاریخچه آن نیز بصورت خودکار ایجاد می‌شود. اگر نام جدول تاریخچه در قسمت تعریف SYSTEM_VERSIONING مشخص نشده باشد، نام آن بر اساس الگو MSSQK_TemporalHistoryFor_<Object_id> نام گذاری می‌شود. به این نکته توجه داشته باشید که هیچ تغییری در داده‌های موجود در جدول تاریخچه نمی‌توان داد. جدول تاریخچه توسط سیستم مدیریت می‌شود و اگر شما سعی کنید داده‌ای را از جدول تاریخچه حذف نمایید با خطای “Cannot Delete Rows From A Temporal Table History Table ‘<TableName>’” مواجهه می‌شوید.

زمانیکه یک سطر Insert می‌شود، سیستم به ستون Period Start زمان جاری را بر اساس زمان سیستم اختصاص می‌دهد و به ستون Period End بالاترین مقدار نوع داده Datetime2 که برابر ۳۱-۱۲-۹۹۹۹ را اختصاص می‌دهد. اگر یک سطر مورد ویرایش قرار (update) گیرد، سیستم یک نسخه از آن سطر را در جدول تاریخچه کپی می‌کند و به ستون Period End، زمان جاری سیستم را اختصاص می‌دهد و سطر بسته می‌شود. مقدار ستون Period End در جدول اصلی بدون تغییر و با همان مقدار ۳۱-۱۲-۹۹۹۹ باقی خواهد ماند.
در زمان حذف (Deleting) ، سیستم آن سطر را در جدول تاریخچه کپی می‌کند و به ستون Period End، زمان جاری سیستم را اختصاص می‌دهد و سطر بعد از حذف از جدول اصلی، بسته می‌شود.

System-Versioning را می‌توان در زمان ایجاد یک جدول (CTREATE TABEL) و یا در زمان ویرایش جدول با استفاده از عبارت ALTER TABLE فعال کرد. همچنین آن جدول باید یک Primary Key به همراه دو ستون Not-Nullable Datetime2 با ویژگی GENERATE ALWAYS AS ROW START یا END داشته باشد. این ستون‌ها به عنوان پارامتر PERIOD FOR SYSTEM_TIME در زمان تعریف جدول مورد استفاده قرار می‌گیرد.

دستور زیر برای ساخت جدول Temporal_Table_Demo می باشد. این جدول شامل یک کلید اصلی به نام Demp_ID به همراه دو ستون زمان؛ TimeStart GENERATED ALWAYS AS ROW START و TimeEnd GENERATED ALWAYS AS ROW END که هر دو برای PERIOD FOR SYSTEM_TIME مورد استفاده قرار گرفته است. این جدول Temporal Table به جدول تاریخچه Dbo.Temporal_Table_Demo_History که در قسمت تعریف SYSTEM_VERSIONING مشخص شده است، ارتباط داده شده است.

CREATE TABLE dbo.Temporal_Table_Demo
(
[DempID] int NOT NULL PRIMARY KEY CLUSTERED
, [DepName] nvarchar(100) NOT NULL
, [DepLocation] varchar(100) NOT NULL
, [NumOfEmp] int NOT NULL
, [TimeStart] datetime2 (2) GENERATED ALWAYS AS ROW START
, [TimeEnd] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (TimeStart, TimeEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Temporal_Table_Demo_History));
GO

همانطور که در تصویر زیر نشان داده شده است عبارت System-Versioned در داخل پرانتز در ادامه اسم جدول ذکر گریده و هم چنین یک آیکون ساعت کوچک به آیکون آن اضافه شده است. جدول تاریخچه بصورت یک جدول مستقل نمی‌باشد و آن را می‌توان با Expand کردن جدول اصلی مشاهده کرد. بصورت پیش فرض یک Clustered Index برای جدول تاریخچه ایجاد می‌شود که شامل دو ستون Period Start و Period End می‌باشد.حال تصور کنید که می‌خواهید به یک جدول موجود قابلیت System-Versioned را اضافه نمایید. برای این کار می‌بایست دو ستون Period Start و Period End از نوع Not-Nullable Datetime2 را بصورت نمونه زیر به جدول اضافه نمایید:

ALTER TABLE dbo.AWBuildVersion ADD
[TimeStart] DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
[TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT,
PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]);
GO
ALTER TABLE dbo.AWBuildVersion
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.AWBuildVersion_History ) );
GO

تصور کنید همانطور که در تصویر زیر مشاهده می‌نمایید جدول تاریخچه Dbo.AWBuildVersion_History به جدول Dbo.AWBuildVersion اضافه گردید.
فعال کردن قابلیت System_Versioning بر روی جدول، محدودیتی برای اعمال تغییرات ساختاری به همراه نخواهد داشت. سیستم بصورت خودکار یک Schema Lock بر روی هر دو جدول اصلی و تاریخچه ایجاد می‌کند تا زمانیکه تغییرات بر روی هر دو جدول اعمال گردد. به این نکته توجه داشته باشید در زمان فعال بودن System_Versioning شما نمی‌توانید دستور Alter Table را برای یکسری از تغییرات همانند اضافه یا حذف ستون‌های Identity، Computed، Sparse، Column_Set یا RowGuidCol استفاده نمایید. برای اعمال این تغییرات شما باید ابتدا قابلیت System_Versioning را غیر‌فعال نمایید، سپس تغییرات خود را اعمال نمایید و سپس مجددا آن قابلیت را فعال نمایید. در دستورات زیر نحوه اضافه کردن یک ستون Identity جدید با جدول Temporal_Table_Demo نشان داد شده است.

ALTER TABLE dbo.[dbo].[Temporal_Table_Demo] SET ( SYSTEM_VERSIONING = OFF );
ALTER TABLE [dbo].[dbo].[Temporal_Table_Demo] ADD ID int IDENTITY (1,1);
ALTER TABLE dbo.[dbo].[Temporal_Table_Demo]
SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[dbo].[Temporal_Table_Demo_History]));

بررسی چند مثال

در ادامه می‌خواهیم با بررسی چند مثال قابلیت جدید فعال شده را بررسی نماییم. در ابتدا با استفاده از دستور زیر ۵ رکورد جدید به جدول Temporal_Table_Demo اضافه می‌نماییم.

INSERT INTO [dbo].[Temporal_Table_Demo]
(
[DempID]
,[DepName]
,[DepLocation]
,[NumOfEmp]
)
VALUES
(۱,'Accounting','Build1',10),
(۲,'HR','Build1',3),
(۳,'Recruitment','Build1',2),
(۴,'IT','Build2',7),
(۵,'Security','Build2',3)
GO

سپس با استفاده از دستور زیر مطمئن خواهیم شد که مقادیر مورد نظر به جدول اضافه شده اند:

SELECT
[DempID]
,[DepName]
,[DepLocation]
,[NumOfEmp]
,[TimeStart]
,[TimeEnd]
FROM [dbo].[Temporal_Table_Demo]
GO

نتیجه اجرا به شرح ذیل می‌باشد:
همانطور که قبلا توضیح داده شد، مقدار ستون Period Start توسط سیستم با مقدار Start Transaction Start Time و مقدار ستون Period End با ماکزیموم مقدار نوع داده Datetime2 مقدار‌دهی می‌شوند. به این نکته هم توجه نمایید که هیچ تغییری در جدول تاریخچه نمایش داده نمی‌شود به این دلیل که هنوز هیچ عملیاتی بر روی جدول اصلی آن انجام نشده است. اگر شما آن را در این مرحله مشاهده نمایید، آن را خالی خواهید دید.
حال تصور کنید می‌خواهیم سطر مربوط به DempID = 5 را با استفاده از دستور زیر حذف کنیم:

DELETE FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] WHERE DempID = 5

زمانیکه از جدول select گرفته می‌شود، مشخص می‌شود که سطر مورد نظر حذف شده و وجود ندارد که این وضعیت و حالت عادی آن می‌باشد:
اما اگر شما با استفاده از دستور زیر از جدول تاریخچه آن select بگیرید.

SELECT
[DempID]
,[DepName]
,[DepLocation]
,[NumOfEmp]
,[TimeStart]
,[TimeEnd]
FROM [dbo].[Temporal_Table_Demo_History]
GO

مشخص می‌شود که سطر حذف شده در جدول تاریخچه اضافه شده است و مقدار ستون Period End آن با زمان پر شده است.حال تصور کنید می‌خواهیم مقدار تعداد کارمندان دپارتمان شماره یک را با استفاده از دستور زیر بروز رسانی نماییم.

UPDATE [SQLShackDemo].[dbo].[Temporal_Table_Demo] SET NumOfEmp=11 WHERE DempID = 1

زمانیکه از جدول select گرفته می‌شود، مشخص می‌شود که سطر مورد نظر بروز رسانی شده است و مقدار ستون Period Start آن با مقدار جدید new transaction time بروز رسانی شده است:
و مقدار رکورد قبل از بروز رسانی در جدول تاریخچه درج گردیده است و مقدار ستون Period End آن با مقدار transaction current time پر شده است.
این دمو مزیت‌های امکانات جدید System_Versioning را به ما نشان می‌دهد. مزیت‌هایی نظیر Rollback کردن تغییرات و یا پیگیری تغییرات یک رکورد بدون نیاز به بازیابی Backup قدیمی بانک اطلاعاتی برای یک یا چند سطر.
بخش جدید تحت عنوان For SYSTEM_Time به دستورهای Select اضافه شده است. این بخش این امکان را می‌دهد که شما از داده‌های جدول به همراه جدول تاریخچه آن بتوانید select بگیرید. این دستور شامل ۵ زیر بخش به شرح ذیل می‌باشد که به شما امکانات زیادی برای استخراج داده‌ها بر اساس نیازتان می‌دهد.

•   AS OF
• CONSTAINED IN
• FROM … TO
• BETWEEN … AND
• ALL

SQL SERVER با معرفی قابلیت‌های جدید، DMVهای جدیدی را نیز برای مدیریت آن ها معرفی می‌نماید که می تواند به شما کمک‌های زیادی نماید. هم چنین در این ویژگی جدید شما می‌توانید برای ارتقا سرعت اجرا دستورات ایندکس‌هایی را بر روی جدول Temporal و History ایجاد نمایید. جهت کسب اطلاعات بیشتر می توانید به مرجع‌هایی که در بخش انتهایی معرفی شده است مراجعه نمایید.

سخن پایانی

System-Versioned Temporal Table یک نوع جدول جدید است که جدول تاریخچه‌ای همراه خود دارد. در این جدول تاریخچه، تاریخچه تغییرات تمام سطرها نگهداری می‌شود. شما می‌توانید برنامه خود را بر اساس جدول زمانی جهت ردیابی تغییرات و Rollback کردن هرگونه تغییر ناخواسته توسعه دهید بدون اینکه نیازی به بازیابی فایل‌های Backup وجود داشته باشد. نکته خوب این مسئله این است که مدیریت ذخیره‌سازی تغییرات توسط خود SQL Server می‌باشد و نیازی به کار اضافه‌ای سمت برنامه‌های شما نمی‌باشد. توجه نمایید همانند تمامی امکانات جدید معرفی شده، نیاز است که آن را در یک محیط تست با دقت تست نمایید و سپس آن را به محیط عملیاتی اعمال نمایید.