SQL Server 2016 System-Versioned Table

SQL Server 2016 ile hayatımıza giren bir diğer özellikde system-versioned table yani Temporal Teble dır. Bu özellik sayesinde tablomuz üzerinde ki DML değişikliklerini otomatik olarak izlenebilir ve  ve izleme sonucunda oluşan değişiklikler aynı yapıya sahip başka bir tabloda loglanır. System-versioned tablosunda her bir kayıt için kayıdın geçerlilik periyodunu başlangıç ve bitiş tarihini belirten DATETIME2 tipinde iki tane kolon bulunmaktadır. Bu kolonlar ile tablomuzda herhangi bir değişiklik yapıldığında bu alanlarda gerekli düzenlemeler yapılarak kayıtlog tablomuza taşınacaktır.

CREATE TABLE <Table_Name>

(

Column_Name_1 DataType,

Column_Name_2 DataType,

Column_Name_3 DataType,

Column_Name_4 DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,

Column_Name_5 DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM_TIME (Column_Name_4, Column_Name_5)) WITH(SYSTEM_VERSIONING = ON);

Syntax ‘ımızı incelediğimiz de var olan tablo yapımızda iki adet DATETIME 2 tipinde kolon bulunmakta ve tablomuza kayıt eklerken SQL Server ‘ın bizim yerimize değer atadığı kolonlardır. PERIOD FOR SYSTEM_TIME  İfadesi ile system-versioned table için kullanılacak kolonların isimlerini belirtiyoruz ve bu şekilde verisyonlamanın hangi kolonlar üzerinden yapılacağını SQL Server ‘a bildirmiş oluyoruz. Bu sayede History tablosuna taşıyacağı kayıdı belirtmiş oluyoruz.  SYSTEM_VERSIONING = ON İfadesi ile de bu  özelliği açıp açmayacağımız SET ediyoruz. On ise artık system-versioned table olarak tablomuz hayatına devam ediyor olacak. Canlı örneğe geçecek olur isek ;

CREATE TABLE Personel_TT
(
Personel_Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED ,
Identity_Number CHAR(11) NOT NULL,
First_Name VARCHAR(100),
Last_Name VARCHAR(100),
Phone VARCHAR(12) NOT NULL,
Email VARCHAR(100) NOT NULL,
Gender VARCHAR(1)  NOT NULL,
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);

Yukarıd aki script ile tablomuzu oluşturup System-Versioned olduğunu da SysStartTime ve SysEndTime kolonları ile de belirtip aktif hale getiriyoruz.

Yukarıda da gördüğünüz üzere diğer tablolardan farklı olarak üzerinde saat işareti bulunmakta oluşturduğumuz tablonun. + ya basıp içeirisne baktığımızda History tablosunuda görmekteyiz.

Peki tablomuza data girişi yapalım.

-- Kayıt Ekleme
INSERT INTO Personel_TT
(
Identity_Number,
First_Name,
Last_Name,
Phone,
Email,
Gender
)
VALUES 
(
'12345678910',
'Yusuf',
'Kahveci',
'05309999999',
'[email protected]',
'E'
)
GO

INSERT INTO Personel_TT
(
Identity_Number,
First_Name,
Last_Name,
Phone,
Email,
Gender
)
VALUES 
(
'98765432101',
'Adam',
'Exev',
'05409999999',
'[email protected]',
'E'
)
GO

INSERT INTO Personel_TT
(
Identity_Number,
First_Name,
Last_Name,
Phone,
Email,
Gender
)
VALUES 
(
'65432896471',
'Marry',
'Chunk',
'05407777777',
'[email protected]',
'B'
)

Tablomuza data  girişi yaptıktan sonra SELECT çekerek datamızı kontrol edelim ;

SELECT * FROM Personel_TT

SysStartTime kolonu incelendiğinde işlemin başladığı tarih değeri atanırken , SysEndTime değeri ise datetime2 veri tipinin maksimum değeri olan 9999-12-31 23:59:59.9999999 olarak atanmaktadır.

 

Tablomuz üzerinde 65432896471  TC kimlik No lu kayıdı silelim ;

DELETE FROM dbo.Personel_TT Where Identity_Number='65432896471'

Daha sonra hem ana tablomuza hemde History tablomuza SELECT çekelim.

SELECT * FROM Personel_TT
GO
SELECT * FROM [dbo].[MSSQL_TemporalHistoryFor_1093578934]
GO

Gördüğünüz üzere ana tablomuzdan kayıdımız silinmiş ve History tablomuza baktığımızda da sildiğimiz kayıdın loglandığını görmekteyiz. Burada dikkat etmeniz gereken nokta silinmeden SysEndTime ‘ı  datetime2 nin max değeri iken history tablosuna loglandıktan sonra DELETE gördüğü yani history e atıldığı tarih olarak güncellenmiş SysEndtime kolonu. SysStartTime kolonu ise ilk işlem tarihini hala tutmakta.

UPDATE işlemi yapalım ana tablomuzda ve süreci tekrar izleyelim ;

UPDATE dbo.Personel_TT SET Phone ='08770000000' WHERe Identity_Number='12345678910'

Tablolarımızı kontrol edelim ;

SELECT * FROM Personel_TT
GO
SELECT * FROM [dbo].[MSSQL_TemporalHistoryFor_1093578934]
GO

Gördüğünüz üzere  güncelleme işlemi yapılan kayıtta ana tabloda SysStartTime değeri en son güncelleme yapıldığı tarihi alırken, History tablosunda aynı değer SysEndTime kolonuna SET edilmiş durumda. Yani DELETE ifadesinden daha farklı UPDATE süreci. Kısaca UPDATE sürecinde UPDATE edilen tarih ana tablo da SysStartTime ve History tablosunda SysEndTime kolonlarında güncellenmekte.

Tablomuzu DROP edelim ;

  DROP TABLE Personel_TT

Tablomuzu DROP etmek isterken yukarıda ki hata mesajını bize döndürdü. Tablo system-versioned olduğu için o özelliği pasif yapmadan DROP işlemi yapamayacağını aktarıyor.

Peki system-versioned özelliği açık olan tabloda bu özelliği nasıl kapatırız ?

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

Yukarıdaki script ile system-versioned özelliğini OFF a çekerek tablomuzu bu işlemden sonra DROP edebiliriz.

DB içerisinde yer alan bir tabloda bu özelliği açmak için ise ;

ALTER TABLE dbo.Personel_TT SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE=dbo.Personel_TT_History,DATA_CONSISTENCY_CHECK=ON)
);

Scripti incelediğimizde SYSTEM_VERSIONIN=ON ifadesi ile Temporal table hale dönüştür diyoruz ve HISTORY_TABLE ifadesinde de HISTORY tablomuzun ismini verip veri bütünlüğünüde Check ettirip özelliği aktif hale getiriyoruz.

Leave a Reply

Your email address will not be published. Required fields are marked *