Delen via

Tijdelijke tabellen

  • Artikel

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Tijdelijke tabellen (ook wel bekend als tijdelijke tabellen met systeemversies), zijn een databasefunctie die ingebouwde ondersteuning biedt voor het verstrekken van informatie over gegevens die op elk moment in de tabel zijn opgeslagen, in plaats van alleen de gegevens die op het huidige moment in de tijd juist zijn.

Aan de slag met tijdelijke tabellen met systeemversiesen bekijk tijdelijke tabelgebruiksscenario's.

Wat is een systeem-versiegebonden tijdtabel?

Een temporale tabel met systeemversies is een type gebruikerstabel dat is ontworpen om de volledige geschiedenis van gegevenswijzigingen bij te houden, waardoor eenvoudige analyse op een bepaald tijdstip mogelijk is. Dit type tijdelijke tabel wordt een tijdelijke tabel met systeemversies genoemd, omdat het systeem de geldigheidsperiode voor elke rij beheert (dat wil gezegd de database-engine).

Elke tijdelijke tabel heeft twee expliciet gedefinieerde kolommen, elk met een datum/tijd2 gegevenstype. Deze kolommen worden periode kolommen genoemd. Deze periodekolommen worden uitsluitend door het systeem gebruikt om de geldigheidsperiode voor elke rij vast te leggen wanneer een rij wordt gewijzigd. De hoofdtabel waarin de huidige gegevens worden opgeslagen, wordt de huidige tabelgenoemd, of gewoon als de tijdelijke tabel.

Naast deze puntkolommen bevat een tijdelijke tabel ook een verwijzing naar een andere tabel met een gespiegeld schema, de geschiedenistabelgenoemd. Het systeem gebruikt de geschiedenistabel om automatisch de vorige versie van de rij op te slaan telkens wanneer een rij in de tijdelijke tabel wordt bijgewerkt of verwijderd. Tijdens het maken van tijdelijke tabellen kunt u een bestaande geschiedenistabel opgeven (die schema-compatibel moet zijn) of een standaardgeschiedenistabel laten maken.

Waarom tijdelijk?

Echte gegevensbronnen zijn dynamisch en vaker dan niet zakelijke beslissingen zijn afhankelijk van inzichten die analisten kunnen verkrijgen uit gegevensontwikkeling. Gebruiksvoorbeelden voor tijdelijke tabellen zijn:

  • Alle wijzigingen in gegevens controleren en waar nodig forensisch gegevens uitvoeren
  • De toestand van de gegevens reconstrueren vanaf elk moment in het verleden
  • Trends in de loop van de tijd berekenen
  • Een langzaam veranderende dimensie onderhouden voor beslissingsondersteuningstoepassingen
  • Herstellen van onbedoelde gegevenswijzigingen en toepassingsfouten

Hoe werkt tijdgerelateerd?

Systeemversiebeheer voor een tabel wordt geïmplementeerd als een paar tabellen: een huidige tabel en een geschiedenistabel. Binnen elk van deze tabellen worden twee extra datum/tijd2 kolommen gebruikt om de geldigheidsperiode voor elke rij te definiëren:

  • Periodebeginkolom : Het systeem registreert de begintijd voor de rij in deze kolom, meestal aangeduid als de ValidFrom Kolom.

  • Kolom Einde Periode: het systeem registreert eindtijd voor de rij in deze kolom, meestal aangeduid met de kolom ValidTo.

De huidige tabel bevat de huidige waarde voor elke rij. De geschiedenistabel bevat elke vorige waarde (de oude versie) voor elke rij, indien van toepassing, en de begin- en eindtijd voor de periode waarvoor deze geldig was.

diagram waarin wordt getoond hoe een tijdelijke tabel werkt.

Het volgende script illustreert een scenario met werknemersgegevens:

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Voor meer informatie, zie Een systeem-geversioneerde tijdstabel maken.

  • Invoegen: Het systeem stelt de waarde voor de kolom ValidFrom in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok en wijst de waarde voor de ValidTo kolom toe aan de maximumwaarde van 9999-12-31. Hiermee wordt de rij gemarkeerd als geopend.

  • Updates: Het systeem slaat de vorige waarde van de rij op in de geschiedenistabel en stelt de waarde voor de kolom ValidTo in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok. Hiermee wordt de rij gemarkeerd als gesloten, met een periode die is vastgelegd waarvoor de rij geldig was. In de huidige tabel wordt de rij bijgewerkt met de nieuwe waarde en stelt het systeem de waarde voor de kolom ValidFrom in op de begintijd van de transactie (in de UTC-tijdzone) op basis van de systeemklok. De waarde voor de bijgewerkte rij in de huidige tabel voor de kolom ValidTo blijft de maximumwaarde van 9999-12-31.

  • Verwijderen: Het systeem slaat de vorige waarde van de rij op in de geschiedenistabel en stelt de waarde voor de kolom ValidTo in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok. Hiermee wordt de rij gemarkeerd als gesloten, met een periode die is vastgelegd waarvoor de vorige rij geldig was. In de huidige tabel wordt de rij verwijderd. Query's van de huidige tabel retourneren deze rij niet. Alleen query's die betrekking hebben op geschiedenisgegevens retourneren gegevens waarvoor een rij is gesloten.

  • Samenvoegen: De bewerking gedraagt zich precies alsof maximaal drie instructies (een INSERT, een UPDATEen/of een DELETE) worden uitgevoerd, afhankelijk van wat is opgegeven als acties in de MERGE-instructie.

De tijden die zijn vastgelegd in het systeem datum/tijd2 kolommen zijn gebaseerd op de begintijd van de transactie zelf. Alle rijen die binnen één transactie zijn ingevoegd, hebben bijvoorbeeld dezelfde UTC-tijd vastgelegd in de kolom die overeenkomt met het begin van de SYSTEM_TIME periode.

Wanneer u query's voor gegevenswijziging uitvoert op een tijdelijke tabel, voegt de database-engine een rij toe aan de geschiedenistabel, zelfs als er geen kolomwaarden veranderen.

Hoe kan ik tijdelijke gegevens opvragen?

De SELECT ... FROM <table>-instructie heeft een nieuwe clausule FOR SYSTEM_TIME, met vijf tijdsspecifieke subclausules om vragen uit te voeren op gegevens in de huidige en geschiedenis tabellen. Deze nieuwe syntaxis van de SELECT instructie wordt rechtstreeks ondersteund in één tabel, doorgegeven via meerdere joins en via weergaven boven op meerdere tijdelijke tabellen.

Wanneer u een query uitvoert met behulp van de FOR SYSTEM_TIME-component met behulp van een van de vijf subclauses, worden historische gegevens uit de tijdelijke tabel opgenomen, zoals wordt weergegeven in de volgende afbeelding.

Diagram dat laat zien hoe tijdelijke queries werken.

Met de volgende query wordt gezocht naar rijversies voor een werknemer met de filtervoorwaarde WHERE EmployeeID = 1000 die ten minste actief waren voor een deel van de periode tussen 1 januari 2021 en 1 januari 2022 (inclusief de bovengrens):

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME filtert rijen uit die een geldigheidsperiode met een duur van nul hebben (ValidFrom = ValidTo).

Deze rijen worden gegenereerd als u meerdere updates uitvoert op dezelfde primaire sleutel binnen dezelfde transactie. In dat geval retourneert tijdelijke query alleen rijversies vóór de transacties en huidige rijen na de transacties.

Als u deze rijen in de analyse wilt opnemen, voert u rechtstreeks een query uit op de geschiedenistabel.

In de volgende tabel vertegenwoordigt ValidFrom in de kolom In aanmerking komende rijen de waarde in de kolom ValidFrom in de tabel waarop een query wordt uitgevoerd en ValidTo de waarde in de kolom ValidTo in de tabel waarop een query wordt uitgevoerd. Zie FROM-component plus JOIN, APPLY, PIVOTen Gegevens opvragen in een systeem-geversioneerde temporele tabelvoor de volledige syntaxis en voorbeelden.

Uitdrukking In aanmerking komende rijen Notitie
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Retourneert een tabel met rijen die de waarden bevatten die op het opgegeven tijdstip in het verleden actueel waren. Intern wordt een samenvoeging uitgevoerd tussen de tijdelijke tabel en de geschiedenistabel. De resultaten worden gefilterd om de waarden in de rij te retourneren die geldig waren op het tijdstip, opgegeven door de parameter date_time. De waarde voor een rij wordt als geldig beschouwd als de system_start_time_column_name waarde kleiner is dan of gelijk is aan de parameterwaarde date_time en de system_end_time_column_name waarde groter is dan de date_time parameterwaarde.
FROM start_date_timeTOend_date_time ValidFrom < einddatum_tijdAND ValidTo >startdatum_tijd Retourneert een tabel met de waarden voor alle rijversies die actief waren binnen het opgegeven tijdsbereik, ongeacht of ze actief zijn voordat de parameterwaarde van de start_date_time voor het argument FROM actief was of niet meer actief was na de parameterwaarde van end_date_time voor het argument TO. Intern wordt een samenvoeging uitgevoerd tussen de tijdelijke tabel en de geschiedenistabel. De resultaten worden gefilterd om de waarden te retourneren voor alle rijversies die op elk gewenst moment actief waren tijdens het opgegeven tijdsbereik. Rijen die niet meer exact actief zijn op de ondergrens die door het FROM eindpunt is gedefinieerd, worden niet opgenomen en records die exact actief werden op de bovengrens die is gedefinieerd door het TO-eindpunt, worden ook niet opgenomen.
BETWEEN start_date_timeANDend_date_time ValidFrom <= end_date_timeAND ValidTo >start_date_time Hetzelfde als in de FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time beschrijving, behalve dat de tabel met geretourneerde rijen ook rijen bevat die actief zijn geworden op de bovengrens die is gedefinieerd door het eindpunt van end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_timeAND ValidTo <=end_date_time Retourneert een tabel met de waarden voor alle rijversies die zijn geopend en gesloten binnen het opgegeven tijdsbereik dat is gedefinieerd door de twee puntwaarden voor het argument CONTAINED IN. Rijen die exact actief zijn geworden op de ondergrens of die niet meer actief zijn op de bovengrens, worden opgenomen.
ALL Alle rijen Retourneert de samenvoeging van rijen die deel uitmaken van de huidige en de geschiedenistabel.

De periodekolommen verbergen

U kunt ervoor kiezen om de puntkolommen te verbergen, zodat query's die niet expliciet naar deze kolommen verwijzen, deze kolommen niet retourneren (bijvoorbeeld bij het uitvoeren van SELECT * FROM <table>).

Als u een verborgen kolom wilt retourneren, moet u expliciet verwijzen naar de verborgen kolom in de query. Op dezelfde manier gaan INSERT en BULK INSERT verklaringen door alsof deze nieuwe periodekolommen niet aanwezig waren, en worden de kolomwaarden automatisch ingevuld.

Zie CREATE TABLE en ALTER TABLEvoor meer informatie over het gebruik van de component HIDDEN.

Monsters

Verwante inhoud