Sdílet prostřednictvím

Úprava dat v systémově verzované časové tabulce

  • Článek

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

Data v dočasné tabulce s systémovou verzí se upravují pomocí regulárních příkazů jazyka pro manipulaci s daty (DML) s jedním důležitým rozdílem: data ve sloupcích období se nedají přímo upravovat. Když se data aktualizují, jsou verzována a předchozí verze každého aktualizovaného řádku se vloží do historické tabulky. Při odstranění dat je odstranění pouze logické; řádek je přesunut z aktuální tabulky do tabulky historie a data se trvale neodstraní.

Vložení dat

Když vložíte nová data, je potřeba zohlednit sloupce PERIOD, pokud nejsou HIDDEN. Můžete také použít přepínání oddílů s časovými tabulkami.

Vložte nová data se sloupci pro viditelná období

Příkaz INSERT můžete vytvořit, když vidíte sloupce PERIOD následujícím způsobem, aby se zohlednily sloupce PERIOD:

Pokud v příkazu INSERT zadáte seznam sloupců, můžete vynechat PERIOD sloupce, protože systém generuje hodnoty pro tyto sloupce automaticky.

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department] (
      [DeptID],
      [DeptName],
      [ManagerID],
      [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

Pokud ve svém příkazu PERIOD specifikujete INSERT sloupců v seznamu sloupců, musíte zadat DEFAULT jako jejich hodnotu.

INSERT INTO [dbo].[Department] (
   DeptID,
   DeptName,
   ManagerID,
   ParentDeptID,
   ValidFrom,
   ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

Pokud v příkazu INSERT nezadáte seznam sloupců, zadejte DEFAULT pro PERIOD sloupce.

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES(12, 'Production', 101, 1, DEFAULT, DEFAULT);

Vložte data do tabulky se skrytými periodickými sloupci

Pokud jsou sloupce PERIOD zadány jako HIDDEN, nemusíte zahrnovat sloupce PERIOD v příkazu INSERT. Toto chování zaručuje, že starší verze aplikací budou dál fungovat, když povolíte správu verzí systému v tabulkách, které mají prospěch z správy verzí.

CREATE TABLE [dbo].[CompanyLocation] (
    [LocID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [LocName] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

Vložení dat pomocí PŘEPÍNAČE ODDÍLŮ

Pokud je aktuální tabulka rozdělená na oddíly, můžete použít PARTITION SWITCH jako efektivní mechanismus pro načtení dat do prázdného oddílu nebo paralelní načtení do více oddílů.

Přechodná tabulka, která se používá v příkazu PARTITION SWITCH IN s časovou tabulkou, musí mít definovanou SYSTEM_TIME PERIOD, ale nemusí být časovou tabulkou. Tím zajistíte, že se během vkládání dat do pracovní tabulky nebo při přidání SYSTEM_TIME období do předem vyplněné pracovní tabulky provádějí dočasné kontroly konzistence.

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2] (
    [DeptID] [int] NOT NULL,
    [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])
) ON [PRIMARY]

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
ADD CONSTRAINT [Staging_Department_Partition2_PK]
PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2]
WITH CHECK ADD CONSTRAINT [chk_staging_Department_partition_2] CHECK (
   [DeptID] > N'100'
   AND [DeptID] <= N'200'
);

ALTER TABLE [dbo].[Staging_Department_Partition2]
CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
    )
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
SWITCH TO [dbo].[Department] PARTITION 2;

Pokud se pokusíte provést PARTITION SWITCH z tabulky bez definice časového období, zobrazí se chybová zpráva:

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

Aktualizace dat

Data v aktuální tabulce aktualizujete pravidelným příkazem UPDATE. Data v aktuální tabulce můžete aktualizovat z tabulky historie pro scénář havárie. Nemůžete ale aktualizovat PERIOD sloupce a během SYSTEM_VERSIONING = ONnemůžete přímo aktualizovat data v tabulce historie.

Pokud nastavíte SYSTEM_VERSIONING na OFF a aktualizujete řádky z aktuálních tabulek a tabulek historie, systém nezachová historii změn.

Aktualizace aktuální tabulky

V tomto příkladu se sloupec ManagerID aktualizuje pro každý řádek, ve kterém je DeptID10. Na sloupce PERIOD se neodkazuje žádným způsobem.

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

Nemůžete ale aktualizovat sloupec PERIOD a nemůžete aktualizovat tabulku historie. V tomto příkladu pokus o aktualizaci sloupce PERIOD vygeneruje chybu.

UPDATE [dbo].[Department]
SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

Příkaz vygeneruje následující chybu.

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

Aktualizace aktuální tabulky z tabulky historie

Pomocí UPDATE v aktuální tabulce můžete vrátit aktuální stav řádku k platnému stavu v určitém bodu v čase v minulosti. Představte si to tak, že se vrátíte k poslední známé verzi řádku. Následující příklad ukazuje návrat k hodnotám v tabulce historie od 25. dubna 2015, kde DeptID je 10.

UPDATE Department
SET DeptName = History.DeptName
FROM Department
FOR SYSTEM_TIME AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

Odstranění dat

Data v aktuální tabulce odstraníte pomocí běžného příkazu DELETE. Sloupec koncového období pro odstraněné řádky se naplní počátečním časem podkladové transakce. Řádky z tabulky historie nelze přímo odstranit, pokud je SYSTEM_VERSIONINGON. Pokud nastavíte SYSTEM_VERSIONING = OFF a odstraníte řádky z aktuálních tabulek a tabulek historie, systém nezachová historii změn.

Následující příkazy nejsou podporovány při používání SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT pro aktuální tabulku
  • SWITCH PARTITION IN pro tabulku historie

Úprava dat v dočasné tabulce pomocí funkce MERGE

Operace MERGE se podporuje se stejnými omezeními, která INSERT a příkazy UPDATE mají, pokud jde o sloupce PERIOD.

CREATE TABLE DepartmentStaging (
    DeptId INT,
    DeptName VARCHAR(50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);

Související obsah