Sdílet prostřednictvím

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

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Existují tři způsoby, jak vytvořit dočasnou tabulku se systémovou verzí při zvažování, jak je zadaná tabulka historie:

  • Dočasná tabulka s tabulkou anonymní historie: zadáte schéma aktuální tabulky a necháte systém vytvořit odpovídající tabulku historie s automaticky vygenerovaným názvem.

  • 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 dočasnou tabulku, protože nevyžaduje žádný parametr v klauzuli SYSTEM_VERSIONING. V následujícím příkladu se vytvoří nová tabulka s povolenou správou systémových verzí bez definování názvu tabulky historie.

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

Poznámky

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.

Anonymní tabulka historie se automaticky vytvoří ve stejném schématu jako aktuální nebo dočasná 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 se vytvoří výchozí clusterovaný index s automaticky vygenerovaný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í. V následujícím příkladu se vytvoří nová tabulka s povolenou správou verzí systému s explicitně definovaným názvem tabulky historie.

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

Poznámky

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í dočasné tabulky s uživatelsky definovanou tabulkou historie je vhodná možnost, když chce uživatel zadat tabulku historie s konkrétními možnostmi úložiště a různými indexy vyladěnými na historické dotazy. V následujícím příkladu se vytvoří tabulka historie definovaná uživatelem se schématem, které je zarovnané s časovou tabulkou, která se vytvoří. Pro tuto uživatelem definovanou tabulku historie je vytvořen clusterovaný index columnstore a další neclusterovaný index rowstore (B-tree) pro vyhledávání bodů. Po vytvoření této uživatelem definované tabulky historie se vytvoří dočasná tabulka určující tabulku historie definovanou uživatelem jako výchozí tabulku historie.

Poznámka

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

Poznámky

Pokud plánujete spouštět analytické dotazy na historická data, která využívají agregační funkce nebo funkce pro okna, důrazně doporučujeme vytvořit clusterovaný 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í

Správu systémových verzí můžete povolit u existující nečasově dočasné tabulky, například když chcete migrovat vlastní dočasné řešení na integrovanou 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 ke skrytí nových sloupců PERIOD (sloupce datetime2ValidFrom a ValidTo), aby nedošlo k ovlivnění stávajících aplikací, které explicitně nezadávají názvy sloupců (například SELECT * nebo INSERT bez seznamu sloupců), nejsou navržené tak, aby zpracovávaly 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

Důležitý

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

Poznámky

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 provádí kontrola konzistence dat v aktuální tabulce, aby bylo zajištěno, ž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. V tomto příkladu předpokládáme, že aktuální vlastní řešení rozdělí 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 k ukládání skutečných a historických řádků, měli byste data rozdělit do dvou tabulek před postupem migrace uvedeným v následujícím příkladu. 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 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
    )
);

Poznámky

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í PERIOD provádí kontrolu konzistence dat v aktuální tabulce, aby se zajistilo, že stávající hodnoty pro sloupce časového 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 upřednostňované skryté sloupce, použijte příkaz ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Související obsah