Az előzményadatok megőrzésének kezelése rendszerverziójú időbeli táblákban

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL-adatbázis a Microsoft Fabricban

A rendszer által verziózott temporális táblák esetében az előzménytábla nagyobb mértékben növelheti az adatbázis méretét, mint a hagyományos táblák, különösen az alábbi feltételek mellett:

• Az előzményadatok hosszú ideig megmaradnak
• Önnek frissítéseket vagy törléseket túlsúlyban alkalmazó adatmódosítási mintázata van.

A nagy méretű és egyre növekvő előzménytáblák a tiszta tárolási költségek és az időbeli lekérdezések teljesítményadója miatt is problémát jelenthetnek. Az előzménytáblában lévő adatok kezelésére szolgáló adatmegőrzési szabályzat kialakítása fontos szempont minden időbeli tábla életciklusának tervezésében és kezelésében.

A temporális táblák adatmegőrzésének kezelése az egyes időtáblákhoz szükséges megőrzési időtartam meghatározásával kezdődik. A megőrzési szabályzatnak a legtöbb esetben az alkalmazás időbeli táblákat használó üzleti logikájának kell részét képeznie. Az adatnaplózási és az időutazási forgatókönyvek alkalmazásai például szigorú követelményeket támasztanak azzal kapcsolatban, hogy az előzményadatoknak mennyi ideig kell rendelkezésre állniuk az online lekérdezéshez.

Miután meghatározta az adatmegőrzési időtartamot, ki kell dolgoznia egy tervet az előzményadatok kezelésére. Döntse el, hogyan és hol tárolja az előzményadatokat, és hogyan törölheti a megőrzési követelményeknél régebbi előzményadatokat. Az előzményadatok időbeli előzménytáblában való kezelésére az alábbi módszerek állnak rendelkezésre:

• Táblaparticionálás
• egyéni törlési szkript
• adatmegőrzési szabályzat

Ezen megközelítések mindegyikével az előzményadatok migrálásának vagy tisztításának logikája az aktuális táblázatban az időszak végének megfelelő oszlopon alapul. Az egyes sorok időszakának vége határozza meg azt a pillanatot, amikor a sorverzió zárt, vagyis amikor az előzménytáblába kerül. A ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) feltétel például azt határozza meg, hogy az egy hónapnál régebbi előzményadatokat el kell távolítani vagy ki kell helyezni az előzménytáblából.

A cikkben szereplő példák a Rendszerverziójú temporális tábla létrehozása cikkben létrehozott mintákat használják.

particionált táblák és indexek a nagy táblák kezelhetőbbé és méretezhetőbbé teszik. A táblaparticionálási megközelítéssel egyéni adattisztítást vagy offline archiválást valósíthat meg egy időfeltétel alapján. A táblaparticionálás teljesítménybeli előnyöket is biztosít, ha időleges táblákat kérdez le az adatelőzmények egy részhalmazán a partícióeliminálás használatával.

A táblaparticionálással egy tolóablakot implementálhat, amellyel az előzményadatok legrégebbi részét áthelyezheti az előzménytáblából, és a megtartott rész méretének korát tekintve állandó marad. A tolóablakok az előzménytáblában a szükséges megőrzési időszakkal megegyező adatokat tartanak fenn. Az adatok előzménytáblából való kikapcsolásának művelete támogatott, míg SYSTEM_VERSIONINGON, ami azt jelenti, hogy karbantartási időszak bevezetése vagy a normál számítási feladatok blokkolása nélkül törölheti az előzményadatok egy részét.

A tolóablak két feladatkészlettel rendelkezik, amelyeket el kell végeznie:

• Particionálási konfigurációs feladat
• Ismétlődő partíciókarbantartási feladatok

Az ábrához tegyük fel, hogy hat hónapig szeretné megőrizni az előzményadatokat, és hogy minden hónapban egy külön partíción szeretné tárolni az adatokat. Tegyük fel azt is, hogy 2023 szeptemberében bekapcsolta a rendszer-verziózást.

A particionálási konfigurációs feladat létrehozza az előzmények táblájának kezdeti particionálási konfigurációját. Ebben a példában olyan számpartíciókat hoz létre, amelyek azonosak a csúszó ablak hónapban mérhető méretével, továbbá egy plusz üres partíciót is előkészít (ezt a cikk későbbi részében ismertetjük). Ez a konfiguráció biztosítja, hogy a rendszer az ismétlődő partíciókarbantartási feladat első indításakor megfelelően tárolja az új adatokat, és garantálja, hogy a partíciókat soha ne ossza fel adatokkal a költséges adatforgalom elkerülése érdekében. Ezt a feladatot a Transact-SQL használatával kell elvégeznie, a jelen cikkben később bemutatott példaszkript segítségével.

Az alábbi képen a kezdeti particionálási konfiguráció látható a hat hónapos adatok megőrzéséhez.

Az első és az utolsó partíció nyitott az alsó és a felső határon, azért, hogy minden új sornak legyen célpartíciója, függetlenül a particionálási oszlop értékétől. Az idő előrehaladtával az előzmények táblázatának új sorai magasabb partíciókba kerülnek. Amikor a hatodik partíció megtelik, eléri a célzott megőrzési időtartamot. Most kell először elindítani az ismétlődő partíciókarbantartási feladatot. Ebben a példában rendszeres időközönként, havonta egyszer kell futnia.

Az alábbi kép az ismétlődő partíciókarbantartási feladatokat mutatja be (a szakasz későbbi szakaszának részletes lépéseit lásd).

Az ismétlődő partíciókarbantartási feladatok részletes lépései a következők:

1. SWITCH OUT: Hozzon létre egy átmeneti táblát, majd váltson partíciót az előzménytábla és az előkészítési tábla között a ALTER TABLE utasítással a SWITCH PARTITION argumentummal (lásd a C példát. Partíciók váltása táblák között).

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

   A partíciókapcsolót követően igény szerint archiválhatja az adatokat az előkészítési táblából, majd elvetheti vagy csonkolhatja az előkészítési táblát, hogy készen álljon az ismétlődő partíciókarbantartási feladat következő végrehajtására.

2. MERGE RANGE: Egyesítse az üres partíciót 1 a ALTER PARTITION FUNCTIONMERGE RANGE használatával a partícióval 2 (lásd B példát). Ha ezzel a függvénnyel eltávolítja a legkisebb határt, az üres 1 partíciót hatékonyan össze lehet egyesíteni a korábbi 2 partícióval, így létrejön az új 1 partíció. A többi partíció is hatékonyan változtatja a sorszámait.

3. SPLIT RANGE: Hozzon létre egy új üres partíciót 7 a ALTER PARTITION FÜGGVÉNYSPLIT RANGE használatával (lásd az A példát). Ha ezzel a függvénnyel új felső határt ad hozzá, hatékonyan létrehozhat egy külön partíciót a következő hónapra.

Az alábbi Transact-SQL szkripttel hozza létre a partíciófüggvényt, a partíciós sémát, és hozza létre újra a fürtözött indexet, hogy a partíciós sémához, partíciókhoz igazodjon. Ebben a példában egy hat hónapos tolóablakot hoz létre havi partíciókkal, 2023 szeptemberétől kezdve.

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;

A következő Transact-SQL szkripttel tarthatja karban a partíciókat a tolóablakos forgatókönyvben. Ebben a példában a partíciót 2023 szeptemberére váltja ki a MERGE RANGEhasználatával, majd hozzáad egy új partíciót 2024 márciusához a SPLIT RANGEhasználatával.

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 that will be switched 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 that will be switched 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

Kissé módosíthatja az előző szkriptet, és használhatja rendszeres havi karbantartási folyamatban:

1. Az (1) lépésben hozzon létre egy új átmeneti táblát az eltávolítani kívánt hónaphoz (ebben a példában október lenne a következő).
2. A (3) lépésben hozza létre és ellenőrizze az eltávolítani kívánt adatok hónapjának megfelelő korlátozást: ValidTo <= N'2023-10-31T23:59:59.999' egy októberi partícióhoz.
3. A (4) lépésben SWITCH particionálja 1-et az újonnan létrehozott előkészítési táblába.
4. A (6) lépésben módosítsa a partíciófüggvényt úgy, hogy egyesíti az alsó határt az októberi adatok áthelyezése után: MERGE RANGE(N'2023-10-31T23:59:59.999'.
5. A (7) lépésben ossza fel a partíciófüggvényt, és hozzon létre egy új felső határt: SPLIT RANGE (N'2024-04-30T23:59:59.999' az októberi adatok áthelyezése után.

Az optimális megoldás azonban az lenne, ha rendszeresen futtatna egy általános Transact-SQL szkriptet, amely minden hónapban módosítás nélkül futtatja a megfelelő műveletet. Általánosíthatja az előző szkriptet, hogy a megadott paramétereket (az egyesítendő alsó határt és a partíció felosztásával létrehozott új határt) hajtsa végre. Ha el szeretné kerülni, hogy minden hónapban létre kelljen hozni egy átmeneti táblát, létrehozhat egyet előre, és újra felhasználhatja, ha módosítja az ellenőrzési kényszert, hogy megfeleljen az átkapcsolt partíciónak. További információkért tekintse meg, hogyan lehet teljesen automatizálni a csúszóablakot.

Az adatáthelyezés elkerülése érdekében el kell végeznie a MERGE és SPLIT RANGE műveleteket, mivel az adatáthelyezés jelentős teljesítményterhelést okozhat. További információ: Partíciófüggvény módosítása. Ezt úgy teheti meg, hogy RANGE LEFT-t használ a RANGE RIGHT helyett, amikor a partíciófüggvényt létrehozza.

Az alábbi diagram a RANGE LEFT és a RANGE RIGHT beállításait ismerteti:

Ha egy partíciófüggvényt RANGE LEFTdefiniál, a megadott értékek a partíciók felső határai. Ha a RANGE RIGHTfunkciót használja, a megadott értékek a partíciók alsó határai. Ha a MERGE RANGE művelettel eltávolít egy határt a partíciófüggvény definíciójából, az alapul szolgáló implementáció a határt tartalmazó partíciót is eltávolítja. Ha a partíció nem üres, a rendszer áthelyezi az adatokat a MERGE RANGE művelet eredményeként létrejött partícióra.

Egy tolóablakos forgatókönyvben mindig eltávolítja a legkisebb partícióhatárt.

• RANGE LEFT eset: A legkisebb partícióhatár a 1partícióhoz tartozik, amely üres (a partíció kicserélése után), így MERGE RANGE nem jár adatáthelyezéssel.

• RANGE RIGHT eset: A legkisebb partícióhatár a 2partícióhoz tartozik, amely nem üres, mert a 1 partíció üres lett a kikapcsolás miatt. Ebben az esetben MERGE RANGE adatok átmozgatása történik (a 2 partíció adatai a 1partícióba kerülnek át). Ennek elkerülése érdekében a csúszóablak forgatókönyvben a RANGE RIGHT-nak egy 1 partícióval kell rendelkeznie, amely mindig üres. Ez azt jelenti, hogy ha RANGE RIGHThasznál, a RANGE LEFT-esethez képest egy további partíciót kell létrehoznia és fenntartania.

Következtetés: A partíciókezelés egyszerűbb, ha egy tolópartícióban használja a RANGE LEFT-t, és így elkerüli az adatáthelyezést. A partícióhatárok RANGE RIGHT definiálása azonban kissé egyszerűbb, mivel nem kell foglalkoznia a dátum- és időellenőrzéssel.

Ha a táblaparticionálás nem megvalósítható, egy másik módszer az adatok törlése az előzménytáblából egy egyedi törlési szkript használatával. Az adatok törlése az előzménytáblából csak akkor lehetséges, ha SYSTEM_VERSIONING = OFF. Az adatok inkonzisztencia elkerülése érdekében végezze el a karbantartást egy karbantartási időszak alatt (ha az adatokat módosító számítási feladatok nem aktívak), vagy egy tranzakción belül (ami hatékonyan blokkolja a többi számítási feladatot). Ehhez a művelethez CONTROL engedély szükséges az aktuális és az előzménytáblákhoz.

A normál alkalmazások és felhasználói lekérdezések minimális letiltásához törölje az adatokat kisebb adattömbökben késleltetéssel, amikor egy tranzakcióban végrehajtja a törlési szkriptet. Bár nincs optimális méret az egyes adattömbök törléséhez minden forgatókönyv esetében, egy tranzakció több mint 10 000 sorának törlése jelentős büntetést vonhat maga után.

A törlési logika minden temporális táblánál ugyanaz, ezért egy általános tárolt eljáráson keresztül automatizálható, amelyet rendszeres időközönként futtat, minden olyan időbeli táblához, amelyhez korlátozni szeretné az adatelőzményeket.

Az alábbi ábra bemutatja, hogyan kell rendszerezni a tisztítási logikát egyetlen táblához a futó számítási feladatokra gyakorolt hatás csökkentése érdekében.

Íme néhány magas szintű irányelv a folyamat végrehajtásához. Ütemezze a karbantartási logikát, hogy minden nap fusson, és iterálja az összes olyan időbeli táblát, amely adattisztítást igényel. A folyamat ütemezéséhez használja az SQL Server-ügynököt vagy egy másik eszközt:

• Törölje az előzményadatokat minden temporális táblában, kezdve a legrégebbitől a legutóbbi sorokig több iterációban, kis adattömbökben, és ne törölje egyetlen tranzakció összes sorát, ahogyan az az előző ábrán is látható.

• Implementáljon minden iterációt egy általános tárolt eljárás meghívásaként, amely eltávolítja az adatok egy részét az előzménytáblából (lásd az alábbi példakódot ehhez az eljáráshoz).

• Számítsa ki, hogy hány sort kell törölnie az egyes időtáblákhoz minden alkalommal, amikor meghívja a folyamatot. Az eredmény és a kívánt iterációk száma alapján határozza meg a dinamikus felosztási pontokat minden eljáráshíváshoz.

• Tervezze meg, hogy egy adott tábla iterációi között késleltetési időszak áll rendelkezésére, hogy csökkentse az időbeli táblához hozzáférő alkalmazásokra gyakorolt hatást.

Egy tárolt eljárás, amely egyetlen időbeli tábla adatait törli, az alábbi kódrészlethez hasonlóan nézhet ki. Tekintse át alaposan ezt a kódot, és módosítsa, mielőtt alkalmazaná a környezetben.

Ez a szkript három utasítást hoz létre, amelyek egy tranzakción belül futnak:

1. SET SYSTEM_VERSIONING = OFF
2. DELETE FROM <history_table>
3. SET SYSTEM_VERSIONING = ON

Az SQL Server 2016 -ban (13.x) az első két lépésnek külön EXEC utasításokban kell futnia, vagy az SQL Server az alábbi példához hasonló hibát okoz:

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 NVARCHAR(MAX) = '';
DECLARE @deleteHistoryDataScript NVARCHAR(MAX) = '';
DECLARE @enableVersioningScript NVARCHAR(MAX) = '';
DECLARE @historyTableName SYSNAME
DECLARE @historyTableSchema SYSNAME
DECLARE @periodColumnName 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 t1
  INNER JOIN sys.tables t2 ON t1.history_table_id = t2.object_id
  INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id
  INNER JOIN sys.schemas s2 ON t2.schema_id = s2.schema_id
  INNER JOIN sys.periods p ON p.object_id = t1.object_id
  INNER JOIN sys.columns 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
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

A következőkre vonatkozik: SQL Server 2017 (14.x) és újabb verziók, valamint az Azure SQL Database.

Az időelőzmények megőrzése egyéni táblaszinten konfigurálható, így a felhasználók rugalmas öregedési szabályzatokat hozhatnak létre. Az időbeli megőrzéshez csak egy paramétert kell beállítania a tábla létrehozásakor vagy sémamódosításkor.

A megőrzési szabályzat meghatározása után az adatbázismotor rendszeresen ellenőrzi, hogy vannak-e olyan előzménysorok, amelyek jogosultak az automatikus adattisztításra. Az egyező sorok azonosítása és eltávolítása az előzménytáblából transzparens módon történik a rendszer által ütemezett és futtatott háttérfeladatban. Az előzménytábla sorainak korfeltételét a SYSTEM_TIME időszak végét jelző oszlop (ebben a példában a ValidTo oszlop) alapján ellenőrzi a rendszer. Ha a megőrzési időtartam hat hónapra van állítva, például a törlésre jogosult táblasorok megfelelnek a következő feltételnek:

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

Az előző példában a ValidTo oszlop a SYSTEM_TIME időszak végének felel meg.

Mielőtt konfigurálja az időalapú táblák adatmegőrzési szabályzatát, ellenőrizze, hogy engedélyezve van-e az időbeli előzménymegőrzés az adatbázis szintjén:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Az adatbázisjelző is_temporal_history_retention_enabled alapértelmezés szerint ON van beállítva, de a ALTER DATABASE utasítással módosíthatja. Ez az érték automatikusan OFF értékre van állítva egy időponthoz kötött visszaállítási (PITR) művelet után. Az adatbázis időelőzmény-megőrzésének törlését engedélyezheti az alábbi utasítás futtatásával. Az <myDB> helyére be kell írnia a módosítani kívánt adatbázist.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

A megőrzési szabályzat a tábla létrehozása során van konfigurálva a HISTORY_RETENTION_PERIOD paraméter értékének megadásával:

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

A megőrzési időtartamot különböző időegységekkel adhatja meg: DAYS, WEEKS, MONTHSés YEARS. Ha HISTORY_RETENTION_PERIOD nincs megadva, INFINITE megőrzést feltételezzük. A INFINITE kulcsszót explicit módon is használhatja.

Bizonyos esetekben érdemes lehet a tábla létrehozása után konfigurálni a megőrzést, vagy módosítani a korábban konfigurált értéket. Ebben az esetben használja a ALTER TABLE utasítást:

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

A megőrzési szabályzat aktuális állapotának áttekintéséhez használja az alábbi mintát. Ez a lekérdezés az adatbázis szintjén összekapcsolja az ideiglenes adatmegőrzés engedélyezésének jelzőt az egyes táblák megőrzési időszakaival:

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 T1
OUTER APPLY (
    SELECT is_temporal_history_retention_enabled
    FROM sys.databases
    WHERE name = DB_NAME()
    ) AS DB
LEFT JOIN sys.tables T2
    ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

A törlési folyamat az előzménytábla indexelrendezésétől függ. Csak a fürtözött indexet (B+ fa vagy oszloptár) tartalmazó előzménytáblák rendelkezhetnek véges adatmegőrzési szabályzattal. Létrejön egy háttérfeladat, amely a véges megőrzési időszakkal rendelkező összes időbeli tábla elavult adattisztítását hajtja végre. A sortár (B+ fa) fürtözött indexének tisztítási logikája kisebb adattömbökben (legfeljebb 10 000) törli az elöregedett sorokat, ezzel minimalizálva az adatbázisnaplóra és az I/O-alrendszerre nehezedő nyomást. Bár a törlési logika a szükséges B+ faindexet használja, a megőrzési időnél régebbi sorok törlési sorrendje nem garantálható. Ne függjön az alkalmazásokban a takarítási sorrendre.

A fürtözött oszloptár törlési feladata egyszerre távolítja el a teljes sorcsoportokat (általában egyenként 1 millió sort tartalmaz), ami hatékonyabb, különösen akkor, ha az előzményadatok nagy ütemben jönnek létre.

Az adattömörítés és a megőrzési adatok tisztítása tökéletes választássá teszik a fürtözött oszlopcentrikus indexet olyan helyzetekben, amikor a számítási feladat gyorsan nagymennyiségű előzményadatot generál. Ez a minta olyan intenzív tranzakciófeldolgozási számítási feladatokra jellemző, amelyek időbeli táblákat használnak a változáskövetéshez és -naplózáshoz, a trendelemzéshez vagy az IoT-adatbetöltéshez.

További információ: Korábbi adatok kezelése időbeli táblákban adatmegőrzési szabályzattal.

• temporális táblák
• Kezdje el a rendszerverziójú temporális táblák használatát
• Temporális táblarendszer konzisztenciájának ellenőrzése
• partíció temporális táblákkal
• Időbeli táblázatokkal kapcsolatos megfontolások és korlátozások
• Temporális tábla biztonság
• rendszerverziójú időtáblák memóriaoptimalizált táblákkal
• Időbeli tábla metaadatnézetei és függvényei