Delen via

Gegevens in een systeem-gestandaardiseerde temporele tabel wijzigen

  • Artikel

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Gegevens in een tijdelijke tabel met systeemversies worden gewijzigd met behulp van DML-instructies (Regular Data Manipulat Language), met één belangrijk verschil: kolomgegevens van perioden kunnen niet rechtstreeks worden gewijzigd. Wanneer gegevens worden bijgewerkt, worden deze geversied en wordt de vorige versie van elke bijgewerkte rij ingevoegd in de geschiedenistabel. Wanneer gegevens worden verwijderd, is de verwijdering logisch en wordt de rij verplaatst naar de geschiedenistabel vanuit de huidige tabel; de gegevens worden niet permanent verwijderd.

Gegevens invoegen

Wanneer u nieuwe gegevens invoegt, moet u rekening houden met de PERIOD kolommen als ze niet HIDDENzijn. U kunt ook partitiewisselingen gebruiken met tijdelijke tabellen.

Nieuwe gegevens invoegen met zichtbare periodekolommen

U kunt de INSERT-instructie als volgt maken wanneer u PERIOD kolommen zichtbaar hebt om rekening te houden met de PERIOD kolommen:

Als u de kolomlijst in uw INSERT-instructie opgeeft, kunt u de PERIOD kolommen weglaten omdat het systeem automatisch waarden voor deze kolommen genereert.

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

Als u de PERIOD kolommen in de kolomlijst in uw INSERT verklaring opgeeft, moet u DEFAULT als waarde opgeven.

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

Als u de kolomlijst niet opgeeft in de instructie INSERT, geeft u DEFAULT op voor PERIOD kolommen.

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

Gegevens invoegen in een tabel met verborgen periodekolommen

Als PERIOD kolommen zijn opgegeven als HIDDEN, hoeft u geen rekening te houden met de PERIOD kolommen in uw INSERT-verklaring. Dit gedrag garandeert dat uw verouderde toepassingen blijven werken wanneer u systeemversiebeheer inschakelt voor tabellen die baat hebben bij versiebeheer.

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

Gegevens invoegen met PARTITION SWITCH

Als de huidige tabel is gepartitioneerd, kunt u PARTITION SWITCH gebruiken als een efficiënt mechanisme om gegevens in een lege partitie te laden of om parallel in meerdere partities te laden.

De stagingtabel die wordt gebruikt in de instructie PARTITION SWITCH IN met een tijdelijke tabel moet SYSTEM_TIME PERIOD gedefinieerd hebben, maar hoeft geen tijdelijke tabel te zijn. Dit zorgt ervoor dat tijdelijke consistentiecontroles worden uitgevoerd tijdens het invoegen van gegevens in een faseringstabel of wanneer SYSTEM_TIME periode wordt toegevoegd aan een vooraf ingevulde faseringstabel.

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

Als u probeert PARTITION SWITCH uit te voeren vanuit een tabel zonder periode-definitie, geeft dit een foutbericht:

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.

Gegevens bijwerken

U werkt gegevens in de huidige tabel bij met een reguliere UPDATE instructie. U kunt gegevens in de huidige tabel bijwerken vanuit de geschiedenistabel voor het noodscenario. U kunt echter geen kolommen bijwerken die door PERIOD zijn aangeduid en u kunt geen gegevens rechtstreeks bijwerken in de geschiedenistabel terwijl SYSTEM_VERSIONING = ONvan toepassing is.

Als u SYSTEM_VERSIONING op OFF instelt en rijen in de huidige en geschiedenistabellen bijwerkt, behoudt het systeem de geschiedenis van wijzigingen niet.

De huidige tabel bijwerken

In dit voorbeeld wordt de kolom ManagerID bijgewerkt voor elke rij waarin de DeptID is 10. Er wordt op geen enkele manier naar de PERIOD kolommen verwezen.

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

U kunt echter geen PERIOD kolom bijwerken en u kunt de geschiedenistabel niet bijwerken. In dit voorbeeld genereert een poging om een PERIOD kolom bij te werken een fout.

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

De statement genereert de volgende fout.

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

De huidige tabel bijwerken vanuit de geschiedenistabel

U kunt UPDATE in de huidige tabel gebruiken om de huidige rijstatus terug te zetten naar een geldige status op een bepaald tijdstip in het verleden. U kunt dit zien als terugkeren naar een laatste goede bekende rijversie . In het volgende voorbeeld ziet u hoe de waarden in de geschiedenistabel per 25 april 2015 worden hersteld, waarbij DeptID10is.

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;

Gegevens verwijderen

U verwijdert gegevens in de huidige tabel met een reguliere DELETE instructie. De kolom eindperiode voor verwijderde rijen wordt gevuld met de begintijd van de onderliggende transactie. U kunt rijen niet rechtstreeks uit de geschiedenistabel verwijderen terwijl SYSTEM_VERSIONING is ON. Als u SYSTEM_VERSIONING = OFF instelt en rijen verwijdert uit de huidige en geschiedenistabellen, blijft het systeem de geschiedenis van wijzigingen niet behouden.

De volgende verklaringen worden niet ondersteund met SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT voor de huidige tabel
  • SWITCH PARTITION IN voor de geschiedenistabel

Samenvoegen gebruiken om gegevens in een tijdelijke tabel te wijzigen

De MERGE-bewerking wordt ondersteund met dezelfde beperkingen als die van INSERT- en UPDATE-uitdrukkingen met betrekking tot PERIOD-kolommen.

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

Verwante inhoud