Udostępnij za pośrednictwem

Modyfikowanie danych w tabeli czasowej w wersji systemowej

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i późniejsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Dane w tabeli czasowej w wersji systemowej są modyfikowane przy użyciu regularnych instrukcji języka manipulowania danymi (DML), z jedną ważną różnicą: nie można bezpośrednio modyfikować danych kolumn okresowych. Gdy dane są aktualizowane, są wersjonowane, a poprzednia wersja każdego zaktualizowanego wiersza jest wstawiana do tabeli historii. Usunięcie danych jest logiczne, a wiersz zostaje przeniesiony z bieżącej tabeli do tabeli historii; dane nie są trwale usuwane.

Wstawianie danych

Podczas wstawiania nowych danych należy uwzględnić kolumny PERIOD, jeśli nie są HIDDEN. Można również użyć przełączania partycji z tabelami czasowymi.

Wstaw nowe dane z widocznymi kolumnami okresów

Możesz utworzyć instrukcję INSERT, gdy kolumny PERIOD są widoczne w następujący sposób, aby uwzględnić kolumny PERIOD:

Jeśli określisz listę kolumn w instrukcji INSERT, możesz pominąć kolumny PERIOD, ponieważ system automatycznie generuje wartości dla tych kolumn.

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

Jeśli określisz kolumny PERIOD na liście kolumn w instrukcji INSERT, musisz określić DEFAULT jako ich wartość.

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

Jeśli nie określisz listy kolumn w instrukcji INSERT, określ DEFAULT dla kolumn PERIOD.

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

Wstaw dane do tabeli z ukrytymi kolumnami okresów

Jeśli kolumny PERIOD są określone jako HIDDEN, nie musisz uwzględniać kolumn PERIOD w instrukcji INSERT. To zachowanie gwarantuje, że starsze aplikacje będą nadal działać po włączeniu obsługi wersji systemu w tabelach, które korzystają z obsługi wersji.

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

Wstawianie danych przy użyciu PRZEŁĄCZNIKA PARTYCJI

Jeśli bieżąca tabela jest podzielona na partycje, możesz użyć PARTITION SWITCH jako wydajnego mechanizmu ładowania danych do pustej partycji lub ładowania do wielu partycji równolegle.

Tabela przejściowa używana w instrukcji PARTITION SWITCH IN z tabelą czasową musi mieć zdefiniowaną SYSTEM_TIME PERIOD, ale nie musi być tabelą czasową. Gwarantuje to, że testy spójności czasowej są wykonywane podczas wstawiania danych do tabeli przejściowej lub gdy SYSTEM_TIME okres jest dodawany do wstępnie wypełnianej tabeli przejściowej.

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

Jeśli spróbujesz wykonać PARTITION SWITCH z tabeli bez definicji okresu, zostanie wyświetlony komunikat o błędzie:

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.

Aktualizowanie danych

Aktualizujesz dane w bieżącej tabeli za pomocą zwykłej instrukcji UPDATE. Dane w bieżącej tabeli można zaktualizować z tabeli historii na potrzeby scenariusza awarii. Nie można jednak zaktualizować kolumn PERIOD i nie można bezpośrednio aktualizować danych w tabeli historii podczas SYSTEM_VERSIONING = ON.

Jeśli ustawisz SYSTEM_VERSIONING na OFF i zaktualizujesz wiersze z tabel bieżących i historycznych, system nie zachowuje historii zmian.

Aktualizowanie bieżącej tabeli

W tym przykładzie kolumna ManagerID jest aktualizowana dla każdego wiersza, w którym DeptID jest 10. Kolumny PERIOD nie są w żaden sposób przywoływanych.

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

Nie można jednak zaktualizować kolumny PERIOD i nie można zaktualizować tabeli historii. W tym przykładzie próba zaktualizowania kolumny PERIOD generuje błąd.

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

Instrukcja generuje następujący błąd.

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

Zaktualizuj bieżącą tabelę z tabeli historii

Możesz użyć UPDATE w bieżącej tabeli, aby przywrócić rzeczywisty stan wiersza do prawidłowego stanu w określonym punkcie w czasie w przeszłości. Pomyśl o tym, jak przywrócić ostatniej znanej wersji wiersza. W poniższym przykładzie pokazano przywrócenie wartości w tabeli historii na dzień 25 kwietnia 2015 r., gdzie DeptID jest 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;

Usuwanie danych

Usuwasz dane w bieżącej tabeli za pomocą standardowej instrukcji DELETE. Kolumna oznaczająca koniec okresu dla usuniętych wierszy jest wypełniana czasem rozpoczęcia transakcji bazowej. Nie można bezpośrednio usunąć wierszy z tabeli historii, podczas gdy SYSTEM_VERSIONING jest ON. Jeśli ustawisz SYSTEM_VERSIONING = OFF i usuniesz wiersze z tabel bieżących i historycznych, system nie zachowuje historii zmian.

Następujące instrukcje nie są obsługiwane podczas SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT dla bieżącej tabeli
  • SWITCH PARTITION IN dla tabeli historii

Używanie funkcji MERGE do modyfikowania danych w tabeli czasowej

Operacja MERGE jest obsługiwana z tymi samymi ograniczeniami, które mają instrukcje INSERT i UPDATE dotyczące kolumn 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);

Powiązana zawartość