Delen via

Een tijdelijke tabel met systeemversies maken

  • Artikel

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

Er zijn drie manieren om een tijdelijke tabel met systeemversies te maken wanneer u overweegt hoe de geschiedenistabel wordt opgegeven:

  • Tijdelijke tabel met een anonieme geschiedenistabel: u geeft het schema van de huidige tabel op en laat het systeem een bijbehorende geschiedenistabel met automatisch gegenereerde naam maken.

  • Tijdelijke tabel met een standaardgeschiedenistabel: u geeft de schemanaam en tabelnaam van de geschiedenistabel op en laat het systeem een geschiedenistabel in dat schema maken.

  • Tijdtabel met een door de gebruiker gedefinieerde geschiedenis tabel die vooraf is gemaakt: u maakt een geschiedenistabel die het beste bij uw behoeften past en verwijst vervolgens naar die tabel tijdens het maken van een tijdtabel.

Een tijdelijke tabel maken met een anonieme geschiedenistabel

Het maken van een tijdelijke tabel met een anonieme geschiedenistabel is een handige optie voor het snel maken van objecten, met name in prototypes en testomgevingen. Het is ook de eenvoudigste manier om een tijdelijke tabel te maken, omdat hiervoor geen parameter in de SYSTEM_VERSIONING-component is vereist. In het volgende voorbeeld wordt een nieuwe tabel gemaakt met systeemversiebeheer ingeschakeld zonder de naam van de geschiedenistabel te definiëren.

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);

Opmerkingen

Een tijdelijke tabel met systeemversie moet een gedefinieerde primaire sleutel hebben en precies één PERIOD FOR SYSTEM_TIME gedefinieerd hebben met twee datetime2-kolommen, gedeclareerd als GENERATED ALWAYS AS ROW START of GENERATED ALWAYS AS ROW END.

De PERIOD-kolommen worden altijd beschouwd als niet-nullable, zelfs als de nullbaarheid niet is gespecificeerd. Als de PERIOD-kolommen expliciet als nul zijn gedefinieerd, mislukt de CREATE TABLE-verklaring.

De geschiedenistabel moet altijd worden uitgelijnd met de huidige of tijdelijke tabel, met betrekking tot het aantal kolommen, kolomnamen, volgorde en gegevenstypen.

Er wordt automatisch een anonieme geschiedenistabel gemaakt in hetzelfde schema als de huidige of tijdelijke tabel.

De naam van de anonieme geschiedenistabel heeft de volgende indeling: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Het achtervoegsel is optioneel en wordt alleen toegevoegd als het eerste deel van de tabelnaam niet uniek is.

De geschiedenistabel wordt gemaakt als een rowstore-tabel. PAGE compressie wordt toegepast indien mogelijk, anders wordt de geschiedenistabel niet gecomprimeerd. Sommige tabelconfiguraties, zoals SPARSE kolommen, staan bijvoorbeeld geen compressie toe.

Er wordt een standaard geclusterde index gemaakt voor de geschiedenistabel met een automatisch gegenereerde naam in indeling IX_<history_table_name>. De geclusterde index bevat de PERIOD kolommen (einde, begin).

In de Fabric SQL-database wordt de gemaakte geschiedenistabel niet gespiegeld naar Fabric OneLake.

Als u de huidige tabel wilt creëren als een geheugen-geoptimaliseerde tabel, raadpleegt u systeem-geversioneerde temporele tabellen met geheugen-geoptimaliseerde tabellen.

Een tijdelijke tabel maken met een standaardgeschiedenistabel

Het maken van een tijdelijke tabel met een standaard geschiedenistabel is een handige optie wanneer u naamgeving wilt beheren en nog steeds afhankelijk bent van het systeem om de geschiedenistabel te maken met de standaardconfiguratie. In het volgende voorbeeld wordt een nieuwe tabel gemaakt met systeemversiebeheer ingeschakeld met de naam van de geschiedenistabel die expliciet is gedefinieerd.

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));

Opmerkingen

De geschiedenistabel wordt gemaakt met dezelfde regels als die van toepassing zijn op het maken van een 'anonieme' geschiedenistabel, met de volgende regels die specifiek van toepassing zijn op de benoemde geschiedenistabel.

  • De schemanaam is verplicht voor de parameter HISTORY_TABLE.

  • Als het opgegeven schema niet bestaat, mislukt de CREATE TABLE instructie.

  • Als de tabel die is opgegeven door de parameter HISTORY_TABLE al bestaat, wordt deze gevalideerd op basis van de zojuist gemaakte tijdelijke tabel in termen van schemaconsistentie en tijdelijke gegevensconsistentie. Als u een ongeldige geschiedenistabel opgeeft, mislukt de instructie CREATE TABLE.

Een tijdelijke tabel maken met een door de gebruiker gedefinieerde geschiedenistabel

Het maken van een tijdelijke tabel met door de gebruiker gedefinieerde geschiedenistabel is een handige optie wanneer de gebruiker een geschiedenistabel met specifieke opslagopties en verschillende indexen wil opgeven die zijn afgestemd op historische query's. In het volgende voorbeeld wordt een door de gebruiker gedefinieerde geschiedenistabel gemaakt met een schema dat is afgestemd op de tijdelijke tabel die wordt gemaakt. Voor deze door de gebruiker gedefinieerde geschiedenistabel wordt een geclusterde columnstore-index en een extra niet-geclusterde rowstore-index (B-tree) gemaakt voor puntzoekacties. Nadat deze door de gebruiker gedefinieerde geschiedenistabel is gemaakt, wordt de tijdelijke tabel gemaakt waarin de door de gebruiker gedefinieerde geschiedenistabel wordt opgegeven als de standaardgeschiedenistabel.

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de database-engine een B+-boom. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

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));

Opmerkingen

Als u analysequery's wilt uitvoeren op de historische gegevens die gebruikmaken van statistische functies of vensterfuncties, wordt het maken van een geclusterde columnstore als primaire index ten zeerste aanbevolen voor compressie en queryprestaties.

Als u van plan bent tijdelijke tabellen te gebruiken voor gegevenscontrole (dat wil gezegd, zoeken naar historische wijzigingen voor één rij uit de huidige tabel), moet u een geschiedenistabel voor rijenopslag maken met een geclusterde index.

De geschiedenistabel kan geen primaire sleutel, refererende sleutels, unieke indexen, tabelbeperkingen of triggers hebben. Het kan niet worden geconfigureerd voor het vastleggen van wijzigingengegevens, het bijhouden van wijzigingen, transactionele replicatie of het samenvoegen van replicatie.

In Fabric SQL-database en in Azure SQL Database met Fabric-spiegeling geconfigureerd, wordt de bestaande tabel niet meer gespiegeld wanneer u een bestaande tabel gebruikt als de geschiedenistabel tijdens het maken van een tijdelijke tabel.

Een niet-tijdtabelfunctie wijzigen naar een systeemversiebeheerde tijdtabelfunctie.

U kunt systeemversiebeheer inschakelen voor een bestaande niet-tijdelijke tabel, bijvoorbeeld wanneer u een aangepaste tijdelijke oplossing wilt migreren naar ingebouwde ondersteuning.

U hebt bijvoorbeeld een set tabellen waarin versiebeheer wordt geïmplementeerd met triggers. Het gebruik van tijdelijke systeemversiebeheer is minder complex en biedt andere voordelen, waaronder:

  • Onveranderbare geschiedenis
  • Nieuwe syntaxis voor tijdreizende query's
  • Betere DML-prestaties
  • Minimale onderhoudskosten

Wanneer u een bestaande tabel converteert, kunt u overwegen de HIDDEN-component te gebruiken om de nieuwe PERIOD kolommen (de datetime2 kolommen ValidFrom en ValidTo) te verbergen om te voorkomen dat bestaande toepassingen die niet expliciet kolomnamen opgeven (bijvoorbeeld SELECT * of INSERT zonder een kolomlijst) niet zijn ontworpen om nieuwe kolommen te verwerken.

Versiebeheer toevoegen aan niet-tijdelijke tabellen

Als u wijzigingen wilt bijhouden voor een niet-tijdelijke tabel die de gegevens bevat, moet u de PERIOD definitie toevoegen en eventueel een naam opgeven voor de lege geschiedenistabel die door SQL Server voor u wordt gemaakt:

CREATE SCHEMA History;
GO

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));
GO

Belangrijk

De precisie voor DATETIME2 moet overeenkomen met de precisie voor de onderliggende tabel.

Opmerkingen

Het toevoegen van niet-nullable kolommen met standaardwaarden aan een bestaande tabel met gegevens is een grootte van de gegevensbewerking voor alle andere edities dan SQL Server Enterprise Edition (waarop het een metagegevensbewerking is). Met een grote bestaande geschiedenistabel met gegevens in sql Server Standard-editie kan het toevoegen van een niet-null-kolom een dure bewerking zijn.

Beperkingen voor de begin- en eindkolommen van de periode moeten zorgvuldig worden gekozen:

  • De standaardinstelling voor de beginkolom geeft aan vanaf welk tijdstip bestaande rijen geldig moeten zijn. Het kan niet worden opgegeven als een datum/tijd-punt in de toekomst.

  • Eindtijd moet worden opgegeven als de maximumwaarde voor een bepaalde datum/tijd2 precisie, bijvoorbeeld 9999-12-31 23:59:59 of 9999-12-31 23:59:59.9999999.

Als u PERIOD toevoegt, wordt een gegevensconsistentiecontrole uitgevoerd op de huidige tabel om ervoor te zorgen dat de bestaande waarden voor puntkolommen geldig zijn.

Wanneer een bestaande geschiedenistabel wordt opgegeven bij het inschakelen van SYSTEM_VERSIONING, wordt een gegevensconsistentiecontrole uitgevoerd in zowel de huidige als de geschiedenistabel. U kunt deze overslaan als u DATA_CONSISTENCY_CHECK = OFF opgeeft als extra parameter.

Bestaande tabellen migreren naar ingebouwde ondersteuning

In dit voorbeeld ziet u hoe u migreert van een bestaande oplossing op basis van triggers naar ingebouwde tijdelijke ondersteuning. In dit voorbeeld wordt ervan uitgegaan dat de huidige aangepaste oplossing de huidige en historische gegevens splitst in twee afzonderlijke gebruikerstabellen (ProjectTaskCurrent en ProjectTaskHistory).

Als uw bestaande oplossing gebruikmaakt van één tabel om werkelijke en historische rijen op te slaan, moet u de gegevens splitsen in twee tabellen voordat de migratiestappen in het volgende voorbeeld worden weergegeven. Verwijder eerst de trigger van de toekomstige temporele tabel. Controleer vervolgens of de PERIOD kolommen niet nullbaar zijn.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Opmerkingen

Als u verwijst naar bestaande kolommen in PERIOD definitie, wordt impliciet generated_always_type gewijzigd in AS_ROW_START en AS_ROW_END voor deze kolommen.

Als u PERIOD toevoegt, wordt een gegevensconsistentiecontrole uitgevoerd op de huidige tabel om ervoor te zorgen dat de bestaande waarden voor puntkolommen geldig zijn.

We raden u ten zeerste aan om SYSTEM_VERSIONING in te stellen met DATA_CONSISTENCY_CHECK = ON, om consistentiecontroles voor gegevens af te dwingen op bestaande gegevens.

Als verborgen kolommen de voorkeur hebben, gebruikt u de opdracht ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Verwante inhoud