ثبت یا ذخیره سابقه تغییرات در SQL SERVER با Temporal table

شاید برای شما هم پیش آمده باشد که به دنبال بهترین راه حل تاریخچه نویسی/لاگ برداری از تغییرات جداول دیتابیس در SQL باشید. در این مقاله بایک مثال کاربردی به تشریح این مهم پرداخته ایم.

برای این کار در دیتابیس های SQL SERVER می توان چند راه حل پیشنهاد داد:

1. استفاده از تریگر یا جاب برای نگارش یک نسخه متناسب با کد مد نظر خودمان، این کار ضریب خطای بالا و یا کاهش سرعت OLTP همراه است.

2. استفاده از CDC برای عدم I/O از فایل mdf وخواندن سریع اطلاعات توسط از ldf و در نهایت انتقال آنها با یک جاب به مقصد مورد نظر بدون درگیر کردن فایل اصلی دیتابیس، با سرعتی بالا این عملیات صورت میپذیرد اما باید دقت داشت که CDC ها عموما در فرآیند ETL کاربرد دارند و مدیریت JOB در استفاده از آنها کمی پیچیده تر است.

3. استفاده از temporal tables که از نسخه 2016 به بعد اختیار کاربران قرار گرفت. مدیریت ساده امکان ایجاد تغییرات سریع و پیاده سازی آسان، از خصوصیات مثبت این شیوه است. سرعت مطلوب تر نسبت به تریگرها، ضریب اطمینان بالاتر در ذخیره دیتا نسبت به روش CDC این روش را منطقی تر از سایر روش ها معرفی میکند. (برخی محدودیت ها در نسخه 2017 و 2019 برداشته شده است.)

در این مقاله سعی داریم به محدودیت ها و چرایی های استفاده از امکان جدیدتر شرکت مایکروسافت بپردازیم که به آن temporal table , system version و historical table نیز گفته می شود.

اگر قصد استفاده از جداول نسخه سازی یا همان Temporal Table را در SQL-SERVER داریم، باید ملاحظاتی را به علت ماهیت System-versioning بودن آنها لحاظ کنیم، که بصورت تیتر وارد به موارد زیر را اشاره شده :

یک temporal table باید دارای کلید اصلی برای حفظ یکپارچگی و ارتباط با جدول سوابق باشد و جدول سوابق نمی تواند دارای کلید اصلی باشد.

ستون های SysStartTime و SysEndTime برای ذخیره مقادیر شروع و پایان تغییرات هستند باید با نوع داده datetime2 ایجاد شوند. و آنها را به PERIOD FOR SYSTEM_TIME باید معرفی کرد همانند مثال زیر که ValidForm و ValidTo در آنها وجود دارد:

CREATE TABLE Employee
(
    [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

اگر یک جدول از نوع temporal را بخواهید تبدیل به دو جدول عادی کنید باید از دستور زیر استفاده کنید :

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF  )

اگر بخواهید یک جدول عادی را تبدیل به جدول temporal کنید از دستور زیر می توان استفاده کرد :

1. ستون های مورد نیاز را افزوده

2. فعال کردن جدول سوابق

ALTER TABLE Employee
ADD
    ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
GO
ALTER TABLE Employee
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

اگر میخواهید نام جدول سوابق و جدول اصلی را همانند انتخاب کنید باید از Schema متفاوت برای تعریف آنها استفاده کنید.

بصورت پیش فرض این جدوال PAGE compressed هستند.

محدودیت ها:

• اگر جدول کنونی شما پارتیشن شود، جدول سوابق در فایل گروه اصلی قرار خواهد گرفت، زیرا تنظیمات پارتیشنینگ به طور خودکار از جدول اصلی به جدول سوابق replicate نخواهد شد.
• امکان استفاده از FILETABLE و ستون هایی با نوع داده FILESTREAM ندارند.
• جداول از نوع node و edge را نمی توان به جداول temporal تغییر داد.
• در هنگام طراحی باید دقت داشته باشید که علیرقم پشتیبانی از نوع داده های varchar(max), varbinary(max), text و image در این جداول سرعت و عملکرد سیستم بشدت کاهش پیدا خواهد کرد.
• امکان استفاده از Linked Server نیز وجود ندارد و باید از همان دیتابیس با این جداول کار کنید. این مساله از جهتی مثبت و از جهت دیگر منفی است این موضوع را خودتان در زمان طراحی باید تشخیص دهید.
• این جداول نمی توانند شامل primary key, foreign key, table or column constraints باشند.
• اگر از این جداول در view ها استفاده کنیم، نمیتوان برای آن view این ستون ها را index نمود. (کوئری ها از SYSTEM_TIME استفاده می کنند. )
• امکان آنلاین با دستور WITH ONLINE = ON هیچ تاثیری برروی ستون های جداول سوابق ایجاد نمیکند و ویرایش ستون ها نیز در صورت فعال بودن این امکان هیچ تفاوتی با حالت غیرفعال بودن آن ندارد.
• دستورات TRUNCATE TABLE و DROP TABLE نیز در جداول temporal پشتیبانی نمی شوند.
• در جداول سوابق بصوررت مستقیم نمیتوان ویرایش داده انجام داد و گزینه Edit 200 row values نیز غیر فعال است.
• در این جداول گزینه Design table نیز دیگر وجود ندارد و برای تغییرات باید کوئری نوشته شود.
• امکان ON DELETE CASCADE و ON UPDATE CASCADE در این جداول پشتیبانی نمی شود. برای چنین امکانی باید از تریگر نوع after یا منطق برنامه نویسی استفاده نمایید. (این محدودیت از نسخه 2017 به بالا برداشته شده است.)
• تریگرهای نوع INSTEAD OF پشتیبانی نمی شوند.
• امکان replication با محدودیت هایی قابل دسترس است :

Always On: پشتیبانی کامل
Change Data Capture and Change Data Tracking : پشتیبانی فقط از جدول اصلی
Snapshot and transactional replication : فقط برای یک ناشر قابل استفاده است.
Merge replication : پشتیبانی نمی شود.

مزایا:

• در نگارش کوئری ها فقط از جدول اصلی استفاده می شود و برای استفاده از جدول سوابق کوئری temporal نوشته شود. این مساله موجب درک و خوانایی بهتر کوئری ها می شود.
• از این قابلیت در جداول in-memory می توان استفاده نمود. در مثال زیر ProductInventory یک جدول in-memory system-versioned است :

البته برای این منظور باید اطمینان حاصل کنید که دیتابیس شما از امکان in-memory پشتیبانی می کند یا خیر

اگر چنین فرضی را در نظر بگیریم با کد زیر میتوانیم چنین جدولی را ایجاد کنیم :

USE TemporalProductInventory
GO
BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory] SET (SYSTEM_VERSIONING = OFF)
    END
    DROP TABLE IF EXISTS [dbo].[ProductInventory]
       DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory]
END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId int NOT NULL,
    LocationID INT NOT NULL,
    Quantity int NOT NULL CHECK (Quantity >=0),
  
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,
    PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId)
)
WITH
(
    MEMORY_OPTIMIZED=ON,
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
)

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory ON [ProductInventoryHistory]
WITH (DROP_EXISTING = ON);

اگر بخواهیم برای مدل بالا یک روال ذخیره شده داشته باشیم از کوئری زیر می توان کمک گرفت :

CREATE PROCEDURE [dbo].[spUpdateInventory]
@productId int,
@locationId int,
@quantityIncrement int

WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English')
    UPDATE dbo.ProductInventory
        SET Quantity = Quantity + @quantityIncrement
           WHERE ProductId = @productId AND LocationId = @locationId

/*If zero rows were updated than this is insert of the new product for a given location*/
    IF @@rowcount = 0
        BEGIN
            IF @quantityIncrement < 0
                SET @quantityIncrement = 0
            INSERT INTO [dbo].[ProductInventory]
               (
                   [ProductId]
                    ,[LocationID]
                    ,[Quantity]
                )
                VALUES
                 (
                        @productId
                       ,@locationId
                       ,@quantityIncrement
        END
END;

• یک استراتژی بهینه برای index کردن می توان داشت که بشدت کارایی را بهبود می بخشد و آن هم استفاده از B-tree rowstore index در جدول اصلی و clustered columns store index and برای جدول سوابق است. در ضمن مقالات مایکروسافت به شدت توصیه می کنند که در جدول سوابق بر روی ستون های پایان ورژن گذاری مانند ValidTo و ValidForm در مثال بالا index گذاری صورت پذیرد. مطلوب است این index از نوع clustered باشد و در غیر اینصورت دست کم nonclustered آن استفاده شود.
• موارد زیر (objects/properties ) بعد از ایجاد یا تبدیل به جدول temporal به جدول سوابق منتقل نمیشود:

Period definition

Identity definition

Indexes

Statistics

Check constraints

Triggers

Partitioning configuration

Permissions

Row-level security predicates

• از این جدوال برای خروجی های power BI نیز می توان استفاده کرد :

در نهایت جداول سوابق را زیر مجموعه جداول اصلی می شوند نمیتوان به عنوان یک زنجیره اطلاعاتی با سایر جداول سوابق پیوند و ارتباط داد. البته این امکان از طریق جدول اصلی ممکن است. همانند مثال زیر میتوان از اطلاعات این سوابق بهرمند شد :

SELECT * FROM Employee
    FOR SYSTEM_TIME
      BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
        WHERE EmployeeID = 1000 ORDER BY ValidFrom;

و یا

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL WHERE
        EmployeeID = 1000 ORDER BY ValidFrom;

و یا

SELECT * FROM Employee FOR SYSTEM_TIME
    CONTAINED IN ('2014-01-01 00:00:00.0000000', '2015-01-01 00:00:00.0000000')
        WHERE EmployeeID = 1000 ORDER BY ValidFrom;

برگرفته از: doc.microsoft