System-versioned temporal table

Ένα από τα χαρακτηριστικά που υπάρχουν από την έκδοση του SQL Server 2016 είναι το system-versioned temporable table. Καθώς είναι ένα χαρακτηριστικό που θα βοηθήσει αρκετούς να αφήσουν τους custom tables που έχουν σχεδιάσει για το auditing ή το history των εγγραφών. Ωστόσο να αναφέρουμε ότι μπορούν να διαγραφούν αλλά και να σταματήσουμε το versioning αρκεί να έχουμε τα κατάλληλα δικαιώματα. Καλό είναι να θυμόμαστε ότι στους temporable tables καταγράφονται αυτόματα όλα τα updates και τα deletes που γίνονται πάνω στον πίνακα. Οπότε με σύγκριση τους βλέπουμε όλες τις τιμές που έχουν αλλάξει.

Παρακάτω επισυνάπτω μια σειρά από sql codes όπως υπάρχουν και στο site της Microsoft.

–Δημιουργία πίνακα με ταυτόχρονη ενεργοποίηση system-versioning table

CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED
, DeptName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

–Δημιουργία πίνακα με ταυτόχρονη ενεργοποίηση system-versioning table, σε history table που έχουμε ήδη δημιουργήσει.

CREATE TABLE DepartmentHistory
(
DeptID INT NOT NULL
, DeptName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, ValidFrom DATETIME2 NOT NULL
, ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
DeptID int NOT NULL PRIMARY KEY CLUSTERED
, DeptName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

–Ενεργοποίηση system-versioning table σε υπάρχον πίνακα

ALTER TABLE InsurancePolicy
ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME()
, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.9999999’)
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));

–Στον παρακάτω κώδικα βλέπουμε τρόπους που μπορούμε να κάνουμε query πάνω σε temporable tables.

–Attempt 1
declare
@realtimeFROM datetime,
@realtimeTO datetime;
set @realtimeFROM = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),’2023-01-01 21:30′);
set @realtimeTO = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),’2023-01-30 22:00′);

SELECT * FROM dbo.ipallilos
FOR SYSTEM_TIME BETWEEN @realtimeFROM and @realtimeTO

–Attempt 2 –Display all

SELECT * FROM dbo.ipallilos
FOR SYSTEM_TIME ALL;

–Για να σταματήσουμε το system versioning ώστε να μπορέσουμε να κάνουμε αλλαγές στους πίνακες (νέα πεδία) εκτελούμε.

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

–Για να το διαγράψουμε τελείως μετα θα πρέπει να κάνουμε drop και τον temprable table. Ο οποίος εμφανίζεται σαν κανονικός πίνακας.

–Για να προσδιορίσουμε το retention period που θα ισχύει ο temprable table τις αλλαγές εκτελούμε το παρακάτω. Εκτός βέβαια και εάν θέλουμε να τα κρατήσουμε χωρίς περιορισμό οπότε δεν το εκτελούμε.

ALTER TABLE [dbo].[Products]
SET
(
SYSTEM_VERSIONING = ON
(
HISTORY_RETENTION_PERIOD = 9 MONTHS
)
)