SQL Server 2016 (CTP2.1) System-Versioned Tables (Temporal Tables)
Bu post’ta uzun süredir yazmayı istediğim fakat zaman bulup yazamadığım bir konu olan SQL Server 2016 ile hayatımıza girecek Temporal Tables veya diğer ismi ile System-Versioned Tables üzerinde duracağız. Bu konuya NDA kapsamında izin verildiği kadarı ile değiniyor olacağım.
CTP 2.0 veya CTP 2.1 üzerinde bu özelliğin demo’larını yapabilirsiniz. Unutmayın bu yazıyı CTP 2.1 mevcutken, RTM çıkmadan yazıyorum.
Temporal Tables (System-Versioned Tables) nedir, ne işe yarar?
Bu özellik sayesinde tablolarınızdaki verilerin zaman içerisindeki değişimlerini gözlemleyebilir, istediğiniz bir zaman dilimi ile mevcut zamandaki halleri ile karşılaştırabilirsiniz. Kısacası bu özellik, gerçekleştireceğiniz insert-update-delete işlemlerinizi tarihsel olarak kayıt altına alır.
Microsoft, geçtiğimiz günlerde SQL Server 2016 RTM olduğunda bu özelliğin ismini artık System-Versioned Table olarak kullanacağını duyurdu. Eğer belirtilenin aksi olursa bu yazıyı güncelleyeceğim.
Ne zaman ve kimler ihtiyaç duyar?
Cevabım; herkes ve her zaman! Bu özellik olmadığında bu özelliğin yarattığı yapıyı zaten yazdığımız trigger’lar, job’lar ve T-SQL’ler ile oluşturuyorduk. Çünkü bir veritabanı yöneticisi her zaman için verilerin zaman içerisindeki değişimlerini gözlemlemek ister. Bu özelliği detaylandırmak istersek bence auditing işlemlerimizde, organizasyonel haklarda (Örneğin müdür yetkisinde olan kişilerin geçmişteki verilerin değişiklik analizine erişim yetkisinin olup, altında çalışan kişilerin sadece güncel veriye erişim yetkisinin olması gibi.) ve verilerin belirli zaman dilimi içerisindeki kıyaslanmasında kullanılabilir.
Sytem-Versioned Tablo’ların oluşturulması
Bu tabloları oluşturup kullanmaya başlamadan önce limitlerine göz gezdirmekte fayda var. Çünkü normal tablolardan farklı olarak bazı limitler mevcuttur.
- History tablolar her zaman için system-versioned olarak belirlenen tablonun bulunduğu veritabanı üzerinde oluşturulur.
- System versioning yapılan tablolar kesinlikle truncate edilemez.
- History sorgulamalar linked-server üzerinden gerçekleştirilemez. Kesinlikle aynı instance üzerinden yapılmalıdır.
- System versioning yapılan tablolarda kesinlikle Primary Key olmalıdır (Transactional replication mantığı gibi düşünebilirsiniz). Fakat History tablolarda Primary Key, Foreign Key veya Column Constraint kullanılmaz.
- In-Memory OLTP altyapısındaki Memory-Optimized Tables, system versioning yapılamaz.
- History tablolar üzerinde direkt mofidikasyon yapılamaz.
- Change Data Capture ve Change Data Tracking sadece system versioning tablolarda kullanılabilir. History tablolarda kullanılmaz.
- System-Versioned yapı, AlwaysOn Availability Group mimarisinde de kullanılabilir.
- History tablolarda trigger kullanımı mümkün değildir.
- History tablolarda index kullanımı mümkündür. Columstore index’leride bu tablolarda kullanabilirsiniz.
Öncelikle bu tablolarda kesinlikle 2 adet DATETIME2 kolon tipinde ve NOT NULL olan kolona ihtiyaç duyulmaktadır. NULL olarak oluşturmayı denerseniz aşağıdaki hata ile karşılaşacaksınızdır.
Örnekleri AdventureWorks2014 veritabanı altında gerçekleştireceğim. İlk olarak System-Versioned olarak yeni bir tablo yaratıyorum.
|
|
USE AdventureWorks2014 GO CREATE TABLE Kullanicilar ( KullaniciID char(10) NOT NULL PRIMARY KEY CLUSTERED, Departman varchar(50) NOT NULL, BaslangicZamani datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, BitisZamani datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (BaslangicZamani, BitisZamani) ) WITH (SYSTEM_VERSIONING = ON); |
Oluşturduğumuz tablo Object Explorer’da aşağıdaki şekilde gözükmektedir.
Yukarıdaki örnekte SQL Server, history tablosunu kendi oluşturdu. Bunu manuel olarak biz de oluşturabiliriz. Bence herzaman için bunu tercih etmekte fayda var çünkü otomatik oluşturulan history tablolarının isimleri çok anlamlı olmuyor. History tablolar, MSSQL_TemporalHistoryFor_ diye başlarlar. Sonundaki ID ise System-Versioned olan eşleniğinin object_ id’sidir. İsterseniz bu şekilde de oluşturabilirsiniz. Fakat her seferinde SELECT OBJECT_NAME(711673583) diyip root tabloya erişme ihtiyacı duyabilirsiniz. Bu yüzden aynı örneği bu sefer history tablosunuda kendim oluşturarak tekrarlıyorum. Önceki tabloyu drop ederek başlıyoruz.
|
|
USE AdventureWorks2014 GO DROP TABLE Kullanicilar GO |
System-Versioned olan bir tabloyu yukarıdaki gibi drop etmek isterseniz aşağıdaki hata mesajı ile karşılaşırsınız. Bu duruma yukarıdaki limitasyon maddelerinde değinmiştik.
Bu yüzden öncelikle System-Versioned olan tablonun SYSTEM_VERSIONING özelliğinin OFF’a getirilip, önce parent (System-Versioned) tabloyu sonra da child (History) tabloyu drop etmelisiniz.
|
|
USE AdventureWorks2014 GO ALTER TABLE Kullanicilar SET (SYSTEM_VERSIONING = OFF) GO DROP TABLE Kullanicilar GO DROP TABLE MSSQL_TemporalHistoryFor_711673583 GO |
Öncelikle History tablosunu yaratıyorum. Ardından System-Versioned olan tabloyu yaratacağım ve birbirine bağlayacağım.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE AdventureWorks2014 GO CREATE TABLE Kullanicilar_History ( KullaniciID char(10) NOT NULL, -- Yukarıda belirtmiştik History tabloda PK kullanılmaz. Departman varchar(50) NOT NULL, BaslangicZamani datetime2 NOT NULL, BitisZamani datetime2 NOT NULL ) GO CREATE TABLE Kullanicilar ( KullaniciID char(10) NOT NULL PRIMARY KEY CLUSTERED, Departman varchar(50) NOT NULL, BaslangicZamani datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, BitisZamani datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (BaslangicZamani, BitisZamani) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Kullanicilar_History, DATA_CONSISTENCY_CHECK = ON )) GO |
Object Explorer’da System-Versioned olan tabloya baktığımızda history tablonun belirlediğimiz isimde oluşturulduğunu görüyoruz.
System-Versioned yapılan bir tabloya sağ tıklayıp Design diyemezsiniz! System-Versioned tabloların Right-Click menüsünde Design yer almaz.
Bu sefer aynı yapıyı mevcut bir tabloda uygulayalım. Bunun için AdventureWorks2014 veritabanı altındaki Person schema’sı içerisinde yer alan 909 satır veriye sahip olan BusinessEntityContact tablosunu kullanacağım. İlk olarak tablonun kolon yapısına aşağıdaki kod ile bakıyoruz.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE AdventureWorks2014 GO SELECT col.TABLE_SCHEMA AS [Schema] , col.TABLE_NAME AS [Table] , col.COLUMN_NAME AS [Column] , col.ORDINAL_POSITION AS [OrdinalPosition] , col.COLUMN_DEFAULT AS [DefaultSetting] , col.DATA_TYPE AS [DataType] , col.CHARACTER_MAXIMUM_LENGTH AS [MaxLength] , col.DATETIME_PRECISION AS [DatePrecision] , CAST(CASE col.IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END AS bit)AS [IsNullable] , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']') , col.COLUMN_NAME, 'IsIdentity')AS [IsIdentity] , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']') , col.COLUMN_NAME, 'IsComputed')AS [IsComputed] , CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS [IsPrimaryKey] FROM INFORMATION_SCHEMA.COLUMNS AS col LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA , o.name AS TABLE_NAME , c.name AS COLUMN_NAME , i.is_primary_key FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.objects AS o ON i.object_id = o.object_id LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id WHERE i.is_primary_key = 1) AS pk ON col.TABLE_NAME = pk.TABLE_NAME AND col.TABLE_SCHEMA = pk.TABLE_SCHEMA AND col.COLUMN_NAME = pk.COLUMN_NAME WHERE col.TABLE_SCHEMA = 'Person' AND col.TABLE_NAME = 'BusinessEntityContact' ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION; |
Kolon yapısını öğrendikten sonra bu tablonun history halinin create script’ini hazırlıyorum. Bunu yaparken yukarıda bahsettiğim o iki kolonu da ekliyorum.
|
|
USE AdventureWorks2014 GO CREATE TABLE Person.BusinessEntityContactHistory ( BusinessEntityID int NOT NULL, PersonID int NOT NULL, ContactTypeID int NOT NULL, rowguid uniqueidentifier ROWGUIDCOL NOT NULL, ModifiedDate datetime NOT NULL, SysStartTime datetime2 NOT NULL, SysEndTime datetime2 NOT NULL ) GO |
History tablosunu oluşturduk fakat parent’ı ile bunu ilişkilendirmemiz gerekiyor. Bunun için BusinessEntityContact tablosunu alter ederek history tablosuna eklediğimiz SysStartTime ve SysEndTime kolonlarını ekliyoruz.
|
|
USE AdventureWorks2014 GO ALTER TABLE Person.BusinessEntityContact ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(), SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2), PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) GO GO |
Fakat bu kolonları eklemek ile tablo System-Versioned hale gelmiyor. Bütün bu işlemleri gerçekleştirdikten sonra parent tablo üzerinde SYSTEM_VERSIONING ‘i aşağıdaki gibi açıp, history tablo’sunun hangisi olduğunu göstermemiz gerekiyor.
|
|
USE AdventureWorks2014 GO ALTER TABLE Person.BusinessEntityContact SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Person.BusinessEntityContactHistory)) GO |
Object Explorer’a tekrardan bakıyoruz ve Person schema’sı altındaki BusinessEntityContact tablosunun System-Versioned hale geldiğini görüyoruz.
System-Versioned Tablo ile History Tablonun Sorgulanması ve Kullanımı
T-SQL ile System-Versioned yapıyı parent/child ilişkisinde listelemeniz mümkündür. Bunun için sys.tables altındaki history_table_id kolonunu recursive olarak dönüp object_id ile birleştirmeniz yeterli olacaktır.
|
|
USE AdventureWorks2014 GO SELECT T1.name as TemporalTableName, SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema, T2.name as HistoryTableName, SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema, T1.temporal_type_desc FROM sys.tables T1 LEFT JOIN sys.tables T2 ON T1.history_table_id = T2.object_id WHERE T1.temporal_type <> 0 AND T1.temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE' AND T1.name = 'BusinessEntityContact' GO |
System-Versioned tabloların yukarıda bahsettiğimiz period kolonlarını listelemek için sys.periods altındaki start_column_id ve end_column_id ile sys.columns altındaki object_id’leri birleştirerek aşağıdaki şekilde kullanabilirsiniz.
|
|
USE AdventureWorks2014 GO SELECT P.name as PeriodName, T.name as TemporalTableName, c1.name as StartPeriodColumnName, c2.name as EndPeriodColumnName FROM sys.periods P INNER JOIN sys.tables T ON P.object_id = T.object_id INNER JOIN sys.columns c1 ON T.object_id = c1.object_id AND p.start_column_id = c1.column_id INNER JOIN sys.columns c2 ON T.object_id = c2.object_id AND p.end_column_id = c2.column_id GO |
Bu bölümde yapacağımız demo’da yukarıda yaptığımız son örnek üzerinden devam edeceğiz. Yukarıdaki örnekte mevcut bir tabloyu System-Versioned hale getirdik. Şuan için History tablosu boş durumda. Aşağıdaki gibi kontrol ediyoruz.
|
|
USE AdventureWorks2014 GO SELECT * FROM Person.BusinessEntityContactHistory GO |
History tablosunu test edebilmek için BusinessEntityContact tablosunda bir update uyguluyorum.
|
|
USE AdventureWorks2014 GO UPDATE Person.BusinessEntityContact SET ContactTypeID = 19 WHERE ContactTypeID = 17 GO |
54 satırlık bir veriyi değiştirmiş olduk. Tekrardan History tabloyu sorguluyorum.
|
|
USE AdventureWorks2014 GO SELECT * FROM Person.BusinessEntityContactHistory GO |
Gördüğünüz gibi update ettiğimiz o 54 satırlık veri History tablosuna gelmiş durumda. Şimdi ise Sytem-Versioned tablo (parent) üzerinden ContactTypeID’si 17 olanları sorguluyorum.
|
|
USE AdventureWorks2014 GO SELECT * FROM Person.BusinessEntityContact WHERE ContactTypeID = 17 GO |
Sorgu sonucunda herhangi bir veri karşımıza çıkmıyor çünkü yukarıda ContactTypeID’si 17 olanları 19 olarak update etmiştik. Verinin önceki halini görüntülemek için veriyi update ettiğiniz zamanı SYSTEM_TIME fonksiyonunda kullanıp, sorgunuzu çekmelisiniz.
|
|
USE AdventureWorks2014 GO SELECT * FROM Person.BusinessEntityContact FOR SYSTEM_TIME AS OF '2015-07-09 20:24:09.7825344' WHERE ContactTypeID = 17 GO |
Böylelikle yukarıda update ettiğimiz 54 satırlık ve eski ContactTypeID’si 17 olan verileri rahatıkla listeleyebildik. Eğer örneği kendiniz yaparken History tabloda kayıtların hangi tarih ile oluşturulduğunu hatırlamıyorsanız aşağıdaki gibi alabilirsiniz.
|
|
USE AdventureWorks2014 GO SELECT TOP(1) SysStartTime FROM Person.BusinessEntityContactHistory GO |
System-Versioned Tablo ile History Tablo Üzerinde Index ve İstatistik İşlemleri
Bu konuda herhangi bir kısıtlama bulunmamaktadır. Hem System-Versioned tabloda hem de History tablo’da index, istatistik create edip, index’leri reorganize/rebuild edebilirsiniz.
|
|
USE [AdventureWorks2014] GO CREATE CLUSTERED INDEX ClusteredIndex ON Person.BusinessEntityContactHistory (BusinessEntityID) ON [PRIMARY] GO ALTER INDEX ALL ON Person.BusinessEntityContact REBUILD GO ALTER INDEX ALL ON Person.BusinessEntityContactHistory REBUILD GO CREATE STATISTICS Stat1 ON Person.BusinessEntityContact(BusinessEntityID) GO |
History Tablo’larda Silme İşlemleri
History tablolar üzerinde direkt silme işlemi yapamazsınız. Aşağıdaki gibi denediğinizde bunun mümkün olmadığının bilgisi karşınıza çıkacaktır.
|
|
USE [AdventureWorks2014] GO DELETE FROM Person.BusinessEntityContactHistory WHERE ModifiedDate IN ('2011-12-23 00:00:00.000', '2011-04-25 00:00:00.000', '2011-12-24 00:00:00.000') GO |
Bunun için ilk olarak System-Versioned tablo üzerinden bu yapıyı yani SYSTEM_VERSIONING özelliğini kapamamız gerekmektedir.
|
|
USE AdventureWorks2014 GO ALTER TABLE Person.BusinessEntityContact SET (SYSTEM_VERSIONING = OFF) GO |
Bu özelliği kapadıktan sonra silme işlemini tekrar çalıştırıyoruz ve silme işleminin başarılı olduğunu görüyoruz.
Silme işlemini gerçekleştirdikten sonra parent tablo üzerinde SYSTEM_VERSIONING ‘i yeniden başlatıyoruz.
|
|
USE AdventureWorks2014 GO ALTER TABLE Person.BusinessEntityContact SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=Person.BusinessEntityContactHistory, DATA_CONSISTENCY_CHECK = OFF)) GO |
Post Details
