Hantera kvarhållning av historiska data i systemversionsbaserade temporala tabeller

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

En systemversionerad temporal tabell behåller varje tidigare version av varje rad i sin historiktabell. Historiktabellen kan öka din databasstorlek mer än vanliga tabeller under följande förutsättningar:

• Du behåller historisk data under en lång tid.
• Du har ett uppdaterings- eller borttagningsmönster med tung datamodifiering.

En stor, ständigt växande historiktabell kan bli ett problem, både på grund av lagringskostnader och den prestandaskatt den medför på temporala frågor. Att utveckla en policy för datalagring för historiktabellen är en viktig del av planeringen och hanteringen av livscykeln för varje tidstabell.

Planera en policy för datalagring

För att hantera lagring av temporära tabelldata, bestäm först den nödvändiga lagringstiden för varje temporal tabell. Din bevarandepolicy bör i de flesta fall vara en del av affärslogiken i applikationen som använder de temporala tabellerna. Till exempel har applikationer inom datagranskning och tidsresescenarier strikta krav på hur länge historisk data måste vara tillgänglig för online-förfrågningar.

När du har fastställt din lagringstid för data, utveckla en plan för att hantera historisk data. Bestäm hur och var du lagrar dina historiska data och hur du tar bort historiska data som är äldre än dina kvarhållningskrav.

Varje metod i denna artikel verkar utifrån kolumnen som motsvarar slutet av perioden i den aktuella tabellen, vilket är kolumnen ValidTo i de följande exemplen. Värdet för slutet av perioden för varje rad avgör det ögonblick då radversionen blir stängd, dvs när den hamnar i historiktabellen. Till exempel stämmer tillståndet ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) överens med historisk data som är mer än 30 dagar gammal.

Välj en av följande metoder för att agera på dessa rader:

Exemplen på partitionering och anpassad rensning i denna artikel använder exempel från artikeln Skapa en systemversionerad temporal tabell .

Använd en policy för bevarande av tidshistoria

Gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database, Azure SQL Managed Instance och SQL database i Microsoft Fabric.

Du kan konfigurera tidshistorik på individuell tabellnivå, vilket låter dig skapa flexibla åldringspolicyer. För att möjliggöra tidsbevarande, ställ HISTORY_RETENTION_PERIOD in under tabellskapandet eller vid schemaändring.

Efter att du definierat lagringspolicyn kör Database Engine en schemalagd bakgrundsuppgift som hittar och transparent tar bort historiska rader vars slutgiltiga period är äldre än lagringsperioden.

Så här konfigurerar du kvarhållningsprincip

Innan du konfigurerar kvarhållningsprincipen för en temporal tabell kontrollerar du om tidsmässig historisk kvarhållning är aktiverad på databasnivå:

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

Databasflaggan is_temporal_history_retention_enabled är standard på ON, men du kan ändra den genom att använda satsen ALTER DATABASE . Databasmotorn anger den också automatiskt till OFF efter en återställning till en viss tidpunkt (PITR), enligt beskrivningen i Överväganden vid återställning till en viss tidpunkt. Om du vill aktivera rensning av temporal historik för databasen kör du följande kommando. Ersätt <myDB> med den databas du vill ändra:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Du kan konfigurera retentionspolicyn vid tabellskapandet genom att ange ett värde för parametern HISTORY_RETENTION_PERIOD :

CREATE TABLE dbo.WebsiteUserInfo
(
    UserID INT NOT NULL PRIMARY KEY CLUSTERED,
    UserName NVARCHAR (100) NOT NULL,
    PagesVisited INT NOT NULL,
    ValidFrom DATETIME2 (0) GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

Med den policyn på plats blir rader i dbo.WebsiteUserInfoHistory berättigade till sanering när de uppfyller följande villkor:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Du kan ange lagringstiden i DAYS, WEEKS, , MONTHSeller YEARS. Om du utelämnar HISTORY_RETENTION_PERIOD, återställs retention till INFINITE. Du kan också använda nyckelordet INFINITE explicit.

I vissa scenarier kan du vilja konfigurera retention efter tabellskapandet eller ändra det tidigare konfigurerade värdet. I så fall använder du satsen ALTER TABLE:

ALTER TABLE dbo.WebsiteUserInfo
    SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Om du vill granska det aktuella tillståndet för kvarhållningsprincipen använder du följande exempel. Den här frågan kopplar flaggan för temporär kvarhållningsaktivering på databasnivå med kvarhållningsperioder för enskilda tabeller:

SELECT DB.is_temporal_history_retention_enabled,
       SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
       T1.name AS TemporalTableName,
       SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
       T2.name AS HistoryTableName,
       T1.history_retention_period,
       T1.history_retention_period_unit_desc
FROM sys.tables AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Så här tar databasmotorn bort föråldrade rader

Rensningsprocessen beror på indexlayouten för historiktabellen. Du kan konfigurera en ändlig lagringspolicy endast på historiktabeller med ett klustrat radlager (B-träd) eller klustrat kolumnlagringsindex. En bakgrundsuppgift utför åldrad datarensning för alla tidstabeller med en ändlig lagringstid.

B-träd radlagringsindex

Radlagringsindexet med klustring måste börja med den kolumn som motsvarar slutet på perioden SYSTEM_TIME. Om ett sådant index inte finns kan du inte konfigurera en ändlig lagringstid:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

Standardhistoriktabellen har redan ett kompatibelt klustrat index. Om du försöker ta bort det indexet i en historiktabell med en ändlig lagringstid, misslyckas operationen med följande fel:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

Rensningslogiken för det radlagringsklustrade indexet tar bort äldre rader i mindre omgångar (upp till 10 000), vilket minimerar belastningen på databasloggen och I/O-delsystemet. Även om rensningslogik använder det nödvändiga B-trädsindexet kan den inte garantera raderingsordningen för rader äldre än lagringsperioden. Var inte beroende av rensningsordningen i dina program.

Grupperat kolumnlagringsindex

Rensningsuppgiften för den klustrade kolumnlagringen tar bort hela radgrupper på en gång. Varje radgrupp innehåller vanligtvis en miljon rader. Denna metod är mer effektiv, särskilt när din arbetsbelastning genererar historisk data i hög takt.

Datakomprimering och rensning av kvarhållna data gör det klustrade kolumnlagringsindexet till ett bra val i scenarier där din arbetsbelastning snabbt genererar stora mängder historiska data. Det mönstret är typiskt för intensiva transaktionsbearbetningsarbetsbelastningar som använder temporala tabeller för förändringsspårning och revision, trendanalys eller insamling av Internet of Things (IoT)-data.

Rensningen i det klustrade kolumnlagringsindexet fungerar optimalt när historiska rader kommer in i stigande ordning (sorterade efter kolumnen för periodslut). Detta villkor gäller alltid när endast mekanismen SYSTEM_VERSIONING fyller historiktabellen. Om raderna i historiktabellen inte är ordnade efter kolumnen för periodslut (vilket kan hända när du migrerar befintliga historiska data), återskapa det klustrade kolumnlagringsindexet ovanpå ett korrekt sorterat B-trädsindex för radlagring för att uppnå optimal prestanda.

Undvik att bygga om det klustrade kolumnlagringsindexet i en historiktabell med en ändlig lagringstid, eftersom ombyggnad kan ändra radgruppsordningen som systemversioneringsoperationen naturligt påtvingar. Om du behöver bygga om det klustrade kolumnlagreindexet i historiktabellen, skapa det ovanpå ett kompatibelt B-trädindex för att bevara den radgruppsordning som krävs för regelbunden datarensning. Ta samma metod om du skapar en temporär tabell med en befintlig historiktabell som har ett klustrat kolumnlagringsindex utan garanterad dataordning:

/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO

/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

När du konfigurerar en ändlig lagringsperiod för en historiktabell med ett klustrat kolumnlagringsindex kan du inte skapa ytterligare icke-klustrade B-trädindex i den tabellen:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Det föregående påståendet misslyckas med följande fel:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Frågetabeller med kvarhållningsprincip

Alla frågor i den temporala tabellen filtrerar automatiskt bort historiska rader som matchar den begränsade retentionspolicyn, för att undvika oförutsägbara och inkonsekventa resultat. Rensningsuppgiften raderar åldrade rader när som helst och i godtycklig ordning.

Följande skärmdump visar frågeplanen för en grundläggande fråga. Detta exempel förutsätter en lagringsperiod på enMONTH i tabellen WebsiteUserInfo :

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Frågeplanen inkluderar ett extra filter i kolumnen för periodens slut (ValidTo) i operatorn Clustered Index Scan (markerad i bilden nedan) i historiktabellen.

Om du frågar historiktabellen direkt kan du se rader som är äldre än den angivna lagringsperioden, men utan någon garanti för upprepbara frågeresultat. Följande skärmdump visar frågeplanen för en fråga i historiktabellen utan extra filter:

Lita inte på affärslogik som läser historiktabellen efter lagringsperioden, eftersom du kan få inkonsekventa eller oväntade resultat. Använd temporala frågor med klausulen FOR SYSTEM_TIME för att analysera data i temporala tabeller.

Överväganden för återställning till en specifik tidpunkt

När du återställer en databas till en specifik tidpunkt har den nya databasen temporal retention inaktiverad på databasnivå (is_temporal_history_retention_enabled satt till OFF). Detta beteende låter dig granska historiska rader som är äldre än lagringsperioden innan saneringsuppgiften tar bort dem. För att återuppta automatisk rensning av den återställda databasen, återställ TEMPORAL_HISTORY_RETENTION till ON.

Använd tabellpartitionering

Partitionerade tabeller och index kan göra stora tabeller mer hanterbara och skalbara. Genom att använda tabellpartitioneringsmetoden kan du implementera anpassad datarensning eller offlinearkivering baserat på en tidssituation. Tabellpartitionering ger dig också prestandafördelar när du kör frågor mot temporala tabeller i en delmängd av datahistoriken med hjälp av partitionseliminering.

Använd tabellpartitionering för att implementera ett glidande fönster för att flytta ut den äldsta delen av den historiska datan från historiktabellen, och håll storleken på den bevarade delen konstant efter ålder. Ett glidande fönster lagrar data i historiktabellen motsvarande den nödvändiga lagringsperioden. Historiktabellen stödjer att byta ut data medan SYSTEM_VERSIONING är ON, vilket betyder att du kan rensa en del av historikdatan utan att införa ett underhållsfönster eller blockera dina vanliga arbetsbelastningar.

Ett glidande fönster kräver två uppsättningar uppgifter:

• En konfigurationsuppgift för partitionering
• Återkommande partitionsunderhållsuppgifter

För detta exempel, anta att du vill spara historisk data i sex månader och att du vill lagra varje månads data i en separat partition. Anta också att du aktiverade systemversionering i september 2023.

En konfigurationsuppgift för partitionering skapar den inledande partitioneringskonfigurationen för historiktabellen. I det här exemplet skapar du samma antal partitioner som storleken på det glidande fönstret, i månader, plus en extra tom partition. Denna konfiguration säkerställer att systemet kan lagra ny data korrekt när du först startar den återkommande partitionsunderhållsuppgiften. Det garanterar också att du aldrig delar upp partitioner som innehåller data, vilket undviker dyra datarörelser. Definiera partitionfunktionen med RANGE LEFT istället för RANGE RIGHT. För mer information, se prestandaöverväganden med tabellpartitionering senare i denna artikel.

Följande bild visar den initiala partitioneringskonfigurationen för att behålla sex månaders data.

Den första och sista partitionen är öppna på den nedre respektive övre gränsen för att säkerställa att varje ny rad har en destinationspartition oavsett värdet i partitioneringskolumnen. Med tiden hamnar nya rader i historiktabellen i högre partitioner. När den sjätte partitionen fylls upp når du den angivna lagringstiden. Vid det här laget kan du börja med återkommande partitionsunderhåll för första gången. Schemalägg det att köras periodiskt, en gång i månaden i det här exemplet.

Följande bild illustrerar de återkommande partitionsunderhållsuppgifterna.

Varje körning av den återkommande underhållsuppgiften utför följande steg:

1. SWITCH OUT: Skapa en staging-tabell och byt sedan en partition mellan historiktabellen och staging-tabellen genom att använda satsen ALTER TABLE med argumentet SWITCH PARTITION .

   ALTER TABLE [<history table>]
       SWITCH PARTITION 1 TO [<staging table>];
   

   Efter partitionsbytet kan du valfritt arkivera data från stagingtabellen och sedan antingen ta bort eller förkorta stagingtabellen för att förbereda nästa underhållscykel.

2. MERGE RANGE: Slå ihop den tomma partitionen 1 med partitionen 2 genom att använda satsen ALTER PARTITION FUNCTION med MERGE RANGE. När du använder denna funktion för att ta bort den lägsta gränsen, slår du effektivt ihop den tomma partitionen 1 med den tidigare partitionen 2 för att bilda en ny partition 1. De andra partitionerna ändrar också effektivt sina ordningstal.

3. SPLIT RANGE: Skapa en ny tom partition 7 genom att använda satsen ALTER PARTITION FUNCTION med SPLIT RANGE. När du använder denna funktion för att lägga till en ny övre gräns skapar du i praktiken en separat partition för kommande månad.

Använd Transact-SQL för att skapa partitioner i historiktabellen

Använd följande Transact-SQL-skript för att skapa partitionsfunktionen, partitionsschemat och återskapa det klustrade indexet så att det partitioneras i enlighet med schemat. I det här exemplet skapar du ett skjutfönster på sex månader med månatliga partitioner från och med september 2023.

BEGIN TRANSACTION;

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo](DATETIME2 (7))
    AS RANGE LEFT FOR VALUES (
        N'2023-09-30T23:59:59.999',
        N'2023-10-31T23:59:59.999',
        N'2023-11-30T23:59:59.999',
        N'2023-12-31T23:59:59.999',
        N'2024-01-31T23:59:59.999',
        N'2024-02-29T23:59:59.999'
    );

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
    TO (
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY]
    );

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
)
ON [sch_Partition_DepartmentHistory_By_ValidTo] (ValidTo);

COMMIT TRANSACTION;

Använd Transact-SQL för att underhålla partitioner i scenariot med skjutfönster

Använd följande Transact-SQL skript för att underhålla partitioner i scenariot med skjutfönstret. I det här exemplet byter du ut partitionen för september 2023 genom att använda MERGE RANGE, och lägger sedan till en ny partition för mars 2024 genom att använda SPLIT RANGE.

BEGIN TRANSACTION;

/* (1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023]
(
    DeptID INT NOT NULL,
    DeptName VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 (7) NOT NULL,
    ValidTo DATETIME2 (7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/* (2) Create index on the same filegroups as the partition to switch out */
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023](
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];

/* (3) Create constraints matching the partition to switch out */
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023] WITH CHECK
    ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
            CHECK (ValidTo <= N'2023-09-30T23:59:59.999');

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1];

/* (4) Switch partition to staging table */
ALTER TABLE [dbo].[DepartmentHistory]
    SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE
        )
    );

/* (5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/* (6) merge range to move lower boundary one month ahead */
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE (N'2023-09-30T23:59:59.999');

/* (7) Create new empty partition for "April and after"
by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE (N'2024-03-31T23:59:59.999');

COMMIT TRANSACTION;

Den optimala lösningen är dock att regelbundet köra ett generiskt Transact-SQL skript varje månad utan ändringar. Du kan generalisera det föregående skriptet för att agera på dina angivna parametrar (den nedre gränsen som behöver slås ihop och den nya gränsen som skapas av partitionsdelningen). För att undvika att skapa en staging-tabell varje månad, skapa en i förväg och återanvänd den genom att ändra kontrollbegränsningen så att den matchar partitionen du byter ut. För mer information, se hur man helt automatiserar scenariot med glidande fönster.

Prestandaöverväganden med tabellpartitionering

Utför åtgärderna MERGE RANGE och SPLIT RANGE på ett sätt som undviker dataförflyttning, eftersom dataförflyttning kan orsaka betydande prestandaoverhead. Mer information finns i Ändra en partitionsfunktion.

När du skapar partitionsfunktionen som RANGE LEFT, är de angivna värdena de övre gränserna för partitionerna. När du använder RANGE RIGHTär de angivna värdena de nedre gränserna för partitionerna. När du använder åtgärden MERGE RANGE för att ta bort en gräns från partitionsfunktionsdefinitionen tar den underliggande implementeringen även bort den partition som innehåller gränsen. Om den partitionen inte är tom, MERGE RANGE flyttar den datan till den resulterande partitionen.

I följande diagram beskrivs alternativen RANGE LEFT och RANGE RIGHT:

I ett scenario med skjutfönster tar du alltid bort den lägsta partitionsgränsen.

• RANGE LEFT Fall: Den lägsta partitionsgränsen tillhör partitionen 1, som är tom (efter att partitionen bytts ut), så MERGE RANGE det orsakar ingen datarörelse.

• RANGE RIGHT Fall: Den lägsta partitionsgränsen tillhör partition 2, som inte är tom eftersom bytet ut endast tömmer partitionen 1. I detta fall orsakar datarörelse MERGE RANGE , vilket flyttar data från partition 2 till partition 1. För att undvika denna datarörelse RANGE RIGHT behöver det i scenariot med glidande fönster ha partition 1, som alltid är tom. Detta krav innebär att om du använder RANGE RIGHT, bör du skapa och underhålla en extra partition jämfört med RANGE LEFT fallet.

Slutsats: Partitionshantering är enklare när du använder RANGE LEFT den i en glidande partition, och den undviker datarörelse. Det är dock lite enklare att definiera partitionsgränser med RANGE RIGHT eftersom du inte behöver hantera problem med datum- och tidskontroll.

Använd ett anpassat saneringsskript

När en lagringspolicy inte finns tillgänglig för din tabell och tabellpartitionering inte är möjlig, kan du ta bort data från historiktabellen genom att använda ett anpassat saneringsskript. Denna process är endast möjlig när SYSTEM_VERSIONING = OFF. För att undvika datainkonsekvens, utför rensning antingen under ett underhållsfönster (när arbetsbelastningar som ändrar data inte är aktiva) eller inom en transaktion (vilket effektivt blockerar andra arbetsbelastningar). Den här åtgärden kräver CONTROL behörighet för aktuella tabeller och historiktabeller.

Rensningslogiken är densamma för varje temporal tabell, så du kan automatisera den genom en generisk lagrad procedur. Använd SQL Server Agent eller ett annat verktyg för att schemalägga den proceduren att köras varje dag, och iterera över varje temporär tabell där du vill begränsa datahistoriken.

Följande diagram illustrerar hur du organiserar din rensningslogik för en enda tabell för att minska effekten på de körande arbetsbelastningarna.

Här är några övergripande riktlinjer för att implementera processen:

• Ta bort historisk data i varje tidstabell i flera iterationer av små bitar. Börja från de äldsta raderna och gå vidare till de senaste. Undvik att radera alla rader i en enda transaktion, som föregående diagram visar. Även om ingen enskild chunk-storlek fungerar för alla scenarier, kan radering av mer än 10 000 rader i en enda transaktion innebära en betydande påföljd.

• Implementera varje iteration som en anropning av en generisk lagrad procedur, som tar bort en del av data från historiktabellen.

• Beräkna hur många rader du behöver ta bort för en enskild temporal tabell varje gång du anropar processen. Baserat på resultatet och antalet iterationer du vill ha, bestäm dynamiska splitpunkter för varje procedurankallelse.

• Planera en fördröjning mellan iterationer för en enskild tabell för att minska effekten på applikationer som får tillgång till den temporala tabellen.

Följande lagrad procedur tar bort data för en enda temporär tabell. Den upptäcker historiktabellen och kolumnen för periodens slut från katalogvyerna, och kör sedan tre satser i en transaktion: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, och SET SYSTEM_VERSIONING = ON. Gå igenom denna kod noggrant och justera den innan du tillämpar den i din miljö.

I SQL Server 2016 (13.x) måste de två första stegen köras i separata EXECUTE-instruktioner, eller så genererar SQL Server ett fel som liknar följande exempel:

Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO

CREATE PROCEDURE usp_CleanupHistoryData (
    @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS SYSNAME;

/* Generate script to discover history table name and
end of period column for given temporal table name */
EXECUTE sp_executesql N'
    SELECT @hst_tbl_nm = t2.name,
           @hst_sch_nm = s2.name,
           @period_col_nm = c.name
    FROM sys.tables AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS c
             ON p.end_column_id = c.column_id
            AND c.object_id = t1.object_id
    WHERE t1.name = @tblName
          AND s1.name = @schName',
    N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT;

IF @historyTableName IS NULL
   OR @historyTableSchema IS NULL
   OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

SET @disableVersioningScript = @disableVersioningScript +
    'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = OFF)';

SET @deleteHistoryDataScript = @deleteHistoryDataScript +
    ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
    WHERE [' + @periodColumnName + '] < ' + '''' +
    CONVERT (VARCHAR (128), @cleanupOlderThanDate, 126) + '''';

SET @enableVersioningScript = @enableVersioningScript +
    ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' +
    @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); ';

BEGIN TRANSACTION;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Relaterat innehåll

• Temporala tabeller
• Kom igång med systemversionsbaserade tidstabeller
• Systemkonsekvenskontroller för tidstabeller
• Partitionering med temporära tabeller
• överväganden och begränsningar för tidstabeller
• Säkerhet för temporära tabeller
• systemversionsbaserade tidstabeller med minnesoptimerade tabeller
• vyer och funktioner för temporala tabellmetadata