Rendszerverziójú temporális tábla létrehozása

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Háromféleképpen hozhatsz létre rendszer-verziójú időbeli táblát, attól függően, hogyan állítod be a történettáblázatot:

Ideiglenes tábla létrehozása névtelen előzménytáblával

A névtelen előzménytáblával rendelkező ideiglenes tábla létrehozása kényelmes lehetőség a gyors objektumlétrehozáshoz, különösen prototípusokban és tesztkörnyezetekben. Ez a legegyszerűbb módja az időbeli tábla létrehozásának, mert nem igényel semmilyen paramétert a SYSTEM_VERSIONING klauzulában. A következő példa olyan új táblát hoz létre, amelyben a rendszerverziózás engedélyezve van, a naplóelőzmény-tábla nevének megadása nélkül.

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

A rendszerverziójú időtábláknak elsődleges kulccsal kell rendelkezniük, és pontosan egy PERIOD FOR SYSTEM_TIME kell definiálni két datetime2 oszlopmal, GENERATED ALWAYS AS ROW START vagy GENERATED ALWAYS AS ROW ENDdeklarálva.

A PERIOD oszlopokat a rendszer mindig nem nullázhatónak tekinti, még akkor is, ha nincs megadva nullázhatóság. Ha a PERIOD oszlopok explicit módon null értékűként vannak definiálva, a CREATE TABLE utasítás meghiúsul.

Az előzménytáblának mindig az aktuális vagy az időbeli táblához kell igazodnia az oszlopok, oszlopnevek, rendezés és adattípusok számától függően.

A Database Engine automatikusan létrehoz egy anonim történeti táblát ugyanabban a sémában, mint az aktuális vagy időbeli tábla.

A névtelen előzménytáblázat neve a következő formátummal rendelkezik: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Az utótag megadása nem kötelező, és csak akkor lesz hozzáadva, ha a táblanév első része nem egyedi.

Az előzmények tábla sorosan tárolt táblaként jön létre. PAGE tömörítést alkalmazzák, ha lehetséges, ha nem, a történeti tábla tömörítetlen. Egyes táblakonfigurációk, például SPARSE oszlopok például nem teszik lehetővé a tömörítést.

A történettáblázat számára egy alapértelmezett klaszterelt indexet hoznak létre automatikusan generált névvel, a formátumban IX_<history_table_name>. A fürtözött index tartalmazza a PERIOD oszlopokat (vég, kezdet).

A Fabric SQL-adatbázisban a létrehozott előzménytáblát nem tükrözi a Fabric OneLake.

Az aktuális táblát memóriaoptimalizált táblaként való létrehozásához tekintse meg a rendszer által verziózott temporális táblákat memóriaoptimalizált táblákkal.

Temporális tábla létrehozása alapértelmezett előzménytáblával

A temporális tábla létrehozása alapértelmezett előzménytáblával kényelmes választás, ha szabályozni szeretné az elnevezést, és továbbra is a rendszerre támaszkodva hozza létre az előzménytáblát az alapértelmezett konfigurációval. A következő példa egy új táblát hoz létre, amelyen a rendszerverzió engedélyezve van, a történet tábla neve pedig kifejezetten meg van definiálva.

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

Az előzménytábla ugyanazokkal a szabályokkal jön létre, mint a névtelen előzménytáblák létrehozásakor, és az alábbi szabályok kifejezetten a nevesített előzménytáblára vonatkoznak.

  • A sémanév kötelező a HISTORY_TABLE paraméterhez.

  • Ha a megadott séma nem létezik, a CREATE TABLE utasítás meghiúsul.

  • Ha a HISTORY_TABLE paraméter által megadott tábla már létezik, akkor az sémakonzisztenciája és időbeli adatkonzisztenciájaalapján ellenőrzi az újonnan létrehozott időbeli táblát. Ha érvénytelen előzménytáblát ad meg, a CREATE TABLE utasítás meghiúsul.

Időbeli tábla létrehozása felhasználó által definiált előzménytáblával

Egy időbeli tábla létrehozása felhasználó által definiált történettáblázattal kényelmes lehetőség, amikor egy történelmi táblát szeretnél megadni speciális tárolási opciókkal és különböző indexekkel, amelyek a történelmi lekérdezésekhez hangolódnak. A következő példában létrehozol egy felhasználó által definiált történettáblát, amelynek sémája az időbeli tábla mellett van igazítva. Ez a történettáblázat egy klaszterezett columnstore indexet és egy extra nem klaszterizált sortároló (B-fa) indexet tartalmaz a pontkeresésekhez. Miután létrehoztad a történettáblát, létrehozod az időbeli táblát, és megadod a felhasználó által definiált történettáblát alapértelmezett történettáblázatként.

Note

A dokumentáció általában a B-fa kifejezést használja az indexekre hivatkozva. A sorkataszterekben az adatbázismotor egy B+ fát implementál. Ez nem vonatkozik az oszlopcentrikus indexekre vagy a memóriaoptimalizált táblák indexére. További információ: SQL Server és Azure SQL index architektúrája és tervezési útmutatója.

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

Ha analitikus lekérdezéseket tervezel a történelmi adatokon, amelyek aggregátumokat vagy ablakos funkciókat használnak, akkor nagyon ajánlott egy klaszterezett oszloptároló létrehozása elsődleges indexként a tömörítés és a lekérdezések teljesítménye érdekében.

Ha időbeli táblákat szeretne használni az adatnaplózáshoz (azaz az aktuális táblából származó egyetlen sor előzménymódosításainak keresését), akkor egy csoportosított indexet tartalmazó sortár-előzménytáblát kell létrehoznia.

Az előzménytáblában nem lehetnek elsődleges kulcsok, idegen kulcsok, egyedi indexek, táblakorlátozások vagy eseményindítók. Nem konfigurálható módosítási adatrögzítésre, változáskövetésre, tranzakciós replikációra vagy egyesítési replikációra.

A Fabric SQL Database-ben és az Azure SQL Database-ben a Fabric-tükrözés konfigurálva van, ha egy meglévő táblát használ előzménytáblaként az időbeli tábla létrehozásakor, a meglévő tábla nem lesz tükrözve.

A nem időalapú tábla módosítása rendszerverziójú temporális táblázattá

Engedélyezheted a rendszerverziózást egy létező, nem időbeli táblán, például amikor egy egyedi időbeli megoldást szeretnél átvinni beépített támogatásra.

Előfordulhat például, hogy olyan táblák vannak, amelyekben a verziószámozást triggerekkel implementálják. A temporális rendszerverzió használata kevésbé összetett, és egyéb előnyöket is biztosít, például:

  • Nem módosítható előzmények
  • Az időutazó lekérdezések új szintaxisa
  • Jobb DML-teljesítmény
  • Minimális karbantartási költségek

Egy meglévő tábla átalakításakor fontold meg, hogy a HIDDEN klauzulát használd az új PERIOD oszlopok ( datetime2 oszlopok ValidFrom és ValidTo) elrejtésére, hogy elkerüld azokat az alkalmazásokat, amelyek nem határozzák meg kifejezetten oszlopneveket (például SELECT * vagy INSERT oszloplista nélkül), és nem új oszlopok kezelésére terveztek.

Verziószámozás hozzáadása nem időleges táblákhoz

Ha el szeretné kezdeni az adatokat tartalmazó nem időleges táblák módosításainak nyomon követését, hozzá kell adnia a PERIOD definíciót, és opcionálisan meg kell adnia egy nevet az SQL Server által létrehozott üres előzménytáblának:

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

A DATETIME2 pontosságának igazodnia kell az alapul szolgáló tábla pontosságához.

Remarks

Ha alapértelmezett értékekkel rendelkező, nem null értékű oszlopokat adunk hozzá egy adatokkal rendelkező meglévő táblához, az SQL Server Enterprise kiadáson kívüli összes kiadás esetében ez adatműveletnek számít (míg az SQL Server Enterprise kiadás esetében metaadat-művelet). Az SQL Server Standard kiadásban lévő adatokkal rendelkező nagy méretű előzménytáblák esetén a nem null oszlop hozzáadása költséges művelet lehet.

Gondosan kell kiválasztani az időszak kezdő és záró oszlopainak korlátozásait:

  • A kezdőoszlop alapértelmezett értéke azt határozza meg, hogy a meglévő sorok melyik időponttól tekinthetők érvényesnek. A jövőben nem adható meg dátum/idő pontként.

  • A befejezési időt egy adott datetime2 pontosság maximális értékeként kell megadni, például 9999-12-31 23:59:59 vagy 9999-12-31 23:59:59.9999999.

A(z) PERIOD hozzáadása adatkonzisztencia-ellenőrzést végez az aktuális táblán annak biztosítására, hogy az időszakoszlopok meglévő értékei érvényesek legyenek.

Ha a SYSTEM_VERSIONINGengedélyezésekor egy meglévő előzménytáblát ad meg, az aktuális és az előzménytáblában is adatkonzisztencia-ellenőrzést hajt végre. Kihagyható, ha további paraméterként adja meg DATA_CONSISTENCY_CHECK = OFF.

Meglévő táblák migrálása beépített támogatásba

Ez a példa bemutatja, hogyan migrálható egy meglévő megoldásból az eseményindítók alapján a beépített időbeli támogatásba. Ez a példa feltételezi, hogy a jelenlegi egyedi megoldás az aktuális és az előzményadatokat két különálló felhasználói táblába bontja (ProjectTaskCurrent és ProjectTaskHistory).

Ha a meglévő megoldásod egyetlen táblát használ a tényleges és történelmi sorok tárolására, akkor az adatokat két táblára kell osztanod a következő példában bemutatott migrációs lépések előtt. Először dobja el a trigger-t a jövőbeli időbeli táblán. Ezután győződjön meg arról, hogy a PERIOD oszlopok ne lehessenek null értékűek.

/* 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

A PERIOD definícióban lévő meglévő oszlopokra való hivatkozás implicit módon generated_always_type módosítja AS_ROW_START és AS_ROW_END ezekre az oszlopokra.

A(z) PERIOD hozzáadása adatkonzisztencia-ellenőrzést végez az aktuális táblán annak biztosítására, hogy az időszakoszlopok meglévő értékei érvényesek.

Nyomatékosan javasoljuk, hogy a meglévő adatok konzisztenciájának ellenőrzése érdekében állítsa be a SYSTEM_VERSIONING-t a DATA_CONSISTENCY_CHECK = ON-re.

Ha a rejtett oszlopokat preferálják, használd a következő parancsot:

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

Kapcsolódó tartalom

  • Last updated on