SQL Server 2016 – Temporal Tables

Merhaba,

Kanımca Yazılım Uzmanı olmanın temel ve olmazsa olmaz şartlarından biriside doğru bir raporlama ve takip mekanizması oluşturmaktan geçmektedir. Takip sürecini kontrol edecek olan bu mekanizma, hem detaylı raporlama hem de projenin yaşam döngüsünde atılan her adımı kayda alacak, yapılan tüm işlemleri gerektiği taktirde biz işin sorumlularına an be an listeyecek bir özellikte olmalıdır. Tabi ki de bu sistem projenin hem yazılım hem de veritabanı olmak üzere ikili saf olarak ortak bir organizasyonla gerçekleştirilecektir. Haliyle şuanda ilgili konumuzun esasını teşkil edecek olan nokta veritabanı kısmını ilgilendirmekte ve bu yapılanmaya SQL Server 2016 versiyonuyla gelen yeni bir teknikle yaklaşım sergileyeceğiz. Bu tekniğimize Temporal Tables diyeceğiz.

Temporal Tables özelliği, Zamansal Tablolar diye nitelendirebileceğimiz bir SQL Server 2016 yeniliğidir. Bu özelliğin özeti, veritabanında yapılan Data Manipulation Language(DML) işlemlerini raporlamamızı sağlayan bir yapıdır. Biz her ne kadar Temporal Tables olarak hitap edecek olsakta, internet yahut başka kaynaklarda “System-Versioned Table” şeklinde bir ifadede görebileceğinizden dolayı öncelikle şaşırmamanızı söylüyor, bu iki ifadeninde aynı özelliğin farklı sıfatları olduğunu belirtiyorum.

Hatırlarsanız eğer şuana kadar yaptığımız raporlama ve takip sistemlerinde izlediğimiz adımlar oluşturduğumuz tablolara özel “log” tabloları yahut dosyaları oluşturmaktan ibaretti. Yapılan her işlemi ya log tablolarına sorgular eşliğinde kayıt eder yahut trigger‘lar aracılığıyla takip sürecini gerçekleştirirdik.

Temporal Tables özelliği ile bu süreci hem teknik olarak daha kullanışlı, hemde pratikte daha maliyetli bir şekilde gerçekleştiriyor olacağız.

Temporal Tables özelliğinin bize sağlayacağı artıları kısaca maddelersek eğer;

• Tablomuzda bulunan kayıtların zaman içindeki değişikliklerinin(update) izlenmesi ve takip edilmesi,
• Tablo üzerinde yanlışlıkla yapılan delete ve update sorgularının geri getirilmesi,
• Bir verinin belirli bir zamana yahut zaman aralığına odaklı izlenebilmesi.

şeklinde olacaktır. Şimdi burada dikkatinizi çekmek istediğim bir nokta var. Farkındaysanız yukarıdaki maddeleri oldukça kısa tuttum. Bunun sebebi, fazla uzatmadan sonuncu maddeyi bir an önce gözlerinizin önüne getirmekti 🙂

Gözünüz sonuncu maddeye tekrardan gitmişken ben anlatımıma devam edeyim bari… Maddede anlattığım gibi tablomuzdaki herhangi bir verinin zamansal takibini gerçekleştirebiliyorsak eğer bu zamanlamayı tutacak bir alana ihtiyacımız olacaktır. Ee doğal olarak bu alanlarımız fiziksel tablomuzun içinde olacaktır. Ayrıca raporlama ve verilerin zamansal izlenimi için Temporal Tables özelliğinin getirisi olan ve History diye nitelendirdiğimiz tablonun birebir benzeri olan log dosyamızı oluşturacağız. Bu tablonun oluşturulmasıyla ilgili yazımızın devamında detaylı açıklama yapacağım için şimdilik bahsetmiyorum.

Şimdi şöyle düşüneceğiz. Bir verinin, zaman içindeki değişimini takip edeceksek eğer, o verinin;

• “İlk İşlem Tarihi”
• “Son İşlem Tarihi”

olmak üzere iki farklı bilgisini tutacağız. Bu bilgiler “datetime2” tipinde kolonlarda tutulacaktır. Örnek olarak ilk işlem tarihi için “StartDate”, son işlem tarihi için ise “EndDate” olmak üzere ikitane kolon oluşturmuş olalım.

Şimdi bir verinin zamansal takibinde bu iki kolon şu şekilde faaliyet gösterecektir.

• Veri İlk Kaydedildiğinde (Insert)
  Veri ilk kaydedileceği sırada “StartDate” kolonuna verinin kaydedilme tarih ve zamansal değerini, “EndDate” kolonuna ise daha veriyle ilgili başka işlem yapılmadığı için “datetime2” tipinin alabileceği son değer olan “9999-12-31 23:59:59.9999999” değerini tutacaktır.
• Verinin İlk Güncellendiğinde (Update)
  Veri güncelleneceği sırada benzer şekilde “StartDate” kolonuna verinin güncellenme tarih ve zamansal değerini, “EndDate” kolonuna ise yukarıda olduğu gibi “datetime2” tipinin alabileceği son veriyi yazacaktır. Lakin veri güncelleme işlemi yapıldığından dolayı Temporal Tables özelliği devreye girecek ve verinin bir önceki halini kayda alacaktır. İşte bu kayıt işleminde Temporal Tables’a özel bir History diye nitelendirdiğimiz tablomuzda işlem gerçekleştirilecektir. Burada fiziksel tablomuzla History log tablomuz arasında aşağıdaki bağ vardır;Verimiz ilk defa güncellendiğinden dolayı, güncellenmeden önceki hali History log tablosuna taşınmaktadır. Bu tabloda “StartDate” kolonuna ilgili verinin orjinal halindeki “StartDate” kolonundaki tarihi alacaktır. “EndDate” tablosunada o anki tarih ve zaman verisini yazacaktır.Yani ilk güncelleme durumunda History tablosundaki “StartDate” kolonu, fiziksel tablonun “StartDate” kolonundan geçirilecektir. Bunun sebebi, ilgili verinin önceden History tablosunda bir kaydının bulunmamasıdır. History tablosunda kaydı olan veriler(bir başka deyişle ilk güncellemeden sonraki güncellemeler) için aşağıdaki gibi bir mantıkta hareket edilmektedir.
• Verinin Sonraki Güncellemelerinde (Update)
  Verimiz ilkten sonraki tüm güncellemelerinde şöyle bir mantıkla History tablosuna işlenmektedir. Öncelikle güncellenen verinin fiziksel tabloda “StartDate” kolonuna o anki zaman ve tarih değeri girilirken, “EndDate” kolonuna ise “datetime2” değerinin alabileceği en son değer girilir. History tablosunda ise, “StartDate” kolonu ilgili verinin alınan en son kaydının “EndDate” kolonundaki veriyle güncellenmektedir. “EndDate” kolonu ise o anki zaman ve tarih değerleriyle güncellenmektedir.History tablosundaki sonraki güncelleme işlemlerini aşağıdaki şemada somut bir şekilde görebilirsiniz.
  

Temporal Tables özelliği ile verilerimizi kolaylıkla takip edebilmekteyiz. Tabi ki de bu işlemleri uygulamalı bir şekilde pratik olarak ele alacağız. Ama daha o adıma gelmeden Temporal Tables özelliğini kullanabilmemiz için nelere dikkat etmemiz, hangi şartları gözetmemiz gerektiğine bir göz atalım.

• Temporal Tables ile raporlama ve takip mekanizması oluşturacağımız tablolarda Primary Key tanımlanmış bir kolon olması gerekmektedir. Bu şekilde yaşam döngüsünde hangi verinin değişime uğradığını bu Primary Key aracılığıyla ayırt edebileceğiz.
• Makalemizde de değindiğimiz gibi takibini sağlayacağımız ve kaydını tutacağımız tablomuzun içerisinde bir başlangıç birde bitiş niteliğinde iki adet “datetime2” tipinden kolonların bulunması gerekmektedir.
• Linked Server üzerinde Temporal Tables kullanılmamaktadır.
• History tablomuzda constraint yapılarının hiçbirini uygulayamayız.
• Herhangi bir verinin değişiklik periodunu tutması amacıyla oluşturmuş olduğumuz “datetime2” tipinden kolonlar üzerinde manuel değişiklikler yapmamız gerekmektedir.
• Eğer bir tabloda Temporal Tables aktifse o tabloda Truncate işlemi gerçekleştiremiyoruz.
• History tablosunda direkt olarak DML işlemleri gerçekleştiremiyoruz.
• Temporal Tables özelliğinin bulunduğu bir tabloda Computed Column(Hesaplanmış Kolon) tanımlayamıyoruz.

İşte Temporal Tables özelliğini teorik olarak burada noktalıyoruzzz…

Şimdi sıra bu anlattıklarımız çerçevesinde pratik uygulamalar geliştirmeye geldi.

Öncelikle bir Temporal Tables oluşturmayı görelim.

Temporal Tables Oluşturma

Temporal Tables oluşturmak bildiğimiz tablo oluşturma yöntemiyle aynıdır.

CREATE TABLE DersKayitlari
(
	----------1. Kısım----------
	DersID INT PRIMARY KEY IDENTITY(1,1),
	Ders NVARCHAR(MAX),
	Onay BIT,
	----------1. Kısım----------
	----------2. Kısım----------
	StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
	EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
	----------2. Kısım----------
	----------3. Kısım----------
	PERIOD FOR SYSTEM_TIME(StartDate, EndDate)
	----------3. Kısım----------
)
----------4. Kısım----------
WITH(SYSTEM_VERSIONING = ON)
----------4. Kısım----------

Şimdi yukarıda neler yapmış olduk inceleyelim.

• 1. Kısım
  Tablomuzda bulunacak olan normal kolonlarımızı oluşturuyoruz.
• 2. Kısım
  Temporal Tables’da verilerin izlenme periyodunu tutmamızı sağlayacak olan, makalemizin içeriğinde oldukça vurguladığımız “StartDate” ve “EndDate” kolonlarımızı oluşturuyoruz. Bu kolonlara dikkat ederseniz eğer ikiside “datetime2” veri tipindendir. Ayrıca “GENERATED ALWAYS AS ROW START” ve “GENERATED ALWAYS AS ROW END” komutlarıylada işaretlenerek, History tablosunda hangi kolon başlangıç hangi kolon bitiş zamanını tutacağını belirtmiş oluyoruz.
• 3. Kısım
  PERIOD FOR SYSTEM_TIME komutu ile “StartDate” ve “EndDate” kolonlarının takip sürecinde periyodik işlevde kolonlar olduğunu belirtiyoruz. Yani bu işlemden sonra ilgili kolonlar Temporal Table işlemlerine özel işaretlenmiş oluyorlar.
• 4. Kısım
  WITH(SYSTEM_VERSIONING = ON) komutu ile artık oluşturacağımız bu tablonun System-Versioned Table özelliğini aktifleştirmiş oluyor, anlayacağınız tablomuzu Temporal Table olarak ayarlamış oluyoruz.

Bu şekilde T-SQL komutlarımızı Execute ederek sorgumuzu işleyelim. Alacağımız sonuc aşağıdaki gibi olacaktır.

Gördüğünüz gibi “DersKayitlari” isimli tablomuz oluşturulmuş içerisine de “MSSQL_TemporalHistoryFor_402100473” isimli bir History tablosu eklenmiştir. “DersKayitlari” tablosu ile History tablomuzun kolonları raporlama ve takip sürecini net bir şekilde gerçekleştirebilmemiz için bire bir aynıdır. Tek fark History de Primary Key kolonu yoktur.

Ayriyetten biliyorum ki, sizlerde benim gibi History tablosunun isminden rahatsız olmuşsunuzdur. Biz “DersKayitlari” tablomuzu Temporal tablo olarak ayarlarken oluşturulacak History tablosuna bir isim vermediğimiz için sistem rastgele isimlendirme yapmıştır. Eğer ki, History tablosunun ismini belirlemek istiyorsanız aşağıdaki gibi WITH(SYSTEM_VERSIONING = ON) komutuna HISTORY_TABLE ile ekleme yapınız.

CREATE TABLE DersKayitlari
(
	DersID INT PRIMARY KEY IDENTITY(1,1),
	Ders NVARCHAR(MAX),
	Onay BIT,

	StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
	EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,

	PERIOD FOR SYSTEM_TIME(StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DersKayitlariLog))

Şauana kadar yeni oluşturulan bir tabloyu Temporal tablo olarak ayarlamayı gördük. Şimdi ise önceden oluşturulmuş bir tabloyu Temporal olarak ayarlamayı görelim.

Var Olan Tabloyu Temporal Tables Olarak Ayarlama

CREATE TABLE DersKayitlari
(
	DersID INT PRIMARY KEY IDENTITY(1,1),
	Ders NVARCHAR(MAX),
	Onay BIT
)

Varsayalım ki elimizde “DersKayitlari” tablomuz en sade şekilde olsun.

Eğer bu tabloyu Temporal yapmak istiyorsak bir duruma dikkat etmeliyiz. Tablo içerisinde veri var mı? yok mu? Eğer varsa yeni eklenecek olan “StartDate” ve “EndDate” kolonlarına boş kalamayacakları için varsayılan değerlerin belirtilmesi gerekecektir. Yok eğer veri yoksa bu işlemi düşünmemize gerek olmayacaktır.

Öncelikle tablomuz içerisinde veri olmadığını varsayalım.

ALTER TABLE DersKayitlari
ADD
StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartDate, EndDate)

Bu şekilde çalışarak periyodik kolonlarımızı ekleyebiliriz.

Veri olduğunu varsayarsak eğer,

ALTER TABLE DersKayitlari
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)

şeklinde çalışarak Default constraint aracılığıyla eklenecek kolonlarımıza varsayılan değerleri girilmelidir.

Velhasıl hangi şekilde olursa olsun kolonlar eklendikten sonra Temporal tablo özelliği aktifleştirilmelidir.

ALTER TABLE DersKayitlari
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DersKayitlariLog))

Evet…

Bu işlemden sonra elimizdeki kolonumuz Temporal olarak değiştirilmiş olacaktır.

Şimdi sıra Temporal tablomuzun çalışma mekanizmasını denemeye geldi.

Çalışma Mekanizması

Ekran alıntısından gördüğünüz gibi tablolarımızı sorguladığımızda içerikleri resimdeki gibi gelmektedir.
Şimdi aşağıdaki sorguları çalıştıralım.

Update DersKayitlari Set Ders = 'Matematik2' where DersID = 1
Update DersKayitlari Set Ders = 'Fizik2' where DersID = 2
Update DersKayitlari Set Ders = 'Kimya2' where DersID = 3
Update DersKayitlari Set Ders = 'Kimya3' where DersID = 3
Update DersKayitlari Set Ders = 'Biyoloji2' where DersID = 4
Update DersKayitlari Set Ders = 'Biyoloji3' where DersID = 4

Sorgularımızı gönderdikten sonra alacağımız neticeyi aşağıdaki ekran görüntüsünden inceleyiniz.

Gördüğünüz gibi verilerimizde yapılan değişiklikler adım adım History tablosuna aktarılmakta, raporlandırılmakta ve takip mekanizması sıkıntısız çalışmaktadır.

Şimdi ise History tablomuzda bir güncelleme gerçekleştirelim.

Gördüğünüz gibi herhangi bir Temporal tablonun History tablosu içerisinde güncelleme vs. gibi işlemler gerçekleştirememekteyiz. Eğer History tablosu içerisinde güncelleme yapmak için Temporal özelliğini aşağıdaki gibi pasifize etmeliyiz…

ALTER TABLE DersKayitlari SET ( SYSTEM_VERSIONING = OFF )

Bu sorguyu çalıştırdıktan sonra History tablosu üzerinde rahatça işlem gerçekleştirebiliriz.

Şimdide sıra Temporal tablo içerisinde History tablosuna özel bir filtreleme işlemi gerçekleştirmeye geldi.

History Tablosuna Özel Temporal Tabloyu Sorgulama

Eğer ki Temporal tabloyu, History tablosundaki periyotlara göre sorgulamak istiyorsak aşağıdaki gibi bir sorgu kullanabiliriz.

Select * from DersKayitlari dk inner join DersKayitlariLog dkl on dk.DersID = dkl.DersID where DAY(dkl.StartDate) >= DAY(CAST('Tarih' as datetime2)) or  DAY(dkl.EndDate) <= DAY(CAST('2016-09-26 06:23:45.8195851' as datetime2)) 

Tabi ki de sorgulamayı bu şekilde tercih edebileceğimiz gibi Temporal tablolara özel olarak gelen parametreler aracılığıyla, tablomuzu History tablosunun periyoduna özel bir şekilde rahatça sorgulatabilmekteyiz.

Şimdi bu parametreleri aşağıdaki sorgular neticesinde gelen veriler üzerinde inceleyelim…

StartDate >= datetime and EndDate < datetime

SELECT * FROM DersKayitlari
FOR SYSTEM_TIME AS OF '2016-09-26 06:22:53.5432528'
WHERE DersID = 3

start_datetime >= datetime and end_datetime < datetime

SELECT * FROM DersKayitlari
FOR SYSTEM_TIME FROM '2016-09-26 06:23:45.7982247' TO '2016-09-26 07:47:49.4820110'
WHERE DersID = 3

start_datetime >= datetime and end_datetime < datetime

start_datetime >= datetime and end_datetime < datetime

Şimdi ise databaseimizde bulunan Temporal tablolara nasıl erişebileceğimizi inceleyelim.

Temporal Tabloları Listelemek

Aşağıdaki vermiş olduğum sorgularla sistemimizdeki Temporal tabloları listeletebiliriz.

SELECT
NAME, OBJECT_ID, TEMPORAL_TYPE_DESC, HISTORY_TABLE_ID, OBJECT_NAME(HISTORY_TABLE_ID) as
[History Tablo Adı]
FROM SYS.TABLES WHERE OBJECT_NAME(HISTORY_TABLE_ID) IS NOT NULL

ya da

SELECT
NAME, OBJECT_ID, TEMPORAL_TYPE_DESC, HISTORY_TABLE_ID, OBJECT_NAME(HISTORY_TABLE_ID) as
[History Tablo Adı]
FROM SYS.TABLES WHERE TEMPORAL_TYPE_DESC = 'SYSTEM_VERSIONED_TEMPORAL_TABLE'

Evet, benim için uzun ve zahmetli bir yazı oldu…
Sizin için okuması zevkli, bol katkılı ve faydalı bir içerik olacağını umuyorum.

Sonraki yazılarımızda görüşmek üzere diliyorum…
İyi çalışmalar…