Adatok módosítása rendszerverziójú időtáblában

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

A rendszerverziójú időtáblák adatai normál adatmanipulációs nyelvi (DML) utasításokkal módosulnak, és egy fontos különbség van: az időszakoszlop adatai közvetlenül nem módosíthatók. Amikor az adatokat frissítik, verzióba kerülnek, és minden frissített sor előző verzióját beillesztik a történettáblázatba. Amikor az adatokat töröljük, a törlés logikus, és a sor átkerül a jelenlegi táblából a történettáblázatba; Az adatok nem törlődnek véglegesen.

Adatok beszúrása

Új adatok beszúrásakor figyelembe kell vennie a PERIOD oszlopokat, ha azok nem HIDDEN. A partícióváltást temporális táblákkal is használhatja.

Új adatok beszúrása látható időszak oszlopokkal

A INSERT utasítást úgy alakítsa ki, hogy figyelembe vegye a látható PERIOD oszlopokat az alábbiak szerint:

Ha a INSERT utasításban adja meg az oszloplistát, kihagyhatja a PERIOD oszlopokat, mert a rendszer automatikusan generál értékeket ezekhez az oszlopokhoz.

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

Ha a PERIOD utasítás oszloplistájában megadja a INSERT oszlopokat, akkor meg kell adnia a DEFAULT értéket.

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

Ha nem adja meg az oszloplistát a INSERT utasításban, adja meg a(z) DEFAULT oszlopokhoz PERIOD értéket.

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

Adatok beszúrása rejtett időszak oszlopokkal rendelkező táblába

Ha PERIOD oszlopok HIDDENvannak megadva, nem kell figyelembe vennie a PERIOD utasítás INSERT oszlopait. Ez a viselkedés garantálja, hogy az örökölt alkalmazások továbbra is működjenek, amikor engedélyezi a rendszerverziósítást a verziószámozás előnyeit élvező táblákon.

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

Adatok beszúrása a PARTITION SWITCH használatával

Ha a jelenlegi tábla partíciózva van, PARTITION SWITCH akkor hatékonyan töltheted be az adatokat egy üres partícióba, vagy párhuzamosan több partícióba is betöltheted.

A PARTITION SWITCH IN utasításban időbeli táblával használt átmeneti táblában definiálni kell a(z) SYSTEM_TIME PERIOD elemet, de magának a táblának nem kell időbeli táblának lennie. Ez biztosítja, hogy az átmeneti konzisztencia-ellenőrzések az adatbeszúrás során egy átmeneti táblába vagy SYSTEM_TIME időszak előre feltöltött átmeneti táblába való hozzáadásakor legyenek végrehajtva.

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

Ha pontdefiníció nélküli táblából próbál PARTITION SWITCH végrehajtani, hibaüzenet jelenik meg:

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.

Adatok frissítése

Az aktuális táblában lévő adatokat normál UPDATE utasítással frissítheti. Az aktuális táblában lévő adatokat a vészforgatókönyv előzménytáblájából frissítheti. Azonban nem frissítheti a PERIOD oszlopokat, és az előzménytáblában lévő adatokat közvetlenül nem frissítheti, miközben SYSTEM_VERSIONING = ON.

Ha a SYSTEM_VERSIONING-t OFF-re állítja, és frissíti az aktuális és előzménytáblák sorait, a rendszer nem őrzi meg a módosítások előzményeit.

Az aktuális tábla frissítése

Ebben a példában a ManagerID oszlop frissül minden olyan sorhoz, ahol a DeptID10. A PERIOD oszlopokra semmilyen módon nem hivatkoznak.

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

Azonban nem frissíthet PERIOD oszlopot, és nem frissítheti az előzménytáblát. Ebben a példában egy PERIOD oszlop frissítésére tett kísérlet hibát okoz.

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

Az utasítás a következő hibát generálja.

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

Az aktuális tábla frissítése az előzménytáblából

Az aktuális táblában található UPDATE használatával visszaállíthatja a tényleges sorállapotot érvényes állapotra egy adott időpontban a múltban. Gondoljon erre úgy, mint amikor egy utolsó jól ismert sorverzióra állítjuk vissza. Az alábbi példa az előzmények táblázatában szereplő értékek 2015. április 25-i visszaállítását mutatja be, ahol a DeptID10.

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;

Adatok törlése

Az aktuális táblában lévő adatokat normál DELETE utasítással törölheti. A törölt sorok záróidőszak oszlopa a mögöttes tranzakció kezdő időpontjával van feltöltve. Nem törölhet közvetlenül sorokat az előzménytáblából, amíg SYSTEM_VERSIONINGON. Ha beállítja a SYSTEM_VERSIONING = OFF-t, és töröl sorokat az aktuális és az előzménytáblából, a rendszer nem tartja meg a változások előzményeit.

A következő utasítások nem támogatottak a(z) SYSTEM_VERSIONING = ON esetében:

  • TRUNCATE
  • SWITCH PARTITION OUT az aktuális táblához
  • SWITCH PARTITION IN az előzménytáblához

A MERGE használatával módosíthatja a temporális tábla adatait

A MERGE művelet ugyanazokat a korlátozásokat támogatja, mint INSERT és UPDATE utasítások PERIOD oszlopokra vonatkozóan.

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

Kapcsolódó tartalom

  • Last updated on