Temporal Table Nedir ve Nasıl Kullanılır?
Temporal table ile tablolarımızda yapılan insert, update ve delete işlemlerinin geçmişini tutabiliyoruz. Öncesinde verinin sadece en son halini tutabiliyorduk. Ve biri yanlış bir update bastığında backup’tan dönerek problemi çözebiliyorduk. Temporal tabloların diğer faydalarını aşağıda liste halinde bulabilirsiniz.
Temporal table, verilerin değişiminin geçmişini tutabildiğimiz için geçmişteki belli bir anda analiz yapmamıza da olanak sağlıyor.
Bütün temporal tablolarda datetime2 tipini sahip Period start column ve Period end column isimlerinde iki tane kolon bulunur. Bu kolonlara period kolonları denir. Bu period kolonları, sistem tarafından, bir satır değiştiğinde bu değişikliği ne süre ile geçerli olacağını tutmak için kullanılırlar. Aşağıda yaptığımız örnekte daha açık bir şekilde anlaşılacağını düşünüyorum.
Period start column‘da transaction’ın işleme başladığı an tutulur. Örneğin bir transaction başlattınız ve bu transaction içersinde birden fazla işlem yaptınız. İşlemin bitme süresi ne olursa olsun bütün işlemler için period start column’daki değer transaction’ın başlama zamanı olur.
Temporal tablolar, ayrıca tablonun şema yapısının aynısı başka bir tabloda tutar(history table). Temporal tablo’da update veya delete işlemi olduğunda bu değişiklikten önceki versiyonu history table’da tutulur.
Temporal tabloyu oluştururken, temporal tablo ile aynı şema yapısına sahip başka bir tablo oluşturarak o tabloyu history table olarak belirtebilirsiniz. Eğer belirtmezseniz history table sql server tarafından otomatik olarak oluşturacaktır. History tablolarda’da temporal tablolardaki gibi period kolonları bulunur.
Temporal Tabloların Faydaları:
- Uygulamacıların yaptığı işlemleri audit etmiş olursunuz. Böylelikle geçmişe dönük yapılan işlemler kayıt altına alınmış olur.
- Geçmişteki bir anda verinin o anki halini yeniden yapılandırabilirsiniz.
- KDS uygulamarı için verinin değişim eğrisini ve sürecini görebilirsiniz.
- Yanlış bir işlem yapıldıysa bu işlemi geri alabilirsiniz. Veritabanı yöneticileri olarak bizleri büyük bir yükten kurtaran bir çözüm olduğunu söyleyebilirim.
Bir örnek yaparak konunun daha iyi anlaşılacağını düşünüyorum.
Aşağıdaki gibi bir temporal tablo oluşturalım. Script’te gördüğünüz gibi tablonun ismi TemporalOrnek. History tablosu olarak’ta TemporalOrnek_HistoryTablosu ismini veriyoruz.
CREATE TABLE dbo.TemporalOrnek ( [ID] int NOT NULL PRIMARY KEY CLUSTERED , [Ad] VARCHAR(100) NOT NULL , [Soyad] VARCHAR(100) NOT NULL , [Maas] INT NOT NULL , [PeriodStart] datetime2 (2) GENERATED ALWAYS AS ROW START , [PeriodEnd] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalOrnek_HistoryTablosu));
Tablomuzu oluşturduktan sonra aşağıdaki script yardımıyla içine biraz kayıt insert edelim.
INSERT INTO [dbo].[TemporalOrnek]([ID],[Ad],[Soyad],[Maas]) VALUES (1,'Nurullah','CAKIR',1000), (2,'Hakan','GÜRBAŞLAR',1000), (3,'Faruk','ERDEM',1000), (4,'Dilara','AYDIN',1000)
Daha sonra neler olduğunu görmek için TemporalOrnek tablosunu ve onun history’sini tutan TemporalOrnek_HistoryTablosu tablosunu aşağıdaki gibi sorgulayalım.
SELECT * FROM [test].[dbo].[TemporalOrnek] GO SELECT * FROM [test].[dbo].[TemporalOrnek_HistoryTablosu]
Aşağıda da gördüğünüz gibi PeriodStart ve PeriodEnd kolonları otomatik olarak dolduruldu.
PeriodStart kolonuna transaction’ın başlangıç zamanını,
PeriodEnd kolonuna da ‘9999-12-31 23:59:59:59’ değerini set etti.
History tablosunda değişikliklerin önceki halinin tutulduğunu söylemiştik. Bu yüzden history tablosunun hala boş olduğunu görüyoruz.
Şimdi bir update işlemi yaparak history tablosunda verilerin versiyonunun nasıl tutulduğunu görelim.
Script’te de gördüğünüz üzere kendi maaşıma 1 TL zam yapıyorum. 🙂
UPDATE [dbo].[TemporalOrnek] SET [Maas] = 1001 WHERE Ad='Nurullah'
Update sorgusunu çalıştırdıktan sonra aynı select ifadelerini yeniden çalıştırıyorum.
Aşağıda gördüğünüz gibi Nurullah’ın olduğu satır için Maas 1001 oldu ve PeriodStart kolonunun değeri değişti.
History tablosuna da yeni bir kayıt geldiğini görüyoruz. Bu tabloda Maas’ın eski halini görüyoruz.
PeriodStart kolonunda bu satırın bu hali ile ne zaman set edildiğini,
PeriodEnd kolonunda da bu halinin ne zaman değiştiğini görüyoruz.
Aynı satırı bu sefer delete edelim ve select ifadelerini yeniden çalıştıralım bakalım neler olacak.
DELETE [dbo].[TemporalOrnek] WHERE Ad='Nurullah'
Gördüğünüz gibi veri her değiştiğinde history tablosuna bir satır ekleniyor.
Tablolarımızın son hali aşağıdaki gibi:
Aşağıdaki sorgu yardımıyla da iki tabloyu da kullanarak belirli bir tarih aralığındaki kayıtları versiyon bilgisiyle beraber sorgulayabilirsiniz.
SELECT * FROM [test].[dbo].[TemporalOrnek] FOR SYSTEM_TIME BETWEEN '2017-11-06 06:52:03.13' AND '2017-11-06 06:57:13.98' WHERE Ad = 'Nurullah' ORDER BY PeriodStart;
Aşağıdaki sorgu yardımıyla da iki tabloyu da kullanarak tüm kayıtları versiyon bilgisiyle beraber sorgulayabilirsiniz.
SELECT * FROM [test].[dbo].[TemporalOrnek] FOR SYSTEM_TIME ALL ORDER BY PeriodStart;
Temporal tabloları silmek için DROP TABLE script’ini çalıştırırsanız aşağıdaki gibi bir hata alırsınız.
Msg 13552, Level 16, State 1, Line 12
Drop table operation failed on table ‘test.dbo.TemporalOrnek’ because it is not supported operation on system-versioned temporal tables.
Silme işlemini gerçekleştirebilmek için öncelikle tablodaki versiyonlamayı kapatmalısınız. Aşağıdaki scrtip yardımıyla versiyonlamayı kapatarak temporal tablo’larınızı silebilirsiniz.
ALTER TABLE [dbo].[TemporalOrnek] SET ( SYSTEM_VERSIONING = OFF ) GO DROP TABLE [dbo].[TemporalOrnek] GO DROP TABLE [dbo].[TemporalOrnek_HistoryTablosu]
Default olarak history tablosunda periodstart ve periodend kolonları üzerinde clustered index vardır. Daha performanslı olması açısından history tablosunu manual oluşturup bu iki kolon üzerinde clustered columnstore index oluşturmanızı tavsiye ederim.
İlk etapta çok güzel bir özellik gibi gelmesine karşın her zamanki gibi bazı limitler ve kısıtlamalar kullanılabilirliğini sorgulamanıza yol açabilir. Aşağıda limitlerini ve kısıtlamalarını bulabilirsiniz.
Limitler ve Kısıtlamalar:
- Ana tabloda Primary Key tanımlı olmalı.
- Eğer ana tablo partition yapıda ise history tablosu default file group’ta oluşur.
- Ana ve history tablosu file table olamaz.
- Ana tablo (n)varchar(max), varbinary(max), (n)text, ve image gibi veri tiplerini desteklese de temporal tablo’yu bu veri tipleri ile oluşturduğunuzda ciddi bir storage maliyeti sizi bekliyor. Aynı zamanda bu veri tiplerinin büyüklüğünden dolayı performans sıkıntısı da yaşayabilirsiniz.
- History tablosu primary key, foreign key, table veya column constraint içeremez.
- Indexed view desteği yok.
- TRUNCATE ve DROP TABLE sistem versiyonlanması açık iken desteklenmiyor.
- Ana tabloda ON DELETE CASCADE ve ON UPDATE CASCADE desteği yok. Bu ifadelerin ne anlam ifade ettiğini “ON DELETE CASCADE ve ON UPDATE CASCADE Nedir ve Nasıl Kullanılır” isimli makalede bulabilirsiniz. SQL Server 2017 CTP 2.0’da bu destek geliyor.
- INSTEAD OF trigger desteklenmiyor. AFTER Trigger sadece Ana tablo için destekleniyor. “SQL Server Trigger Çeşitleri” isimli makalede trigger çeşitleri hakkında detaylı bilgi bulabilirsiniz.
- Change Data Capture ve Change Tracking’i sadece ana tablo için destekliyor. “Change Data Capture(CDC)” isimli makaleyi okumak isteyebilirsiniz.
- Merge replication desteği yok.
Memory Optimized Tabloları temporal olarak oluşturmak için de aşağıdaki script’i kullanabilirsiniz.
CREATE TABLE dbo.TemporalOrnekInMemory ( [ID] int NOT NULL PRIMARY KEY NONCLUSTERED , [Ad] VARCHAR(100) NOT NULL , [Soyad] VARCHAR(100) NOT NULL , [Maas] INT NOT NULL , [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL , [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL , PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.TemporalOrnekInMemory_HistoryTablosu ) );
