Pazartesi , 20 Kasım 2017

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

Bir önceki yazımızda SQL Server 2016 ile beraber gelen Temporal tables özelliğine detaylı bir şekilde değinip tablolarımızı nasıl oluşturabileceğimizi gördük. Şimdi ise önceki yazımızda oluşturduğumuz Temporal Table objeleri üzerinde uygulama yapalım. İlk olarak Temporal tablomuzu ve History tablosunu aşağıdaki gibi sorgulayalım.


SELECT * FROM dbo.Urun

SELECT * FROM dbo.UrunHistory

Temporal Table6

Yukarıdaki resimde gördüğümüz gibi Urun tablomuzda kayıt varken UrunHistory tablomuzda şuan için herhangi bir kayıt yok. Şimdi 1 numaralı ürün için aşağıdaki gibi Update işlemi yapalım.


UPDATE dbo.Urun

SET ListPrice=20

WHERE ProductID=1



UPDATE dbo.Urun

SET Color='Purple'

WHERE ProductID=1




UPDATE dbo.Urun

SET Name='SQL Server 2016 Kitabı'

WHERE ProductID=1

Yukarıdaki 3 tane Update işlemimizi yaptıktan sonra tekrar şimdi tablolarımızı tekrar sorgulayalım.


SELECT * FROM dbo.Urun

 SELECT * FROM dbo.UrunHistory

Temporal Table7
Yukarıdaki resimdeki sorgularımızın sonucunu incelediğimizde az önce boş olan History tablomuzda artık üç kaydın bulunduğunu ve bu kayıtların Update işleminden önceki kaydın halini tuttuğunu görebiliyoruz. Ayrıca dikkat ederseniz Update işleminden sonra History tablosunda yer alan kayıtların EndDate kolondaki tarih verilerinin de güncellendiğini görebiliyoruz. Bir başka dikkat etmemiz gereken nokta ise History tablosunda bulunan kayıtların StartDate ve EndDate değerleri arasında bir kaydın EndDate değeri sonraki kaydın StartDate değeri olarak görünüyor. Bu da aslında ilgili kaydın geçerli olduğu periyodu gösteriyor ve ileride göreceğimiz üzere biz sorgulamalarımızı da bu tarih aralıklarına göre yapıyoruz.

Yukarıdaki gibi kayıtların nasıl otomatik olarak History tablosuna taşındığını gördükten sonra şimdi History tablosu üzerinde DML işlemlerine izin verilip verilmediğine bakalım. Bunun için ilk olarak aşağıdaki Update komutumuzu UrunHistory tablomuz üzerinde çalıştıralım.


UPDATE dbo.UrunHistory

SET ListPrice=100

WHERE ProductID=1

Temporal Table8

Yukarıdaki gibi History tablomuz üzerinde değişiklik yapmak istediğimizde bize yukarıdaki hatayı verecektir. Eğer yine de Temporal tablomuzun History tablosundaki kayıtlar üzerinde değişiklik yapmak istiyorsak geçici süre içi Temporal Table özelliğini pasif hale getirmemiz gerekecektir. Şimdi tablomuz için Temporal table özelliğini kapatıp birkaç Update yapalım ve bu Update işlemleri sonunda Temporal Table için bazı sorgulama tekniklerine değinelim.


ALTER TABLE dbo.Urun SET ( SYSTEM_VERSIONING = OFF )

GO

UPDATE dbo.UrunHistory

SET StartDate='1900-01-01 00:00:00.0000000',EndDate='2015-09-01 14:20:48.0043903'

WHERE ProductID=1 And StartDate='1900-01-01 00:00:00.0000000'



UPDATE dbo.UrunHistory

SET StartDate='2015-09-01 14:20:48.0043903',EndDate='2015-09-05 00:20:48.0043903'

WHERE ProductID=1 And StartDate='2015-09-13 14:40:33.8152994'



UPDATE dbo.UrunHistory

SET StartDate='2015-09-05 00:20:48.0043903',EndDate='2015-09-13 14:40:38.0275408'

WHERE ProductID=1 And StartDate='2015-09-13 14:40:35.6984056'



GO

ALTER TABLE Urun

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.UrunHistory))

GO

Yukarıdaki kodumuzu incelediğimizde Temporal tablomuz için oluşturulan History tablosu üzerinde doğrudan DML işlemleri yapmak istediğimizde öncelikle tablomuzun Temporal Table özelliğini pasif hale getirip işlemlerimizi yaptıktan sonra benzer şekilde Temporal Table özelliğini tekrar aktifleştiriyoruz. Şimdi tekrar tablolarımızı sorgulayalım.


SELECT * FROM dbo.Urun

 SELECT * FROM dbo.UrunHistory

Temporal Table9

Yukarıdaki resimde History tablosunda yaptığımız değişikleri görebiliyoruz. Yukarıdaki gibi hem tablomuzu hem de History tablomuzu ayrı ayrı sorgulamak yerine sorgumuza ekleyeceğimiz yeni bir parametre ile beraber ikisini beraber sorgulayabiliyoruz. Daha açık bir ifadeyle tablomuzda bulunan bir kaydın belli bir tarihteki haline erişmek için sorgumuzda ilgili tarih aralığını parametre olarak belirtmemiz gerekir. Belirtme işlemini yaparken aşağıdaki ifadeleri kullanabiliriz.

AS OF <date_time> SysStartTime<= date_time ANDSysEndTime > date_time
FROM <start_date_time> TO <end_date_time> SysStartTime<= date_time ANDSysEndTime > date_time
BETWEEN <start_date_time> AND <end_date_time> SysStartTime<= end_date_time ANDSysEndTime > start_date_time
CONTAINED IN (<start_date_time> ,<end_date_time>) SysStartTime>= start_date_time ANDSysEndTime<= end_date_time

Yukarıdaki kullanımları incelediğimizde sağ tarafta parametreyi sorgumuza nasıl ekleyeceğimiz belirtilirken, sağ tarafta ise bu parametrenin açıklaması yapılmıştır. Daha açık bir ifadeyle sağ taraftaki ifadeler sorgulamak istediğimiz tarih aralığında uç noktaların dahil olup olmayacağını belirtmektedir. Şimdi bu parametreleri kullanarak nasıl sorgulama yapabileceğimizi görelim. Bunun için ilk olarak Urun tablomuzdaki 1 numaralı kaydı sorgulayalım.


SELECT * FROM dbo.Urun

WHERE ProductID=1

Temporal Table10

Yukarıdaki resimde 1 numaralı ürüne ait bilgileri görebiliyoruz. Bu bilgiler Urun tablomuzda yer alan kaydın son halidir. Bunu kaydımızın StartDate ve EndDate kolonlarına bakarak anlayabiliriz. Peki bu kaydımızın 2015-09-10 tarihindeki hali nasıldı diye merak ediyorsak doğrudan History tablosunu sorgulamak yerine sorgumuza az önce bahsettiğimiz parametrelerimizden birini ekleyebiliriz. Bunun için ilk olarak AS OF operatörünü kullanalım.


SELECT * FROM dbo.Urun

FOR SYSTEM_TIME AS OF '2015-09-10'

WHERE ProductID=1

Temporal Table11

Yukarıdaki resimde sorgumuzun sonucunu incelediğimizde bir önceki sorgu sonucuna göre ürünün adının güncellemediğini görüyoruz. Daha açık bir ifadeyle 2015-09-10 tarihinde 1 numaralı ürüne ait bilgiler yukarıdaki resimde gösterildiği gibidir. Bu sorgulama SQL Server tarafında yapılırken bizim parametre olarak verdiğimiz 2015-09-10 tarihi verisi kullandığımız AS OF operatör ile işleme alınarak kaydımızın istediğimiz andaki hali sorgulanıyor. Bu şekilde sorgulama yapılırken verilen tarih verisinin StartDate ve EndDate değerleri arasında olduğuna dikkat edin.

Yukarıdaki gibi parametre olarak bir tarih verebileceğimiz gibi tarih ve saati de içeren bir veriyi de parametre olarak kullanabiliriz.

SELECT * FROM dbo.Urun

FOR SYSTEM_TIME AS OF '2015-09-03 11:20:48.0043903'

WHERE ProductID=1

Temporal Table12

Yukarıdaki resmi incelediğimizde bir önceki sorgumuzla aynı sonucu vermiş olsa bile bu sefer kullandığımız FROM <start_date_time> TO <end_date_time> operatörü ile sadece bir tarih verisi değil bir aralık belirtmiş olduk. Eğer bu aralıkta kayıt birden fazla defa işlem görüş olsaydı birden fazla kayıt ekrana listelenecekti. Şimdi sorgumuzu aşağıdaki gibi değiştirip tekrar çalıştıralım.

SELECT * FROM dbo.Urun
FOR SYSTEM_TIME FROM '2015-09-03 11:20:48.0043903' 
TO '2015-09-13 11:20:48.0043903'
WHERE ProductID=1

Temporal Table13

Yukarıdaki resmi incelediğimizde bir önceki sorgumuzla aynı sonucu vermiş olsa bile bu sefer kullandığımız FROM <start_date_time> TO <end_date_time> operatörü ile sadece bir tarih verisi değil bir aralık belirtmiş olduk. Eğer bu aralıkta kayıt birden fazla defa işlem görüş olsaydı birden fazla kayıt ekrana listelenecekti. Şimdi sorgumuzu aşağıdaki gibi değiştirip tekrar çalıştıralım.


SELECT * FROM dbo.Urun
FOR SYSTEM_TIME FROM '2015-09-03 11:20:48.0043903' 
TO '2015-09-13 11:20:48.0043903'
WHERE ProductID=1

Temporal Table14

Yukarıdaki resimde gördüğümüz gibi sorgumuzda kullandığımız tarih Aralığında 1 numaralı ürünle ilgili iki tane değişikliğin yapıldığını görebiliyoruz.

Temporal table objelerimizin nasıl oluşturulup sorgulandığına değindikten sonra şimdi de daha önce oluşturduğumuz Temporal Table objelerinin listesine nasıl erişebileceğimize değinelim. Bunun için SQL Serverda var olan sys.tables sistem kataloğunu kullanabiliriz. SQL Server 2016 ile beraber Temporal Table için bu kataloğa da bazı kolonlar eklendi. Bu kolonlar vasıtasıyla aşağıdaki gibi daha önce oluşturduğumuz Temporal Tablolar hakkında detaylı bilgiye erişebiliriz.


SELECT name,object_id,temporal_type_desc,history_table_id,
object_name(history_table_id) as HistoryTableName
FROM sys.tables
WHERE object_id =object_id('Urun')

Temporal Table15

Yukarıdaki resimde gördüğümüz gibi Uru adıyla oluşturduğumuz Temporal Table ile ilgili detaylara sys.tables kataloğu üzerinden erişebiliyoruz. Benzer şekilde sadece belli bir Temporal Table değil var olan tüm Temporal Table objelerine erişmek istersek sorgumuzu aşağıdaki gibi değiştirebiliriz.


SELECT name,object_id,temporal_type_desc,history_table_id,
object_name(history_table_id) as HistoryTableName
FROM sys.tables
WHERE temporal_type_desc='SYSTEM_VERSIONED_TEMPORAL_TABLE'

Temporal Table16

Yukarıdaki resimde göründüğü gibi var olan tüm Temporal Table objelerimize yukarıdaki gibi erişebiliriz. Şimdi yukarıda listelenen örnek tablolarımızdan birindeki kayıtları Truncate Table komutunu kullanarak kayıtlarımızı silmeye çalışalım.

TRUNCATE TABLE Urun 

Temporal Table17

Yukarıdaki resimde gösterilen hata mesajında da gösterildiği gibi Temporal Table objelerimiz üzerinde Truncate Table komutunun çalıştırılmasına izin verilmiyor. Benzer şekilde şimdi de daha önce oluşturduğumuz Temporal Tablolarımızdan birini silmeye çalışalım.


DROP TABLE dbo.Urun

Yukarıdaki kodumuzu çalıştırdığımızda aşağıdaki gibi bir hata ile karşılaşırız.

Temporal Table18

Yukarıdaki hata mesajından da anlaşılacağı üzere Temporal tablolarımızı doğrudan silemiyoruz. Drop ve Truncate işlemleri için öncelikle Temporal Table özelliğini pasif hale getirip daha sonra tablomuzu ve History tablosunu silebiliriz veya benzer şekilde Truncate edebiliriz. Şimdi aşağıdaki gibi tablomuzu silelim.


ALTER TABLE dbo.Urun SET ( SYSTEM_VERSIONING = OFF )

GO

DROP TABLE dbo.Urun

GO

DROP TABLE dbo.UrunHistory

GO

Yukarıdaki gibi tablomuzun Temporal table özelliğini önce pasif hale getirdikten sonra tablomuzu ve History tablosunu silebiliriz.

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>