Sql Server 2016 Temporal Tables

SQL SERVER 2016 TEMPORAL TABLES

 

    Sql Server 2016’nın yeni özelliklerinden biri de Temporal Tables. Temporal Table’lar temporary table değillerdir, Sql Server 2016 ile birlikte gelecek olan yeni bir özelliktir. Temporal table’lar Sql Server’daki verilerin tarihçesini bir tabloda tutan özelliktir. Temporal table’lar ilk olarak 2011 de duyurulmasına rağmen, Sql Server 2016 da yeni bir özellik olarak gelecektir. Sql Server 2016 CTP2 versiyonunda bu özellik test kullanıcılarına sunulmuştur. Diğer bir adına da system-versioned table da denmektedir.

    Temporal table’lar, sorgu güncellemeyi ve veri silmeyi mümkün hale getirmiştir, normal tablolarda sadece mevcut veriler gelirken, temporal tablolarda verini geçmişine gidilebilmektedir. Örnek olarak bir kolondaki değeri 10, bu değeri 20 yaptığınızda, normal tabloda sadece 20 değerini görebilirsiniz. Temporal tabloda ise eski değer 10’u geri almak mümkün olmaktadır. Tablonun geçmişi veya tarihçesi de diyebiliriz buna, tutulduğu için ilk değere dönülebilmektedir.

    Temporal tabloların kullanım tiplerini inceleyelim

  • Denetim: Girilen verilerin tüm değerlerini kontrol amaçlı kullanılır.
  • Değişen boyutlar: Kolonlardaki değişen değerlerin takibinin yapılması.
  • Kayıt bozulmalarını düzeltmek: Veri kaydederken bir hata sonucu veri kaydedilmediğinde, bu veri history tabledan alınıp ana tabloya kayıt edilebilir.

Temporal tablolar, bir kaydın sürekli kaydını tutabilmektedir, bunun anlamı, bu kayıt ne zaman silindiğini veya güncellendiğini tarih-saat olarak tutmaktadır. Bu özellik aslında kanıt niteliğide taşımaktadır. Önceki Sql Server versiyonlarında bu gibi kayıtları tutmak için, ayrı bir tabloya log kayıtları yazılmaktaydı ve bu işlemler çok zaman almaktaydı.

    Dikkat edilmesi gereken diğer bir konuda, Temporal tablolar, change data capture özelliği ile karıştırılmamalıdır. Change data capture özelliği, değişen verileri belli bir zaman aralığı içinde transaction log’dan bakabilmektedir. Temporal tablolar ise değişiklikleri history table’a yazarak daha uzun zaman aralığında saklayabilmektedir. Bu iki alana System_Time periyod alanları denmektedir.

    Temporal table oluşturmak için aşağıdaki gereksinimlerin hazır olması gerekmektedir.

  • Primary key’in tanımlı olması
  • İki tane datetime2 formatında kolon tanımlanması gerekiyor. İlki başlangıç diğer bitiş zamanını belirtmek için kullanılır. İstenildiğinde bu iki alan hidden flag ile işaretlenip gizlenebilir.
  • Trigger kullanımına izin verilmemektedir.
  • In-memory OLTP tablolarda kullanılamaz.

Bazı kısıtlamalarda şu şekildedir,

  • Temporal ve history tabloları Filetable olamazlar
  • History tablolarında constraint olamaz
  • Insert ve update yapıldığında, System_Time periyod alanları referans olarak gösterilemez
  • History tablosundaki veri güncellenemez

 

Aşağıdaki script ile basit bir temporal tablo oluşturuyoruz,

CREATE
TABLE dbo.TemporalTablo

    (ID int
primary
key

    ,alan1 int

    ,alan2 int

    ,alan3 AS alan1 * alan2

    ,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 tablosu için farklı bir isim verilmez ise aşağıdaki gibi Sql Server ilk olarak instance adını daha sonra TemporalHistoryfor_****** olarak otomatik isimlendirme yapacaktır.

SSTT-1

History tablosu, ana tablo gibi özdeş kolonlardan oluşmaktadır, fakat constraintler içermemektedir. Kendi indeksleri ve statisticleri vardır. History table’da clustered coloumstore indeksler oluştururak, performans olarak iyileştirmeler yapılabilmektedir.

Yeni bir tabloyu history table olarak oluşturabiliyoruz, varolan bir tabloyu da history table’a dönüştürmek mümkündür.

Varolan bir history table üzerine, yeni bir history table eklendiğinde, veya history table değerlerinin sıfırlanması için de bu işlem yapılmak istenebilir, bu gibi durumlarda verilerin çakışmaması için DATA_CONSISTENCT_CHECK komutu kullanılarak verilerin doğruluğu kontrol edilebilmektedir.

Temporal tabloların işlevselliğine bakalım, nasıl veri kayıt ediliyor, nasıl düzenleme yapılıyor. İlk olarak tablomuza birkaç veri kayıt edelim.

INSERT
INTO dbo.TemporalTablo(ID, alan1, alan2)

VALUES         (5,10,15)

        ,(20,25,30)

        ,(35,40,45);

 

Kayıt ettiğimi değerleri kontrol ediyoruz

SELECT
*
FROM dbo.TemporalTabl

SSTT-2

Buradaki SysEndTime’In pek bir anlamı yoktur, sadece maksimum datetime2 değerini getirmektedir.

Tablo üzerinde silme işlemi yapalım

DELETE
FROM dbo.TemporalTablo

WHERE ID = 20

 

Tablo üzerinde güncelleme işlemi yapalım

UPDATE dbo.TemporalTablo

SET alan1 = 45

WHERE ID = 5

 

Ana tablonun son hali

SSTT-3

History tablosu

SSTT-4

ID’si 5 olan satırın, alan1 alanı saat 07:07 de ilk kaydın girilmiş olduğu, saat 07:21’de ise değerin 45 yapıldığı görülmektedir.

    Bir tabloda temporal table özelliği aktif olduğunda, schema değişikliklerine kısıtlı olarak izin vermektedir. İzin verilen değişiklikler, Alter Table… Rebuild, Create Index, Create Statistics.

    Örnek olarak DROP TABLE dbo.TemporalTablo yaptığımızda hata verecektir.

Temporal bir tabloya yeni bir kolon eklemek için, ilk önce System versioning özelliğini kapatmamız gerecektir. Aşağıdaki script ile Temporal table özelliğini kapatıyoruz.

ALTER
TABLE dbo.TemporalTablo SET (SYSTEM_VERSIONING = OFF);

 

Aşağıdaki şekilde görüldüğü gibi, history tablosunu normal bir tabloya dönüştürdü

SSTT-5

Temporal table özelliğini kapattığımızda, history tablomuz silinmiyor, geçmiş tüm veriler durmaktadır. Ana tablo üzerinde değişiklikler yapıldıktan sonra, tekrardan temporal table aktif edilebilir.

Tekrar temporal table özelliğini aktifleştirmek için aşağıdaki scripti çalıştırmak yeterli olacaktır. Consistency check on veya off yapılabilir.

ALTER
TABLE dbo.TemporalTablo SET (SYSTEM_VERSIONING = ON


(HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_565577053,DATA_CONSISTENCY_CHECK=ON))

 

    Temporal tablolar Sql Server 2016 ile gelen yeni bir özellik, verilerde istenmeden silinmeler yaşandığında, history tablosundan geçmiş verilere bakılabilmektedir.