Dela via

Skapa en systemversionsbaserad temporal tabell

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

Det finns tre sätt att skapa en systemversionsbaserad temporal tabell när du överväger hur historiktabellen anges:

  • Temporaltabell med en anonym historiktabell: du anger schemat för den aktuella tabellen och låter systemet skapa en motsvarande historiktabell med 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 temporal tabell eftersom det inte kräver någon parameter i SYSTEM_VERSIONING -satsen. I följande exempel skapas en ny tabell med systemversioner aktiverade 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.

En anonym historiktabell skapas automatiskt i samma schema som aktuell eller temporal tabell.

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 standardklusterindex 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. I följande exempel skapas en ny tabell med systemversioner aktiverade med namnet på historiktabellen uttryckligen definierad.

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 användardefinierad historiktabell är ett praktiskt alternativ när användaren vill ange en historiktabell med specifika lagringsalternativ och olika index som är anpassade till historiska frågor. I följande exempel skapas en användardefinierad historiktabell med ett schema som är justerat med den temporala tabell som skapas. I den här användardefinierade historiktabellen skapas ett grupperat columnstore-index och ett extra B-trädindex (nonclustered rowstore) för punktsökningar. När den här användardefinierade historiktabellen har skapats skapas den tidsmässiga tabellen som 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 analysfrågor på historiska data som använder aggregeringar eller fönsterfunktioner rekommenderar vi starkt att du skapar ett grupperat kolumnarkiv som ett primärt index för 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 systemversionshantering i 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 bör du överväga att använda HIDDEN -satsen för att dölja de nya PERIOD kolumnerna ( datetime2-kolumnernaValidFrom och ValidTo) för att undvika att påverka befintliga program som inte uttryckligen anger kolumnnamn (till exempel SELECT * eller INSERT utan en kolumnlista) inte är utformade 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 datakonsekvenskontroll i den aktuella tabellen för att se till att de befintliga värdena för periodkolumner är giltiga.

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. I det här exemplet förutsätter vi att den aktuella anpassade lösningen delar upp aktuella och historiska data i två separata användartabeller (ProjectTaskCurrent och ProjectTaskHistory).

Om din befintliga lösning använder en tabell för att lagra faktiska och historiska rader bör du dela upp data 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 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
    )
);

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 datakonsekvenskontroll i den aktuella tabellen för att se till att de befintliga värdena för periodkolumner är giltiga.

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änder du kommandot ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Relaterat innehåll