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

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

نوشته شده توسط: بهزاد عبداله زاده
۱۳ دی ۱۳۹۶
زمان مطالعه: 20 دقیقه
۳
(۴)

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

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

دوره آموزشی 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 می‌باشد و نیازی به کار اضافه‌ای سمت برنامه‌های شما نمی‌باشد. توجه نمایید همانند تمامی امکانات جدید معرفی شده، نیاز است که آن را در یک محیط تست با دقت تست نمایید و سپس آن را به محیط عملیاتی اعمال نمایید.

چه رتبه ای می‌دهید؟

میانگین ۳ / ۵. از مجموع ۴

اولین نفر باش

title sign
دانلود مقاله
معرفی ویژگی Temporal Tables در SQl Server 2016
فرمت PDF
8 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
بهزاد عبداله زاده
مقالات
2 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
بهزاد عبداله زاده
پروفایل نویسنده
title sign
دیدگاه کاربران

    • با سلام و خسته نباشید
      من یک جدول TEMPORAL TABLE دارم از حالت FOR SYSTEM_TIME BETWEEN استفاده می کنم اما در بعضی از موقع ها تاریخهایی که بین FOR SYSTEM_TIME BETWEEN می گذارم درست واکشی نمی کند یعنی REANGE بین تاریخها درست نمایش داده نمی شود.
      حالت FOR SYSTEM_TIME BETWEEN به چه صورت عمل می کند.
      با تشکر

    • با سلام و خسته نباشید
      من یک جدول TEMPORAL TABLE دارم از حالت FOR SYSTEM_TIME BETWEEN استفاده می کنم اما در بعضی از موقع ها تاریخهایی که بین FOR SYSTEM_TIME BETWEEN می گذارم درست واکشی نمی کند یعنی REANGE بین تاریخها درست نمایش داده نمی شود.
      حالت FOR SYSTEM_TIME BETWEEN به چه صورت عمل می کند.
      با تشکر

    • با سلام
      مقاله خیلی عالی بود و خیلی استفاده کردم
      ممنونم از شما

    • با سلام
      مقاله خیلی عالی بود و خیلی استفاده کردم
      ممنونم از شما

    • با سلام، تفاوتش به CDC چی هست؟ آیا از لحاظ پرفورمنس و فضای ذخیره سازی بهتر عمل می کنه؟

      • سلام
        موراد مربوط به Temporal Table
        ۱- به صورت سنکرون است ، یعنی همان لحظه تغییر دیتا در جدول سوابق اعمال می شود . این مورد در TPS بالا ممکن است مشکل ساز شود
        ۲-نیاز به اضافه کردن فیلدهایی ذر جدول اصلی دارید
        ۳- پیاده سازی راحتی دارد
        ۴- نوع اکشن انجام شده قابل تشخیص نمی باشد
        ….
        موارد مربوط به CDC
        ۱- به صورت آسنکرون است
        ۲- پیاده سازی پیچیده
        ۳- نوع اکشن قابل تشخیص است

    • با سلام و خسته نباشید // این قابلیت برای جدول های سنگین پیشنهاد میشه؟ و آیا ایجاد جدول History روی سرعت عملیات ها روی اون جدول تاثیر منفی نمیزاره؟

      • سلام
        وقت بخیر
        این عملیات به صورت Synchronized انجام می شود. این موضوع باعث میشه روی عملیات های Insert,Update,Delete یه سرباری داشته باشید .
        اگر این نوع عملیات در بانک شما خیلی خیلی زیاد است یه سرباری به ازای این قضیه دارید.
        در جهت افزایش کارایی می توانید File Group جداول سوابق را جدا کنید ….

    • Developer های دات نت تمایل دارند تمامی کارهای مربوط به دیتابیس را در سمت دات نت انجام دهند، حتی کارهای مربوط به ادمین اس کیو ال. در صورتی که دیتا بیس و Application دو مقوله ی جدا از هم هستند و کسی که به صورت حرفه ایی اس کیو ال کار می کند به دلیل گسترده بودن مطالب دیگر یک دات نت کار حرفه ایی نیست و بالعکس.

    • سلام
      ظاهراً خروجی PDF این مقاله مشکل داشته و بعد از اینکه چند بار دانلود و با دو سه نرم افزار مختلف آکروبات تست شد متاسفانه Error میدهد:
      .The file is damaged and could not be repaired
      لطفاً بررسی شود.
      با تشکر.

      • سلام منم همین مشکل رو دارم

        • با سلام و عرض ادب
          مقاله برای شما ایمیل شد همچنین مورد ذکر شده تست شده و مقاله بدون مشکل دانلود و باز می گردد لطفا کش دانلود منیجر خود را چک کنید.

      • با سلام و عرض ادب
        مورد اعلام شده بررسی و تست شد و فایل مقاله بدون هیچ مشکلی دانلود و باز می‌شود.
        لطفا قبل از دانلود کش مرورگر و نرم‌افزار دانلود خود را خالی کرده و سپس اقدام به دانلود کنید شاید مشکل از دانلود منیجر شما باشد.
        با تشکر

    • سلام
      ظاهراً خروجی PDF این مقاله مشکل داشته و بعد از اینکه چند بار دانلود و با دو سه نرم افزار مختلف آکروبات تست شد متاسفانه Error میدهد:
      .The file is damaged and could not be repaired
      لطفاً بررسی شود.
      با تشکر.

      • با سلام و عرض ادب

        مورد اعلام شده بررسی و تست شد و فایل مقاله بدون هیچ مشکلی دانلود و باز می‌شود.
        لطفا قبل از دانلود کش مرورگر و نرم‌افزار دانلود خود را خالی کرده و سپس اقدام به دانلود کنید شاید مشکل از دانلود منیجر شما باشد.

        با تشکر

    • سلام
      آیا entity framework از این ویژگی پشتیبانی می کند ؟

      • سلام دوست عزیز
        تنظیم این ویژگی در سطح SQL Server به ازای جداول شما می تواند انجام شود.
        حتی پس از ایجاد جدول می توانید با چند اسکریپت کوتاه این ویژگی را فعال کنیم. (فعال سازی بر عهده ادمین و..)
        در سیستم های حرفه ای و بزرگ خود ادمین بانک اطلاعاتی برای افزای کارایی و… کلی ویژگی می تواند در سطح SQL Server فعال کند که نیاز نیست Developer درگیر پیاده سازی آنها را با ORM و…. شود

  • 1
  • 2
ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image