T-SQL temporal tabellen

Een temporal tabel, ook wel bekend als system-versioned temporal table, is een tabel die logs bijhoudt van veranderingen in de data van een gekoppelde, andere tabel. Dit zorgt ervoor dat je logs opbouwt over data uit een tabel die voor een bepaalde tijdperiode valide was. De naam system-versioned komt voort uit het feit dat in deze tabel de validiteit van de data met een tijdsperiode wordt aangegeven. De system-versioned table bevat 2 kolommen die de tijdsperiode aangeven: de startdatum vanaf wanneer de data valide was en de einddatum, wanneer de data niet meer valide was en is gewijzigd of verwijderd. De temporal tabel heeft namelijk 2 kolommen die bijhouden van welke tot welke periode de data valide was.

Een temporal tabel kan nuttig zijn wanneer:

1. Het bijhouden van wijzigingen in de data nodig is voor bijv. audits.
2. Bijhouden van trends door de tijd heen
3. Een back-up bijhouden als er per ongeluk wijzigingen in de data plaatsvinden die verkeerd zjin.

Hoe werkt het?

De temporal tabel, laten we deze voor nu log tabel noemen, is gekoppeld aan een andere tabel, deze noemen we de huidige tabel. Bij een insert in de huidige tabel wordt de start dateTime gezet voor deze specifieke data in de log tabel. Dit is het beginpunt vanaf waar deze data geldig is. Als er in de huidige tabel een update of delete wordt uitgevoerd, zal de data die gewijzigd of verwijderd word, opgeslagen worden in de log tabel. In de log tabel krijgt deze data dan een tijdsperiode mee, vanaf de tijd dat deze data valide was tot de tijd van de update of delete. Met een merge doet de log tabel eigenlijk hetzelfde als met een update of een delete, afhankelijk van de uitkomst van de merge.

Hieronder een voorbeeld van hoe je een temporal tabel kunt aanmaken voor een tabel:

Als je geen history tabel naam opgeeft zal SQL er zelf een maken met de volgende naamgeving:
dbo.MSSQL_TemporalHistoryFor_X, waar X de primary key is van de huidige tabel.

Je kunt ook SYSTEM_VERSIONING aanzetten op een bestaande tabel. Dat doe je door in de bestaande tabel 2 kolommen toe te voegen:

En daarna de SYSTEM_VERSIONED aan te zetten:

ALTER TABLE dbo.shippingOrders
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.shippingOrdersHistory));

De volgende query is een voorbeeld van hoe je data kunt opvragen vanuit een temporal tabel:

Bovenstaande query zoekt rijen in dbo.ShippingOrders log tabel waar de shippingOrderId 1000 is, shipped op true staat en de data valide is geweest in het afgelopen jaar.

Voor meer informatie over hoe je queries kun uitvoeren op een temportal tabel, lees het volgende artikel: queries uitvoeren op een System_Versioned table.

Limitaties

Een temportal tabel heeft ook een aantal limitaties:

1. Je kunt er geen primary key aangeven.
2. Je kunt geen constraints opgeven
3. INSERT en UPDATE statements zijn niet toegestaan binnen een temporal tabel
4. TRUNCATE TABLE werkt niet op een tabel met SYSTEM_VERSIONED = ON
5. File tabellen zijn niet toegestaan

Zie hier voor de gehele lijst aan limitaties.