T-SQL Temporal Tables(System Versioned Table)

Temporal Tables (Sistem Versiyonlu Tablolar), SQL Server 2016 ve sonraki sürümlerde sunulan, verilerin zaman içindeki değişimlerini otomatik olarak takip eden bir özelliğidir. Bu tablolar, veri değişikliklerinin tarihçesini saklayarak “zamanda yolculuk” yapabilme imkanı sağlar. Bir raporlama aracı olarak kullanılabilir. Herhangi bir veri kaybı için bir önlem mekanizması olarak da kullanılır.

Temporal Tables yapısında Mevcut verilerin tutulduğu Ana tablo ve Normal bir tablo gibi çalışır ancak zaman bilgisi içerir. Bir diğer tablo türümüz ise History Table ana tablodaki verilerin geçmiş durumlarını saklar. Sistem tarafından otomatik olarak yönetilir.

Temporal Tables yapılarında önemli olan zaman kavramları mevcuttur. Bu zaman kavramları DATETIME2 türündendir. Bunlar aşağıdaki gibi iki zaman kavramıdır.

SysStartTime: Kaydın geçerli olduğu başlangıç zamanı
SysEndTime: Kaydın geçerli olduğu bitiş zamanı

Temporal olarak oluşturulacak tabloda bir primary key olmalıdır. Hangi verilerin değişime uğrayacağı bu primary key aracılığıyla ayırt etmiş olacağız. Geçmiş tablosu doğrudan değiştirilemez (INSERT, UPDATE, DELETE) TRUNCATE TABLE komutu kullanılamaz.

Yeni Tablo Oluşturma

CREATE TABLE Product
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    CategoryID INT,
    Price DECIMAL(10,2) NOT NULL,
    StockQuantity INT NOT NULL,
    Discontinued BIT DEFAULT 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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Varolan Tabloyu Temporal Table’a Dönüştürme

Tablomuzda veri yoksa aşağıdaki şekilde yapılmaktadır. İlk komut çalıştırıldıktan sonra ikinci komut çalıştırılır.

--Eğer kolonlarımızın içerisinde veri yoksa
ALTER TABLE Product
ADD 
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

--Sistem versiyonlamayı aktif edelim
ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Eğer tablomuzda veri varsa aşağıdaki yapıda oluşturulmaktadır. İlk komut çalıştırıldıktan sonra ikinci komut çalıştırılır.

ALTER TABLE Product
ADD
    StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT CAST('1900-01-01 00:00:00.0000000' AS DATETIME2),
    EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
    PERIOD FOR SYSTEM_TIME (StartDate, EndDate);
--Sistem versiyonlamayı aktif edelim
ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Yukarıdaki tablomuzu oluşturduktan sonra History tablomuzun mevcut tablomuzun altında oluştuğunu görebiliriz.

--Ürün ekleme
INSERT INTO Product (ProductID, ProductName, CategoryID, Price, StockQuantity)
VALUES (1, 'Wireless Mouse', 5, 29.99, 100);
--İki güncelleme yapılır.
UPDATE Product SET Price = 24.99 WHERE ProductID = 1;
UPDATE Product SET StockQuantity = 85 WHERE ProductID = 1;

Yukarıdaki update işlemi yapıldığında history tablosunda SysEndTime değeri verinin güncellendiği anlık getdate() değeri ile set edilmektedir. SysStartTime parametresi ise insert veya update ifadesinin tablomuza ilk yapıldığı hali olarak görülmektedir. Test işlemini sıralı yaparak bu kavramı daha iyi anlayabiliriz. Aşağıdaki resimde dikkat edilirse 2. update sonrasında değer history tablosuna kaydedildiği zaman history tablosunda bulunan ilk değerin SysEndTime değeri 2. update edilen değerin history tablosunda SysStartTime değeri olmuş olacaktır. Bu güncelleme değerleri ilgili kolon üzerinde birbirini takip etmektedir.

Ürünü tamamen silelim

DELETE FROM Product WHERE ProductID = 1

History tablosunda silinen değerin geldiğini görmüş oluyoruz.

Yukarıdaki oluşturulan tablomuzu sorgulama komutları aşağıdaki gibidir.

1. Belirli Bir Zamandaki Veriler (AS OF)

-- 15 Haziran 2023 saat 10:00'daki veriler
SELECT * FROM Product
FOR SYSTEM_TIME AS OF '2023-06-15 10:00:00'
WHERE ProductID = 1;

2. İki Tarih Arasındaki Tüm Değişiklikler (FROM )

SELECT * FROM Product
FOR SYSTEM_TIME FROM '2023-06-15 10:00:00' TO '2023-07-15 10:00:00'
WHERE ProductID = 1;

3. Between komutu ilede aynı işlemi yapabiliriz.

-- 1 Ocak 2023 ile 1 Temmuz 2023 arasındaki tüm versiyonlar
SELECT * FROM Product
FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-07-01'
WHERE CategoryID = 5;

4. Belirli Aralıkta Başlayan ve Biten Kayıtlar (CONTAINED IN)

-- Sadece 2023'ün 1. çeyreğinde aktif olan kayıtlar
SELECT * FROM Product
FOR SYSTEM_TIME CONTAINED IN ('2023-01-01', '2023-03-31')

Temporal Table’ı Pasif Hale Getirme:

-- Sistem versiyonlamayı kapatma
ALTER TABLE Product SET (SYSTEM_VERSIONING = OFF);

Bu yapı ile log tablosu normal bir tablo yapısına gelmektedir.

Temporal özelliğini kalıcı olarak kaldırmak için:

1. Adım: Sistem Versiyonlamayı Kapat

ALTER TABLE Product SET (SYSTEM_VERSIONING = OFF);

2. Adım: PERIOD Tanımını Kaldır

ALTER TABLE Product DROP PERIOD FOR SYSTEM_TIME;

3. Adım: Zaman Sütunlarını Sil

ALTER TABLE Product DROP COLUMN SysStartTime, SysEndTime;

4. Adım: Geçmiş Tabloyu Sil

DROP TABLE ProductHistory;

Temporal Tables, veri değişikliklerinin izlenmesi gereken uygulamalarda oldukça kullanışlıdır. Özellikle finansal sistemler, insan kaynakları uygulamaları ve veri denetimi gerektiren projelerde sıklıkla kullanılır.

Temporal tabloların performansı etkileyen temel noktaları şunlardır:

Yazma Yükü: Her UPDATE veya DELETE işleminde SQL Server, eski veriyi otomatik olarak History tablosuna yazar. Bu, tek bir I/O yerine iki katı I/O demektir.
Page Splitting: Geçmiş tablosuna sürekli veri eklendiği için disk üzerinde fragmantasyon oluşabilir.
Depolama Alanı: Zamanla geçmiş tablosu asıl tablodan çok daha büyük hale gelir. Eğer geçmiş tablosu üzerinde doğru indeksleme yapılmazsa, sorgu performansı düşer.

Schema Modification: Şema değişikliği yapmak normal tablolara göre bir tık daha zahmetlidir (Sistem versiyonlamayı kapatıp açmak gerekebilir).

Yukarıdaki işlemi kullanmayıp Trigger kullanmak isteyebilirsiniz. Bu yöntem ise Transaction bitmesine rağmen trigger’ın bitmesini bekler. Bu yapıda kilitlenmelere blocking durumuna sebep olmaktadır. En maliyetli yöntemdir. Change Data Capture(CDC) yapısı kullanıldığında yapılan her şeyi maliyetsiz bir şekilde kaydeder. Bu yapının en büyük dezavantajı veritabanı tabanının ldf dosyasını maksimum boyut olun 2 TB boyutuna ulaştırır. Buda veritabanı üzerinden işlem yapmanızı engeller.

Peki ne zaman log tablosu veya cdc kullanılmalı. Eğer saniyede binlerce UPDATE alıyorsanız ve bu güncellemelerin geçmişini tutmak sistemi kilitliyorsa, CDC (Change Data Capture) kullanmalısınız. CDC, transaction log’ları asenkron okuduğu için ana tablo üzerinde kilit oluşturmaz.

Eğer amacınız “Veri ne zaman değişti, eski hali neydi?” gibi sorulara yanıt vermekse ve veri hacminiz devasa (terabaytlarca günlük değişim) değilse Temporal Table en iyi çözümdür. SQL Server bu işlemi trigger’dan çok daha hızlı yapar çünkü kernel seviyesinde optimize edilmiştir. Performans anlamında sıkıntı yaşatabilir.

Başka makalede görüşmek dileğiyle..

Ey iman edenler, sabredin ve sabırda yarışın, (sınırlarda) nöbetleşin. Allah’tan korkun. Umulur ki kurtulursunuz. Al-i İmran Suresi, 200. ayet

Bir yanıt yazın