Pazartesi , 20 Kasım 2017

SQL Server 2016 Temporal Table – Bölüm 1

SQL Serverda bir tablo üzerinde yapılan DML işlemlerinin izlenip yapılan işlemlerin tablomuzla hemen hemen aynı yapıya sahip başka tabloya taşınması, hem tablo üzerinde yapılan değişikliklerin izlenmesi hem de tablomuzda var olan verinin değişim trendini izlemek için hayati öneme sahiptir. Bu sebeple birçok sistemde kullanılan tablolarımızla aynı yapıda ve tablo adının sonuna log veya History ifadesi eklenerek yeni tablolar oluşturulup herhangi bir değişlik yapıldığında kaydın o anki hali oluşturduğumuz History veya log tablosuna taşınıyor. Böylece biz elimizde olan iki tabloyu karşılaştırarak bir kaydın zaman içindeki değişimini gözlemleyebiliyoruz.
Bu şekilde tablomuz üzerinde yapılan değişiklikler sırasında kaydın eski halini başka bir tabloda tutmamız için birkaç yöntem sık kullanılıyor. Bunlardan ilki ilgili tablo üzerinde DML triggerlarının tanımlanması ve bu sayede yapılan DML değişliklerin izlenmesi iken diğer sık kullanılan yöntem ise uygulama tarafından yapılan değişlik sonrasında yine uygulama aracılığıyla kaydın değişen halinin History tablomuza taşınmasıdır.
Sık kullanılan yukarıda bahsettiğimiz yöntemlerde kullanıcı geliştirme yaptığı için tablomuzda yapılan bir değişikliğin History tablosuna taşınması atlanabilir. Bu durumda tablolarımızda var olan verinin bütünlüğü sağlanamayacaktır. Bu sebeple SQL Server 2016 CTP 2 versiyonu ile beraber bir tablomuzda yapılan DML değişikliklerinin otomatik olarak izlenmesi ve bu izleme sonucunda oluşan değişliklerin tablomuza aynı yapıya sahip başka bir tabloya otomatik olarak taşınması Temporal Table veya system-versioned table olarak adlandırılmaktadır. Diğer bir değişle Temporal Table özelliği ile tablomuzda bulunan her bir kaydın kaydın geçerlilik periyodunu belirten başlangıç ve bitiş tarihini belirten datetime2 tipinde iki tane kolon bulunmaktadır. Böylece tablomuz üzerinde herhangi bir değişiklik yapıldığında bu alanlarda gerekli düzenlemeler yapılarak kaydımız SQL Server tarafından oluşturulan tablomuza taşınacaktır.
Temporal Table özelliği ile üzerinde değişlik yapılan tabloların loğlarının tutulmasının bir diğer önemli kullanım amacı da tablomuz üzerinde yapılacak olan hatalı bir işlemin kolayca geri alınabilmesidir. Örneğin hatalı bir şekilde tablomuz üzerinde bir kaydın silindiğini düşünürsek bu kaydı, backup/restore gibi uzun bir yöntem kullanmadan basit bir şekilde geri getirebiliriz.
Çok kullanışlı olan Temporal Table özelliğini bize sağladıklarını kısa listeleyecek olursak,

  • Tablomuzdaki kayıtların zaman içindeki değişliklerinin izlenmesi
  • Tablomuzdaki kayıtlar üzerinde yapılan değişikliklerin takip edilmesi
  • Veri ambarı, karar destek sistemi gibi yapılar için OLTP sistem üzerinde yapılan değişliklerin belirlenmesi tablo üzerinde yanlışlıkla yapılacak delete veya update gibi işlemlerin otomatik olarak loglanıp kolayca kurtarılabilmesi

Temporal Table özelliğinin bize sağlamış olduğu faydalara değindikten sonra bu özelliğin DML işlemleri sonucunda nasıl davrandığını kısa inceleyelim. Daha önce de belirttiğimiz gibi Temporal Table oluşturulduğumuzda tablomuzda her kayıt için kaydın geçerlilik periyodunu belirleyen datetime2 tipinde iki tane kolon bulunmaktadır. Bu kolonları SysStartTime ve SysEndTime olarak adlandırdığımızda tablomuza yeni bir kayıt ekildiğimizde yani Insert işlemi sırasında SysStartTime değeri işlemin başladığı tarih olarak atanırken, SysEndTime değeri ise datetime2 veri tipinin maksimum değeri olan 9999/12/31 olarak atanmaktadır. Update işleminde ise kaydın kaydın güncel halinin SysStartTime değeri işlemin başladığı tarih olarak atanırken, SysEndTime değeri ise datetime2 veri tipinin maksimum değeri atanırken kaydın eski hali yani History tablosuna taşınan halinde ise SysEndTime kolonuna işlemin başladığı tarih atanırken SysStartTime değeri değiştirilmez. Benzer olarak Delete işleminde de kayıt History tablosuna taşınırken SysEndTime kolonuna işlemin başladığı tarih atanmaktadır.

SQL Server 2016 CTP 2 versiyonu ile tanıtılan Temporal Table özelliği özellikle verimizi izlemek istediğimizde büyük kolaylık sağlasa bile bu versiyon üzerinde bazı kullanım kısıtları var. Bu kısıtları aşağıdaki gibi listeleyebiliriz.

• Temporal table üzerinde Primary Key tanımlamamız zorunludur. Böylece Temporal table üzerinde yapılan değişlikleri izlemek ve bir kaydı, diğer versiyonları ile ilişkilendirmek daha kolay olacaktır.
• Bir kaydın geçerliliğini belirten periyodumuz için ddatetime2 tipinde bir başlangıç bir tane bitiş kolonu oluşturmalıyız.
• Linked Server üzerinde Temporal tablolarımızı kullanamıyoruz.
• History tablomuz üzerinde Primary Key, Foreign Key, check vs gibi Constraint kullanmıyoruz.
• Bir kaydın geçerlilik periyodunu belirten başlangıç ve bitiş kolonları üzerinde manuel değişiklik yapamıyoruz.
• Temporal table(SYSTEM_VERSIONING) aktif olduğu süre tablomuz üzerinde Truncate Table komutunu çalıştıramıyoruz.
• History tablosu üzerinde doğrudan DML işlemlerini yapamıyoruz
• Temporal table üzerinde Computed Column tanımlayamıyoruz.

Temporal Table özelliğine bu kadar değindikten sonra şimdi bu özelliğin detaylarını uygulama üzerinde görelim. Bunun için aşağıdaki gibi ürün bilgilerini tutan bir tablo ile tablomuz üzerinde yapılan değişliklerin trendini gösterecek şekilde Temporal Table oluşturalım. İşlemi aşağıdaki gibi TSQL kodu ile yapabileceğimiz gibi SQL Server Management Stdio ile de yapabiliriz. Şimdi tablomuzu oluşturalım.

CREATE TABLE dbo.Product
(
    ProductId			int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Name				varchar(50) NOT NULL,
    Color				varchar(50) NULL,
    Price				int NOT NULL,

    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)
GO

Yukarıdaki kodumuzu incelediğimizde kodumuzun normal tablo oluşturma ifadesinden çok farklı olmadığını görebiliyoruz. Create Table ifadesinden sonra ilk 4 kolonumuzda ürünlerimize ait bilgilerimizi tutarken sonraki iki kolon ise tablomuzda bulunan ilgili kaydın geçerlilik zamanını belirtmek için oluşturduğumuz kolonlardır. Dikkat ederseniz bu kolonların veri tipi yukarıda bahsettiğimiz gibi datetime2 olup aynı zamanda tablomuza kayıt eklerken bizim değerini manuel belirtmediğimiz SQL Serverın bizim yerimize değer verdiği kolonlardır. Bu kolonlarımızı belirledikten sonra normal tablo oluşturma kodumuzdan farklı olarak PERIOD FOR SYSTEM_TIME ifadesi ile Temporal table için kullanılacak olan kolonların isimlerini belirtiyoruz. Böylece SQL Server Temporal table kullandığımızda hangi kolonlara göre versiyonlama işlemi yapacağını ve kaydı History tablosuna taşıyacağını biliyor. Son olarak tablo oluşturma kodumuzu yazdıktan sonra yine SQL Server 2016 öncesinde bulunmayan SYSTEM_VERSIONING özelliği aktif hale getirerek oluşturmak istediğimiz tablomuzun Temporal Table olduğunu belirtiyoruz.

Yukarıdaki gibi kodumuzu açıkladıktan sonra kodumuzu çalıştırıp oluşan tablomuzu görelim.

Temporal Table1

Yukarıdaki resimde gördüğümüz gibi Product adında oluşturduğumuz Temporal Table ile beraber SQL Server bizim için değişen kayıtların tutulacağı [dbo].[MSSQL_TemporalHistoryFor_759673754] adında bir tablo daha oluşturdu. Dikkat ederseniz bizim oluşturduğumuz tablomuz ile SQL Serverın rastgele isim vererek oluşturduğu değişen kayıtların tutulacağı tablo yapı olarak birebir aynı gibi görünüyor. Fakat dikkat ederseniz SQL Server tarafından oluşturulan tablomuza rastgele bir isim verildi ve çok fazla akılda kalıcı bir isim değil. Bu sebeple biz tablomuzu oluşturduğumuzda SQL Server 2016 ile beraber eklenen bir parametre ile History olarak oluşturulacak tablomuzun adını daha akılda kalıcı ve anlamlı bir isim olarak biz belirtebiliriz. Şimdi aşağıdaki gibi bir Temporal Table oluşturalım ve History tablosu için bizim belirlediğimiz bir ismin kullanılmasını sağlayalım.

 CREATE TABLE dbo.ProductNew
(
    ProductId			int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Name				varchar(50) NOT NULL,
    Color				varchar(50) NULL,
	Price				int NOT NULL,

    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.ProductNewHistory) )
GO

Yukarıdaki kodumuzu incelediğimizde ProductNew adından bir Temporal Table ile bu tablomuzdaki kayıtların değişimini tutacak ve adı ProductNewHistory olacak şekilde bir tablo oluşturduk. Dikkat ederseniz kodumuzun sonuna eklediğimiz HISTORY_TABLE parametresi ile SQL Server tarafından oluşturulacak olan History tablomuzun adını biz belirlemiş olduk. Yeni oluşturduğumuz tabloları yine SQL Server Management Stdio üzerinden kontrol edebiliriz.

Temporal Table2

Yukarıdaki resimde gördüğümüz gibi Temporal tablomuza ait History tablosu için SQL Server bir isim vermedi bunun yerine bizim parametre olarak belirttiğimiz isim kullanıldı.

Buraya kadar yapmış olduğumuz işlemlerde sıfırdan bir Temporal Table nasıl oluşturulabileceğine değindik. Şimdi de var olan bir tablomuzu nasıl Temporal Tabloya dönüştürebileceğimizi görelim. Bunun için ilk olarak aşağıdaki gibi örnek bir tablo oluşturalım.

SELECT ProductID,Name,Color,ListPrice
INTO Urun
FROM Production.Product

Yukarıdaki kodumuzla aşağıdaki resimde gördüğümüz gibi Urun adında bir tablo oluşturduk.

Temporal Table3

Dikkat ederseniz tablomuz normal bir tablo yani Temporal değil. Şimdi bu tablomuzu Temporal tabloya çevirelim. Bir tablonun Temporal olması için ilk şart tablomuz üzerinde bir tane Primary Key olmasıdır. Urun tablomuzu yeni oluşturduğumuz için üzerinde Primary key olmadığını biliyoruz fakat biz yine de her ihtimale karşı tablomuzda Primary Key olup olmadığını aşağıdaki gibi kontrol edelim.

SELECT OBJECTPROPERTY(OBJECT_ID('Urun'),'TableHasPrimaryKey')

Temporal Table4

Yukarıdaki kodumuzda parametre olarak verdiğimiz tablo üzerinde bir Primary Key varsa fonksiyonumuzdan 1 yoksa 0 dönecektir. Yukarıdaki resimde gördüğümüz gibi sorgumuzun sonucu sıfır olarak görünüyor yani tablomuzda Primary Key yok. Bu sebeple şimdi tablomuza aşağıdaki gibi öncelikle Primary Key ekleyelim.

ALTER TABLE Urun
ADD CONSTRAINT PK_Urun
PRIMARY KEY(ProductID)

Yukarıdaki gibi tablomuza Primary Key ekledikten sonra Temporal tablomuzun bir diğer olmazsa olmazı olan kayıtların geçerlilik periyodunu gösteren datetime2 tipindeki başlangıç ve bitiş tarihi kolonlarıdır. Bu kolonlarımızı da tablomuza eklememiz gerekiyor. Fakat bu kolonları tablomuza eklerken NOT NULL olarak eklediğimiz için bunlarla beraber tablomuzda var olan kayıtlarında bu kolonlarında değer olması için birde ek olarak Default Constraint eklememiz gerekecektir. Default Constraint ile başlangıç tarihi için minimum tarih değeri verirken, bitiş tarihini belirten kolon için de maksimum tarih değerini aşağıdaki gibi belirtmemiz gerekiyor. Ayrıca ekleyeceğimiz kolonlarımızın Temporal Table için kayıtlarımızın geçerlilik periyodunu temsil ettiğini belirtmek için PERIOD FOR SYSTEM_TIME özelliği ile kolon isimlerimizi belirtiyoruz.

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

Yukarıdaki gibi Primary Key ve Periyod kolonlarımızı ekledikten sonra artık tablomuz Temporal Table için gereksinimleri sağlıyor olacak. Tek yapmamız gereken ise tablo seviyesinde bu özelliği aktif hale getirmek. Bunu da aşağıdaki gibi yapabiliriz.

ALTER TABLE Urun
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.UrunHistory))

Yukarıdaki gibi ALTER TABLE komutu ile de tablomuzu Temporal Table yapmış olduk. Fakat yukarıdaki kodumuzla beraber tablo oluşturduğumuzda da eğer HISTORY_TABLE parametresi ile History tablosunun adını biz belirteceksek, burada kullanacağımız tablomuzu şema adıyla beraber kullanmalıyız. Yani sadece UrunHistory olarak değil de dbo.UrunHistory olarak belirtmeliyiz.
Yukarıdaki gibi Urun adında bir tane Temporal Table oluşturduktan sonra şimdi tablomuzu SQL Server Management Stdio üzerinden kontrol edelim.
Temporal Table5

Yukarıdaki resimde de gördüğümüz gibi Urun adında bir Temporal table ile ona ait Urun History adında bir tane History tablosu oluşturuldu. Buraya kadar SQL Server 2016 ile gelen yeni bir özellik olan Temporal tabloların nasıl oluşturulduğune değindik. Sonraki yazıda ise Temporal table üzerinde bazı uygulamalar yapalım.

Hakkında ismailadar

Cevapla

E-posta adresiniz yayınlanmayacak. Required fields are marked *

*


*

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>