Skapa en systemversionsbaserad temporal tabell

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Du kan skapa en systemversionerad temporär tabell på tre sätt, baserat på hur du specificerar historiktabellen:

  • Temporal tabell med en anonym historiktabell: du specificerar schemat för den aktuella tabellen och låter systemet skapa en motsvarande historiktabell med ett automatiskt genererat namn.

  • Temporal tabell med en standardhistoriktabell: du anger schemanamnet för historiktabellen och tabellnamnet och låter systemet skapa en historiktabell i schemat.

  • Tidstabell med en användardefinierad historiktabell som skapats i förväg: du skapar en historiktabell som passar dina behov och refererar sedan till tabellen när du skapar tidstabellen.

Skapa en temporal tabell med en anonym historiktabell

Att skapa en temporal tabell med en anonym historiktabell är ett praktiskt alternativ för att snabbt skapa objekt, särskilt i prototyper och testmiljöer. Det är också det enklaste sättet att skapa en temporär tabell eftersom det inte kräver någon parameter i klausulen SYSTEM_VERSIONING . Följande exempel skapar en ny tabell med systemversionering aktiverad, utan att definiera namnet på historiktabellen.

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

Remarks

En systemversionsbaserad temporal tabell måste ha en primärnyckel definierad och ha exakt en PERIOD FOR SYSTEM_TIME definierad med två datetime2-kolumner , deklarerade som GENERATED ALWAYS AS ROW START eller GENERATED ALWAYS AS ROW END.

Kolumnerna PERIOD antas alltid vara icke-nullbara, även om nullbarhet inte har angetts. Om kolumnerna PERIOD uttryckligen definieras som nullbara, misslyckas instruktionen CREATE TABLE.

Historiktabellen måste alltid vara schemajusterad med den aktuella eller tidsmässiga tabellen, med avseende på antalet kolumner, kolumnnamn, ordning och datatyper.

Database Engine skapar automatiskt en anonym historiktabell i samma schema som den aktuella eller temporala tabellen.

Namnet på den anonyma historiktabellen har följande format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Suffixet är valfritt och läggs bara till om den första delen av tabellnamnet inte är unik.

Historiktabellen skapas som en radlagringstabell. PAGE komprimering tillämpas om möjligt, annars är historiktabellen okomprimerad. Vissa tabellkonfigurationer, till exempel SPARSE kolumner, tillåter till exempel inte komprimering.

Ett standardklustrat index skapas för historiktabellen med ett automatiskt genererat namn i formatet IX_<history_table_name>. Det klustrade indexet innehåller kolumnerna PERIOD (start, slut).

I Fabric SQL-databasen speglas inte den skapade historiktabellen i Fabric OneLake.

Information om hur du skapar den aktuella tabellen som en minnesoptimerad tabell finns i Systemversionsbaserade temporala tabeller med minnesoptimerade tabeller.

Skapa en temporal tabell med en standardhistoriktabell

Att skapa en temporal tabell med en standardhistoriktabell är ett praktiskt alternativ när du vill styra namngivning och fortfarande förlitar dig på att systemet skapar historiktabellen med standardkonfigurationen. Följande exempel skapar en ny tabell med systemversionering aktiverad, där namnet på historiktabellen explicit definieras.

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

Remarks

Historiktabellen skapas med samma regler som gäller för att skapa en "anonym" historiktabell, med följande regler som gäller specifikt för den namngivna historiktabellen.

  • Schemanamnet är obligatoriskt för parametern HISTORY_TABLE .

  • Om det angivna schemat inte finns misslyckas instruktionen CREATE TABLE .

  • Om den tabell som anges av parametern HISTORY_TABLE redan finns verifieras den mot den nyligen skapade temporala tabellen när det gäller schemakonsekvens och temporal datakonsekvens. Om du anger en ogiltig historiktabell kommer instruktionen CREATE TABLE att misslyckas.

Skapa en temporal tabell med en användardefinierad historiktabell

Att skapa en temporal tabell med en användardefinierad historiktabell är ett bekvämt alternativ när du vill specificera en historiktabell med specifika lagringsalternativ och olika index anpassade till historiska frågor. I följande exempel skapar du en användardefinierad historiktabell med ett schema som är justerat med den temporala tabellen. Denna historiktabell har ett klustrat kolumnlagringsindex och ett extra icke-klustrat radlagringsindex (B-träd) för punktuppslag. Efter att du skapat historiktabellen skapar du den temporala tabellen och anger den användardefinierade historiktabellen som standardhistoriktabell.

Note

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

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

Remarks

Om du planerar att köra analytiska frågor på historiska data som använder aggregat- eller fönsterfunktioner rekommenderas det starkt att skapa ett klustrat kolumnlagrat index som primärindex för bättre komprimering och frågeprestanda.

Om du planerar att använda temporala tabeller för datagranskning (d.v.s. söka efter historiska ändringar för en enskild rad från den aktuella tabellen) bör du skapa en radlagringshistoriktabell med ett grupperat index.

Historiktabellen får inte ha en primärnyckel, sekundärnycklar, unika index, tabellbegränsningar eller utlösare. Det kan inte konfigureras för insamling av ändringsdata, ändringsspårning, transaktionsreplikering eller sammanslagningsreplikering.

När du använder en befintlig tabell som historiktabell vid skapandet av en tidsmässig tabell, och i en Fabric SQL-databas samt en Azure SQL-databas där Fabric-spegling är konfigurerad, slutar den befintliga tabellen att speglas.

Ändra icke-temporal tabell så att den är en systemversionsbaserad temporal tabell

Du kan aktivera systemversionering på en befintlig icke-temporal tabell, till exempel när du vill migrera en anpassad temporal lösning till inbyggt stöd.

Du kan till exempel ha en uppsättning tabeller där versionshantering implementeras med utlösare. Att använda temporal systemversionering är mindre komplext och ger andra fördelar, inklusive:

  • Oföränderlig historik
  • Ny syntax för tidsresande frågor
  • Bättre DML-prestanda
  • Minimala underhållskostnader

När du konverterar en befintlig tabell, överväg att använda klausulen HIDDEN för att dölja de nya PERIOD kolumnerna ( datetime2-kolumnernaValidFrom och ValidTo) för att undvika att påverka befintliga applikationer som inte uttryckligen specificerar kolumnnamn (till exempel SELECT * eller INSERT utan kolumnlista) och som inte är designade för att hantera nya kolumner.

Lägga till versionshantering i icke-temporala tabeller

Om du vill börja spåra ändringar för en icke-temporal tabell som innehåller data måste du lägga PERIOD till definitionen och eventuellt ange ett namn för den tomma historiktabellen som SQL Server skapar åt dig:

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

Important

Precisionen för DATETIME2 måste överensstämma med precisionen för den underliggande tabellen.

Remarks

Att lägga till icke-nullbara kolumner med standardvärden i en befintlig tabell med data är en storlek på dataåtgärden på alla utgåvor som inte är SQL Server Enterprise-utgåva (där det är en metadataåtgärd). Med en stor befintlig historiktabell med data i SQL Server Standard-utgåvan kan det vara dyrt att lägga till en kolumn som inte är null.

Begränsningar för periodstarts- och periodslutkolumner måste väljas noggrant:

  • Standardvärdet för startkolumnen anger från vilken tidpunkt du anser att befintliga rader är giltiga. Det kan inte anges som en tidpunkt i framtiden.

  • Sluttiden måste anges som det maximala värdet för en viss datetime2-precision , till exempel 9999-12-31 23:59:59 eller 9999-12-31 23:59:59.9999999.

Att lägga till PERIOD utför en datakonsistenskontroll på den aktuella tabellen för att säkerställa att de befintliga värdena för periodkolumnerna är korrekta.

När en befintlig historiktabell anges när du aktiverar SYSTEM_VERSIONINGutförs en kontroll av datakonsekvens i både den aktuella tabellen och historiktabellen. Den kan hoppas över om du anger DATA_CONSISTENCY_CHECK = OFF som en extra parameter.

Migrera befintliga tabeller till inbyggt stöd

Det här exemplet visar hur du migrerar från en befintlig lösning baserat på utlösare till inbyggt tidsstöd. Detta exempel antar att den aktuella anpassade lösningen delar upp den aktuella och historiska datan i två separata användartabeller (ProjectTaskCurrentProjectTaskHistoryoch ).

Om din befintliga lösning använder en enda tabell för att lagra faktiska och historiska rader, bör du dela upp datan i två tabeller innan migreringsstegen som visas i följande exempel. Ta bort först utlösaren i den framtida temporala tabellen. Kontrollera sedan att kolumnerna PERIOD inte är nullbara.

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

/* Make sure 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
        )
    );

Remarks

Om du refererar till befintliga kolumner i PERIOD definitionen ändras generated_always_type implicit till AS_ROW_START och AS_ROW_END för dessa kolumner.

Att lägga till PERIOD utför en datakonsistenskontroll på den aktuella tabellen för att säkerställa att de befintliga värdena för periodkolumnerna är korrekta.

Vi rekommenderar starkt att du anger SYSTEM_VERSIONING med DATA_CONSISTENCY_CHECK = ON, för att framtvinga datakonsekvenskontroller på befintliga data.

Om dolda kolumner föredras, använd följande kommando:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Relaterat innehåll

  • Last updated on