Πώς μπορούμε να δούμε την ιστορική εικόνα ενός πίνακα στον SQL Server με χρήση Temporal Tables (a.k.a. Row Versioning)
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Ερχόμενος από την έκδοση 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
Εδώ όμως συμβαίνει κάτι περιέργο ενώ εγώ πέρασα τις εγγραφές 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
Ας δοκιμάσω τώρα να κάνω ένα 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 η παλιά εγγραφή πήγε στον ιστορικό πίνακα και η εγγραφή που βλέπουμε εδώ περάστηκε σαν καινούργια:
Οπότε στον ιστορικό πίνακα θα έχουμε την εγγραφή που έγινε update και αυτή που έγινε delete. Ως EndDate βλέπουμε την ώρα που έγιναν αυτές οι ενέργειες:
select * from dbo.ipallilosHist
Πάμε να δούμε πως ήταν οι εγγραφές στο διάστημα μεταξύ 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'
Ουπς!! αυτή η εικόνα δεν είναι αυτή που είχαμε στις ~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
Για να δούμε μαζί το σύνολο των ιστορικών εγγράφων μαζί με τις ισχύουσες:
SELECT * FROM dbo.ipallilos FOR SYSTEM_TIME ALL;
Πώς απενεργοποιείται ο temporal table
Για να διαγραφεί ένας temporal πίνακας θα πρέπει πρώτα να κλείσουμε το versioning και να σβηστεί και ο κανονικός πίνακας και ο ιστορικός:
ALTER TABLE [dbo].[ipallilos] SET ( SYSTEM_VERSIONING = OFF) GO DROP TABLE [dbo].[ipallilos] GO DROP TABLE [dbo].[ipallilosHist] GO
