Modifica dei dati in una tabella temporale con controllo delle versioni di sistema

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di Azure Istanza gestita di SQL di Azure

È possibile modificare i dati in una tabella temporale con controllo delle versioni di sistema usando normali istruzioni Data Manipulation Language (DML), tenendo però presente che i dati della colonna periodo non possono essere modificati direttamente. Quando i dati vengono aggiornati, sono sottoposti al controllo delle versioni e la versione precedente di ogni riga aggiornata viene inserita nella tabella di cronologia. Quando vengono eliminati, l'eliminazione è logica e la riga viene spostata dalla tabella corrente alla tabella di cronologia; i dati non sono eliminati definitivamente.

Inserire i dati

Quando si inseriscono nuovi dati è necessario tenere conto delle colonne PERIOD se non sono HIDDEN. È possibile utilizzare la commutazione delle partizioni anche con le tabelle temporali.

Inserire nuovi dati con colonne periodo visibili

È possibile costruire l’istruzione INSERT quando si hanno colonne PERIOD visibili come le seguenti, per tenere conto delle nuove colonne PERIOD:

Se si specifica l'elenco delle colonne nell’istruzione INSERT, è possibile omettere le colonne PERIOD, perché il sistema genera automaticamente i relativi valori.

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

Se si specificano le colonne PERIOD nell'elenco colonne nell’istruzione INSERT, è necessario specificare anche DEFAULT come relativo valore.

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

Se non si specifica l'elenco colonne nell’istruzione INSERT, specificare DEFAULT per le colonne PERIOD.

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

Inserire dati in una tabella con colonne periodo HIDDEN

Se le colonne PERIOD vengono specificate come HIDDEN, non è necessario tenere conto delle colonne PERIOD nell'istruzione INSERT. In questo modo le applicazioni legacy continueranno a funzionare quando si abilita il controllo delle versioni di sistema nelle tabelle a cui verrà applicata la funzionalità.

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

Inserire i dati con PARTITION SWITCH

Se la tabella corrente è partizionata è possibile usare PARTITION SWITCH per caricare in modo efficiente i dati in una partizione vuota oppure su più partizioni in parallelo.

La tabella di staging usata nell'istruzione PARTITION SWITCH IN con una tabella temporale deve essere definita SYSTEM_TIME PERIOD, ma non deve essere una tabella temporale. Ciò assicura l’esecuzione di controlli di coerenza temporale durante l'inserimento di dati in una tabella di staging o quando il periodo SYSTEM_TIME viene aggiunto a una tabella di staging già popolata.

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

Se si tenta di eseguire l'istruzione PARTITION SWITCH da una tabella priva di definizione del periodo verrà visualizzato il messaggio di errore:

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.

Aggiornamento dei dati

I dati nella tabella corrente vengono aggiornati con una normale istruzione UPDATE. È possibile aggiornare i dati nella tabella corrente dalla tabella di cronologia per lo scenario problematico. Non è tuttavia possibile aggiornare le colonne PERIOD né aggiornare direttamente i dati nella tabella di cronologia se SYSTEM_VERSIONING = ON.

Se si imposta SYSTEM_VERSIONING su OFF e si aggiornano le righe dalle tabelle correnti e di cronologia, il sistema non mantiene la cronologia delle modifiche.

Aggiornare la tabella corrente

In questo esempio, la colonna ManagerID viene aggiornata per ogni riga in cui DeptID è 10. Non c’è alcun riferimento alle colonne PERIOD.

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

Tuttavia, non è possibile aggiornare una colonna PERIOD né la tabella di cronologia. In questo esempio, il tentativo di aggiornare una colonna PERIOD genera un errore.

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

La seguente istruzione può generare questo errore.

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

Aggiornare la tabella corrente dalla tabella di cronologia

È possibile usare l'istruzione UPDATE nella tabella corrente per ripristinare lo stato corrente della riga a uno specifico stato valido precedente. Si pensi a questo come ripristinare un'ultima versione di riga nota. Nell'esempio seguente i valori nella tabella di cronologia vengono ripristinati al 25 aprile 2015, quando DeptID è 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;

Eliminare dati

È possibile eliminare dati dalla tabella corrente con una normale istruzione DELETE. La colonna periodo finale delle righe eliminate verrà popolata con l'ora di inizio della transazione sottostante. Non è possibile eliminare direttamente le righe dalla tabella di cronologia se SYSTEM_VERSIONING è ON. Se si impostano SYSTEM_VERSIONING = OFF e si eliminano righe dalle tabelle correnti e di cronologia, il sistema non mantiene la cronologia delle modifiche.

Le seguenti istruzioni non sono supportate se SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT per la tabella corrente
  • SWITCH PARTITION IN per la tabella di cronologia

Utilizzare MERGE per modificare i dati in una tabella temporale

L’operazione MERGE è supportata con le stesse limitazioni delle istruzioni INSERT e UPDATE relativamente alle colonne 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);

Contenuto correlato