Megosztás a következőn keresztül:

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

Az előzménytábla megadásának mérlegelésekor háromféleképpen hozhat létre rendszerverziójú temporális táblát:

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 annak is, hogy ideiglenes táblát hozzon létre, mivel nem igényel paramétert a SYSTEM_VERSIONING záradékban. Az alábbi példában egy új táblát hoznak létre, amelynek rendszerverziózását engedélyezték anélkül, hogy meghatároznák az előzményként használt tábla nevét.

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 névtelen előzménytáblák automatikusan ugyanabban a sémában jönnek létre, mint az aktuális vagy az 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.

Alapértelmezés szerint a rendszer létrehoz egy fürtözött indexet az előzménytáblához egy automatikusan generált névvel a IX_<history_table_name>formátumban. 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. Az alábbi példában egy új tábla jön létre, amelyben a rendszer verziókövetése engedélyezve van, és az előzménytábla neve kifejezetten meg van határozva.

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

A felhasználó által definiált előzménytáblával temporális tábla létrehozása kényelmes megoldás, ha a felhasználó egy előzménytáblát szeretne megadni adott tárolási beállításokkal és különböző indexekkel, amelyek az előzmény lekérdezésekhez vannak hangolva. Az alábbi példában egy felhasználó által definiált előzménytábla jön létre egy sémával, amely a létrehozott időbeli táblához igazodik. Ebben a felhasználó által definiált előzménytáblában a rendszer egy fürtözött oszlopcentrikus indexet és egy további, nem rendezett sortároló (B-fa) indexet hoz létre a pontkeresésekhez. A felhasználó által definiált előzménytábla létrehozása után a rendszer létrehozza a temporális táblát, amely a felhasználó által definiált előzménytáblát adja meg alapértelmezett előzménytáblaké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 elemzési lekérdezéseket szeretne futtatni az összesítéseket vagy ablakozási függvényeket alkalmazó előzményadatokon, a fürtözött oszloptár elsődleges indexként való létrehozása erősen ajánlott a tömörítési és lekérdezési teljesítményhez.

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élyezheti a rendszerverziózást egy meglévő, nem temporális táblán, például ha egyéni ideiglenes megoldást szeretne áttelepíteni a beépített támogatásba.

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

Meglévő tábla átalakításakor fontolja meg a HIDDEN záradék használatát az új PERIOD oszlopok (datetime2 oszlopok ValidFrom és ValidTo) elrejtéséhez, hogy ne befolyásolja azokat a meglévő alkalmazásokat, amelyek nem adnak meg kifejezetten oszlopneveket (például SELECT * vagy INSERT oszloplista nélkül) nem az új oszlopok kezelésére szolgálnak.

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 PERIOD hozzáadása adatkonzisztencia-ellenőrzést végez az aktuális táblán, így meggyőződhet arról, hogy az időszakoszlopok meglévő értékei érvényesek.

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. Ebben a példában feltételezzük, hogy az aktuális egyéni megoldás két külön felhasználói táblában (ProjectTaskCurrent és ProjectTaskHistory) osztja fel az aktuális és az előzményadatokat.

Ha a meglévő megoldás egyetlen táblával tárolja a tényleges és az előzménysorokat, akkor az adatokat két táblára kell felosztania a következő példában bemutatott migrálási 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 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

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 PERIOD hozzáadása adatkonzisztencia-ellenőrzést végez az aktuális táblán, így meggyőződhet arról, 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 részesíti előnyben, használja a ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;parancsot.

Kapcsolódó tartalom

  • Last updated on