Vytvoření dočasné tabulky s verzí systému

Platí na: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Systémově verzi časové tabulky můžete vytvořit třemi způsoby, podle toho, jak zadáte tabulku historie:

  • Časová tabulka s anonymní tabulkou historie: určíte schéma aktuální tabulky a necháte systém vytvořit odpovídající tabulku historie s automaticky generovaným jménem.

  • Dočasná tabulka s výchozí tabulkou historie : zadáte název schématu tabulky historie a název tabulky a necháte systém vytvořit v daném schématu tabulku historie.

  • Dočasná tabulka s uživatelsky definovanou tabulkou historie vytvořena předem: vytvoříte tabulku historie, která nejlépe vyhovuje vašim potřebám, a pak na tuto tabulku při vytváření dočasné tabulky odkazuje.

Vytvoření dočasné tabulky s anonymní tabulkou historie

Vytvoření dočasné tabulky s anonymní tabulkou historie je vhodná možnost rychlého vytváření objektů, zejména v prototypech a testovacích prostředích. Je to také nejjednodušší způsob, jak vytvořit časovou tabulku, protože nevyžaduje žádný parametr ve SYSTEM_VERSIONING vězle. Následující příklad vytváří novou tabulku s povoleným systémovým verzováním, aniž by definoval název historické tabulky.

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

Dočasná tabulka se systémem musí mít definovaný primární klíč a musí mít přesně jeden PERIOD FOR SYSTEM_TIME definovaný se dvěma sloupci datetime2, deklarován jako GENERATED ALWAYS AS ROW START nebo GENERATED ALWAYS AS ROW END.

Sloupce PERIOD se vždy považují za nenulové, i když není zadaná možnost null. Pokud jsou sloupce PERIOD explicitně definovány jako nullable, příkaz CREATE TABLE selže.

Tabulka historie musí být vždy zarovnaná se schématem s aktuální nebo dočasnou tabulkou s ohledem na počet sloupců, názvy sloupců, řazení a datové typy.

Database Engine automaticky vytváří anonymní historickou tabulku ve stejném schématu jako aktuální nebo časová tabulka.

Název tabulky anonymní historie má následující formát: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Přípona je nepovinná a přidá se jenom v případě, že první část názvu tabulky není jedinečná.

Tabulka historie je vytvořena jako tabulka typu rowstore. PAGE komprese se použije, pokud je to možné, v opačném případě se tabulka historie nekomprimuje. Například některé konfigurace tabulek, například SPARSE sloupce, neumožňují kompresi.

Pro tabulku historie je vytvořen výchozí shlukovaný index s automaticky generovaným názvem ve formátu IX_<history_table_name>. Clusterovaný index obsahuje sloupce PERIOD (konec, začátek).

Ve službě Fabric SQL Database se vytvořená tabulka historie nezrcadlí na Fabric OneLake.

Pokud chcete vytvořit aktuální tabulku jako tabulku optimalizovanou pro paměť, podívejte se na dočasné tabulky verze systému s tabulkami optimalizovanými pro paměť.

Vytvoření dočasné tabulky s výchozí tabulkou historie

Vytvoření dočasné tabulky s výchozí výchozí tabulkou historie je vhodná možnost, pokud chcete řídit pojmenování, a stále spoléháte na systém, aby se tabulka historie vytvořila s výchozí konfigurací. Následující příklad vytváří novou tabulku s povoleným systémovým verzováním, přičemž název tabulky historie je explicitně definován.

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

Tabulka historie se vytvoří pomocí stejných pravidel, která platí pro vytvoření "anonymní" tabulky historie s následujícími pravidly, která platí speciálně pro pojmenovanou tabulku historie.

  • Název schématu je povinný pro parametr HISTORY_TABLE.

  • Pokud zadané schéma neexistuje, příkaz CREATE TABLE selže.

  • Pokud tabulka zadaná parametrem HISTORY_TABLE již existuje, ověří se proti nově vytvořené dočasné tabulce z hlediska konzistence schématu a konzistence dočasných dat. Pokud zadáte neplatnou tabulku historie, příkaz CREATE TABLE selže.

Vytvoření dočasné tabulky s uživatelsky definovanou tabulkou historie

Vytvoření časové tabulky s uživatelsky definovanou tabulkou historie je pohodlnou volbou, pokud chcete specifikovat tabulku historie se specifickými možnostmi ukládání a různými indexy přizpůsobenými historickým dotazům. V následujícím příkladu vytvoříte uživatelsky definovanou tabulku historie se schématem zarovnaným s časovou tabulkou. Tato historická tabulka má clusterovaný columnstore index a navíc neclusterovaný rowstore index (B-tree) pro bodové vyhledávání. Po vytvoření tabulky historie vytvoříte časovou tabulku a určíte uživatelsky definovanou tabulku historie jako výchozí tabulku historie.

Note

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

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

Pokud plánujete spouštět analytické dotazy na historických datech, která používají agregáty nebo okenní funkce, je velmi doporučeno vytvořit clustered columnstore jako primární index pro kompresi a výkon dotazů.

Pokud plánujete použít dočasné tabulky pro auditování dat (to znamená hledání historických změn pro jeden řádek z aktuální tabulky), měli byste vytvořit tabulku historie rowstore s clusterovaným indexem.

Tabulka historie nemůže mít primární klíč, cizí klíče, jedinečné indexy, omezení tabulek nebo triggery. Nedá se nakonfigurovat pro zachytávání dat změn, sledování změn, transakční replikaci, ani slučovací replikaci.

V databázi Fabric SQL a v Azure SQL Database s nastavením zrcadlení Fabric, se při vytváření temporální tabulky, použije existující tabulka jako tabulka historie. Stávající tabulka se přestane zrcadlit.

Změna netemporální tabulky na časovou tabulku se systémovou verzí

Na existující nečasové tabulce můžete povolit systémové verzování, například když chcete migrovat vlastní časové řešení na vestavěnou podporu.

Můžete mít například sadu tabulek, ve kterých se implementuje správa verzí pomocí triggerů. Použití dočasné správy verzí systému je méně složité a poskytuje další výhody, mezi které patří:

  • Neměnná historie
  • Nová syntaxe pro dotazy s časovým cestováním
  • Lepší výkon DML
  • Minimální náklady na údržbu

Při převodu existující tabulky zvažte použití klauzule HIDDEN k zakrytí nových PERIOD sloupců (sloupců ValidFromdatetime2 a ValidTo), abyste se vyhnuli ovlivnění existujících aplikací, SELECT * které explicitně nespecifikují názvy sloupců (například nebo INSERT nemají seznam sloupců) a nejsou navrženy pro nové sloupce.

Přidání správy verzí do netemporálních tabulek

Pokud chcete začít sledovat změny v tabulce, která neobsahuje data, musíte přidat definici PERIOD a volitelně zadat název prázdné tabulky historie, kterou SQL Server vytvoří za vás:

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

Přesnost DATETIME2 musí odpovídat přesnosti podkladové tabulky.

Remarks

Přidání nenulových sloupců s výchozím nastavením do existující tabulky s daty je velikost operace dat ve všech edicích kromě edice SQL Server Enterprise (ve které se jedná o operaci metadat). S velkou existující tabulkou historie s daty v edici SQL Server Standard může být přidání sloupce, který není null, nákladnou operací.

Omezení pro počáteční a koncové sloupce období musí být pečlivě zvolena:

  • Výchozí hodnota pro počáteční sloupec určuje, od kterého bodu v čase považujete existující řádky za platné. Nelze určit jako bod data a času v budoucnosti.

  • Koncový čas musí být zadán jako maximální hodnota pro danou přesnost data a času2, například 9999-12-31 23:59:59 nebo 9999-12-31 23:59:59.9999999.

Přidáním PERIOD se provede kontrola konzistence dat v aktuální tabulce, aby se ověřilo, že stávající hodnoty sloupců období jsou platné.

Pokud je při povolování SYSTEM_VERSIONINGzadána existující tabulka historie, provede se kontrola konzistence dat v aktuální tabulce i v tabulce historie. Pokud zadáte DATA_CONSISTENCY_CHECK = OFF jako dodatečný parametr, můžete ho přeskočit.

Migrace existujících tabulek na integrovanou podporu

Tento příklad ukazuje, jak migrovat z existujícího řešení na základě triggerů na integrovanou dočasnou podporu. Tento příklad předpokládá, že aktuální vlastní řešení rozděluje aktuální a historická data do dvou samostatných uživatelských tabulek (ProjectTaskCurrent a ProjectTaskHistory).

Pokud vaše stávající řešení používá jednu tabulku pro ukládání skutečných a historických řádků, měli byste data před migračními kroky uvedenými v následujícím příkladu rozdělit data do dvou tabulek. Nejprve odstraňte trigger z budoucí temporální tabulky. Ujistěte se, že sloupce PERIOD jsou nenulovatelné.

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

Odkazování na existující sloupce v definici PERIOD implicitně změní generated_always_type na AS_ROW_START a AS_ROW_END pro tyto sloupce.

Přidáním PERIOD se provede kontrola konzistence dat v aktuální tabulce, aby se ověřilo, že stávající hodnoty sloupců období jsou platné.

Důrazně doporučujeme nastavit SYSTEM_VERSIONING s DATA_CONSISTENCY_CHECK = ONpro vynucení kontrol konzistence dat u existujících dat.

Pokud jsou preferovány skryté sloupce, použijte následující příkaz:

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

Související obsah

  • Last updated on