Ändern von Daten in einer temporalen Tabelle mit Systemversionsverwaltung

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

Daten in temporalen Tabellen mit Systemversionsverwaltung werden mithilfe von regulären Anweisungen in Datenbearbeitungssprache (DML) geändert, mit einem wichtigen Unterschied: Zeitraumspaltendaten können nicht direkt geändert werden. Wenn die Daten aktualisiert werden, werden sie mit einer Versionsangabe versehen, und die vorherige Version jeder aktualisierten Zeile wird in die Verlaufstabelle eingefügt. Wenn Daten gelöscht werden, ist der Löschvorgang logisch, und die Zeile wird aus der aktuellen Tabelle in die Verlaufstabelle verschoben. Die Daten werden nicht endgültig gelöscht.

Einfügen von Daten

Wenn Sie neue Daten einfügen, müssen Sie die PERIOD-Spalten bedenken, falls sie nicht HIDDEN sind. Bei temporalen Tabellen können Sie auch den Partitionsaustausch verwenden.

Einfügen von neuen Daten mit sichtbaren Zeitraumspalten

Sie können wie folgt Ihre INSERT-Anweisung erstellen, wenn Sie über sichtbare PERIOD-Spalten verfügen, um die PERIOD-Spalten zu berücksichtigen:

Falls Sie die Spaltenliste in Ihrer INSERT-Anweisung angeben, können Sie die PERIOD-Spalten auslassen, da das System automatisch Werte für jede dieser Spalten erstellt.

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

Falls Sie diePERIOD-Spalten in der Spaltenliste in Ihrer INSERT-Anweisung angeben, müssen Sie DEFAULT als ihren Wert angeben.

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

Falls Sie die Spaltenliste in Ihrer INSERT-Anweisung nicht angeben, geben Sie DEFAULT für PERIOD-Spalten an.

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

Einfügen von Daten in eine Tabelle mit ausgeblendeten Zeitraumspalten

Wenn PERIOD-Spalten als HIDDEN angegeben sind, müssen Sie die PERIOD-Spalten in Ihrer INSERT-Anweisung nicht berücksichtigen. Dieses Verhalten garantiert, dass Ihre älteren Anwendungen weiterhin funktionieren, wenn Sie die Systemversionsverwaltung für die Tabellen aktivieren, die von der Versionsverwaltung profitieren.

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

Einfügen von Daten mithilfe des PARTITIONSAUSTAUSCHS

Falls die aktuelle Tabelle partitioniert ist, können Sie den PARTITION SWITCH als effizienten Mechanismus zum Laden von Daten in eine leere Partition oder zum parallelen Laden in mehrere Partitionen verwenden.

Für die Stagingtabelle, die bei einer temporalen Tabelle in der PARTITION SWITCH IN-Anweisung verwendet wird, muss SYSTEM_TIME PERIOD definiert sein, aber es muss sich nicht um eine temporale Tabelle handeln. Das stellt sicher, dass temporale Konsistenzprüfungen während der Dateneinfügung in eine Stagingtabelle durchgeführt werden, oder wenn der SYSTEM_TIME-Zeitraum zu einer im Voraus ausgefüllten Stagingtabelle hinzugefügt wird.

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

Wenn Sie versuchen, PARTITION SWITCH über eine Tabelle ohne Zeitraumdefinitionen auszuführen, erhalten Sie eine Fehlermeldung:

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.

Daten aktualisieren

Sie aktualisieren Daten in der aktuellen Tabelle mit einer regulären UPDATE-Anweisung. Für den Notfall können Sie die Daten in der aktuellen Tabelle auch mithilfe der Verlaufstabelle aktualisieren. Sie können jedoch PERIOD-Spalten nicht aktualisieren, und Sie können die Daten in der Verlaufstabelle nicht direkt aktualisieren, während SYSTEM_VERSIONING = ON gilt.

Wenn Sie SYSTEM_VERSIONING auf OFF festlegen und Zeilen aus der aktuellen Tabelle und der Verlaufstabelle aktualisieren, behält das System den Änderungsverlauf nicht bei.

Aktualisieren der aktuellen Tabelle

In diesem Beispiel wird die Spalte ManagerID für jede Zeile aktualisiert, in der DeptID 10 ist. Auf die PERIOD-Spalten wird in keiner Weise verwiesen.

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

Sie können jedoch keine PERIOD-Spalte aktualisieren und Sie können die Verlaufstabelle nicht aktualisieren. In diesem Beispiel schlägt der Versuch, eine PERIOD-Spalte zu aktualisieren, fehl.

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

Die Anweisung erzeugt den folgenden Fehler.

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

Aktualisieren der aktuellen Tabelle aus der Verlaufstabelle

Sie können UPDATE für die aktuelle Tabelle verwenden, um den tatsächlichen Zeilenzustand auf einen gültigen Zustand zu einem bestimmten Zeitpunkt in der Vergangenheit wieder herzustellen. Stellen Sie sich dies als Wiederherstellung auf eine letzte bekannte gute Zeilenversion vor. In diesem Beispiel wird gezeigt, wie zu den Werten in der Verlaufstabelle zum 25. April 2015 zurückgekehrt wird, wobei DeptID 10 ist.

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;

Löschen von Daten

Sie löschen Daten in der aktuellen Tabelle mit einer regulären DELETE-Anweisung. Die Endzeitraums-Spalte für gelöschte Zeilen wird mit der Anfangszeit der zugrundeliegenden Transaktion aufgefüllt. Sie können keine Zeilen direkt aus der Verlaufstabelle löschen, wenn SYSTEM_VERSIONING ON ist. Wenn Sie SYSTEM_VERSIONING = OFF festlegen und Zeilen aus der aktuellen Tabelle und der Verlaufstabelle aktualisieren, behält das System den Änderungsverlauf nicht bei.

Folgende Anweisungen werden nicht unterstützt, wenn SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT für die aktuelle Tabelle
  • SWITCH PARTITION IN für die Verlaufstabelle

Verwenden von MERGE zum Ändern von Daten in der temporalen Tabelle

Der MERGE-Vorgang wird mit den gleichen Einschränkungen unterstützt, die bei INSERT- und UPDATE-Anweisungen für PERIOD-Spalten gelten.

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

Zugehöriger Inhalt