Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)

Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)

Ερχόμενος από την έκδοση SQL Server 2016 ο SQL Server έφερε τη δυνατότητα να δούμε ιστορικά μέσα στον χρόνο έναν πίνακα. Δηλαδή να έχουμε ένα row versioning στον πίνακα με τη δυνατότητα να επιλέξουμε τη χρονική στιγμή που θέλουμε. Αυτή η διαδικασία γίνεται εφικτή με τη χρήση των temporal tables.

Τι είναι οι temporal tables

Οι temporal tables είναι πίνακες που είναι σχεδιασμένοι να κρατάνε την ιστορικότητα τους ώστε να διευκολύνουν μια εύκολη πρόσβαση point in time.

Κάθε temporal table έχει υποχρεωτικά δύο datetime2 στήλες που ενημερώνονται αυτόματα από το database engine. Η δουλειά τους είναι να καταγράφουν μέχρι πότε η κάθε εγγραφή ήταν έγκυρη και πότε τροποποιήθηκε.

Επίσης δημιουργείται ένας ιστορικός πίνακας με το ίδιο σχήμα με τον αρχικό που η δουλειά του είναι να αποθηκεύονται σε αυτόν οι προηγούμενες τιμές των εγγραφών (row versions) κάθε φορά που γίνονται update ή delete.

Πώς λειτουργούν οι temporal tables

Για να γίνει έλεγχος της εγκυρότητας των εγγραφών χρησιμοποιούνται οι δυο συστημικές datetime2 κολώνες που αναφέραμε πριν:

  • Ως StartDate είναι η ημερομηνία που έγινε insert η εγγραφή.
  • Ως EndDate είναι η ημερομηνία που τροποποιήθηκε δηλαδή έγινε update ή delete. Αν ως ημερομηνία φαίνεται το έτος 9999 σημαίνει ότι αυτή η γραμμή είναι η πιο πρόσφατη και είναι σε ισχύ.

Το παράδειγμα

Για αρχή θα φτιάξουμε τον temporal πίνακα. Η διαφορά με έναν κανονικό πίνακα ότι υποχρεωτικά θα πρέπει να έχουμε ορίσει τις δύο συστημικές κολόνες StartDate και EndDate, όπως επίσης και το όνομα του ιστορικού πίνακα:

CREATE TABLE ipallilos (
 id INT IDENTITY(1,1) PRIMARY KEY
,Onoma VARCHAR(30)
,Epitheto VARCHAR(30)
,Misthos DECIMAL
,StartDate datetime2 generated always as row start
,EndDate datetime2 generated always as row end
,PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ipallilosHist)) 
GO

Γεμίζουμε τον πίνακα με εγγραφές:

INSERT INTO ipallilos (onoma,epitheto,misthos) VALUES
( 'Stratos', 'Matzouranis',3500.00),
( 'Nikos', 'Georgiou',1600.00),
( 'Anastasis', 'Papandreou',1900.00)
GO

select * from ipallilos
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)

Εδώ όμως συμβαίνει κάτι περιέργο ενώ εγώ πέρασα τις εγγραφές 20:30 φαίνονται σαν να περάστηκαν 18:30. Αυτο συμβαίνει καθώς η ώρα που χρησιμοποιεί δεν είναι με το timezone που έχει ο server δηλαδή GMT+2 αλλά με UTC.

Για να δούμε την πραγματική ώρα που περάστηκαν οι εγγραφές θα πρέπει να προσθέσουμε στην ώρα την διαφορά του UTC timezone με το timezone του συστήματος:

* Η ώρα StartDate είναι διαφορετική σε αυτή την εικόνα καθώς ξαναέφτιαξα τον πίνακα από την αρχή έπειτα ώστε να εξηγήσω αυτό το φαινόμενο.

select *,DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),StartDate) as RealStartDate from dbo.ipallilos
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
01

Ας δοκιμάσω τώρα να κάνω ένα insert, ένα update και ένα delete:

insert into ipallilos (onoma,epitheto,misthos) VALUES
( 'Maria', 'Nikolaou',1500.00)
GO

update ipallilos set misthos = 4000
where id = 1

delete ipallilos where  id = 3

select * from dbo.ipallilos

Βλέπουμε ότι στην πρώτη εγγραφή το StartDate άλλαξε. Aυτό συνέβη καθώς μετά το update η παλιά εγγραφή πήγε στον ιστορικό πίνακα και η εγγραφή που βλέπουμε εδώ περάστηκε σαν καινούργια:

Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
02

Οπότε στον ιστορικό πίνακα θα έχουμε την εγγραφή που έγινε update και αυτή που έγινε delete. Ως EndDate βλέπουμε την ώρα που έγιναν αυτές οι ενέργειες:

select * from dbo.ipallilosHist
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
03

Πάμε να δούμε πως ήταν οι εγγραφές στο διάστημα μεταξύ 21:30 με 22:30:

** δεν μπορώ να δηλώσω ακριβώς την ώρα καθώς αν δεν βάλω between θα θέλει να βάλω ακριβώς την ώρα που έγινε η μεταβολή σε επίπεδο ms.

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME BETWEEN '2020-10-06 21:30' and '2020-10-06 22:00'
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
04

Ουπς!! αυτή η εικόνα δεν είναι αυτή που είχαμε στις ~22:00 καθώς τις τιμές αυτές τις ενημέρωσα στις 22:10. Αυτό συνέβη καθώς όπως είπαμε το SYSTEM_TIME ειναι σε UTC timezone.

Για να δούμε τον πίνακα με την ώρα του δικού μας timezone που έχουμε στον server θα πρέπει να χρησιμοποιήσουμε τις functions που δείξαμε πριν σε παραμέτρους:

declare 
@realtimeFROM datetime,
@realtimeTO datetime;
set @realtimeFROM = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),'2020-10-06 21:30');
set @realtimeTO = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),'2020-10-06 22:00');

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME BETWEEN @realtimeFROM and @realtimeTO
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
05

Για να δούμε μαζί το σύνολο των ιστορικών εγγράφων μαζί με τις ισχύουσες:

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME ALL;
Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
06

Πώς απενεργοποιείται ο temporal table

Για να διαγραφεί ένας temporal πίνακας θα πρέπει πρώτα να κλείσουμε το versioning και να σβηστεί και ο κανονικός πίνακας και ο ιστορικός:

ALTER TABLE [dbo].[ipallilos] SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE [dbo].[ipallilos]
GO
DROP TABLE [dbo].[ipallilosHist]
GO

Πηγές:

Μοιράσου το

Αφήστε μία απάντηση