시스템 버전 관리 temporal 테이블의 데이터 수정

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance

시스템 버전 관리 temporal 테이블의 데이터는 한 가지 중요한 차이점이 있는 일반 DML(데이터 조작 언어) 문을 사용하여 수정됩니다. 기간 열 데이터는 직접 수정할 수 없습니다. 데이터가 업데이트되면 버전이 지정되고 업데이트된 각 행의 이전 버전이 기록 테이블에 삽입됩니다. 데이터가 삭제되면 삭제는 논리적이며 행이 현재 테이블의 기록 테이블로 이동되며 데이터는 영구적으로 삭제되지 않습니다.

데이터 삽입

새 데이터를 삽입할 때는 HIDDEN이 아닌 경우 PERIOD 열을 고려해야 합니다. temporal 테이블과 함께 파티션 전환을 사용할 수도 있습니다.

표시되는 기간 열을 사용하여 새 데이터 삽입

다음과 같이 PERIOD 열이 표시되어 있는 경우 PERIOD 열을 설명하기 위해 INSERT 문을 구성할 수 있습니다.

INSERT 문에서 열 목록을 지정하는 경우 시스템에서 이러한 열에 대한 값을 자동으로 생성하므로 PERIOD 열을 생략할 수 있습니다.

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

INSERT 문의 열 목록에 PERIOD 열을 지정하면 해당 값으로 DEFAULT를 지정해야 합니다.

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

INSERT 문의 열 목록을 지정하지 않으면 PERIOD 열에 DEFAULT를 지정합니다.

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

HIDDEN 기간 열이 있는 테이블에 데이터 삽입

PERIOD 열이 HIDDEN으로 지정된 경우 INSERT 문의 열을 PERIOD를 고려할 필요가 없습니다. 이 동작은 버전 관리의 이점을 얻을 수 있는 테이블에서 시스템 버전 관리 기능을 사용할 때 레거시 애플리케이션이 계속 작동하도록 보장합니다.

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

PARTITION SWITCH를 사용하여 데이터 삽입

현재 테이블이 분할된 경우 PARTITION SWITCH를 효율적인 메커니즘으로 사용하여 빈 파티션에 데이터를 로드하거나 여러 파티션에 병렬로 로드할 수 있습니다.

temporal 테이블이 있는 PARTITION SWITCH IN 문에 사용되는 준비 테이블은 SYSTEM_TIME PERIOD가 정의되어 있어야 하지만 temporal 테이블일 필요는 없습니다. 이렇게 하면 준비 테이블로 데이터를 삽입하거나 미리 채워진 준비 테이블에 SYSTEM_TIME 기간이 추가되는 동안 temporal 일관성 확인이 수행되도록 합니다.

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

기간 정의가 없는 테이블에서 PARTITION SWITCH를 수행하려고 하면 오류 메시지가 표시됩니다.

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.

데이터 업데이트

일반 UPDATE 문으로 현재 테이블의 데이터를 업데이트합니다. 재해 시나리오의 기록 테이블에서 현재 테이블의 데이터를 업데이트할 수 있습니다. 그러나 PERIOD 열을 업데이트할 수 없으며 SYSTEM_VERSIONING = ON인 동안에는 기록 테이블의 데이터를 직접 업데이트할 수 없습니다.

SYSTEM_VERSIONING을 OFF로 설정하고 현재 및 기록 테이블의 행을 업데이트하는 경우 시스템은 변경 기록을 유지하지 않습니다.

현재 테이블 업데이트

이 예제에서는 DeptID가 10인 각 행에 대해 ManagerID 열이 업데이트됩니다. PERIOD 열은 어떤 방식으로도 참조되지 않습니다.

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

하지만 PERIOD 열을 업데이트할 수 없으며 기록 테이블을 업데이트할 수 없습니다. 이 예제에서는 PERIOD 열을 업데이트하려고 하면 오류가 발생합니다.

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

문은 다음과 같은 오류를 생성합니다.

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

기록 테이블에서 현재 테이블 업데이트

현재 테이블의 UPDATE를 사용하여 실제 행 상태를 과거 특정 시점의 유효한 상태로 되돌릴 수 있습니다. 이를 마지막으로 잘 알려진 행 버전으로 되돌리는 것으로 생각하세요. 다음 예에서는 DeptID가 10인 2015년 4월 25일 기준 기록 테이블의 값으로 되돌리는 방법을 보여줍니다.

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;

데이터 삭제

일반 DELETE 문을 사용하여 현재 테이블의 데이터를 삭제합니다. 삭제된 행의 종료 기간 열은 기본 트랜잭션의 시작 시간으로 채워집니다. SYSTEM_VERSIONING이 ON인 상태이면 기록 테이블에서 행을 직접 삭제할 수 없습니다. SYSTEM_VERSIONING = OFF를 설정하고 현재 및 기록 테이블에서 행을 삭제하는 경우 시스템은 변경 기록을 유지하지 않습니다.

SYSTEM_VERSIONING = ON인 동안 다음 문은 지원되지 않습니다.

  • TRUNCATE
  • 현재 테이블의 경우 SWITCH PARTITION OUT
  • 기록 테이블의 경우 SWITCH PARTITION IN

MERGE를 사용하여 temporal 테이블의 데이터 수정

MERGE 작업은 PERIOD 열과 관련하여 INSERT 및 UPDATE 문과 동일한 제한 사항으로 지원됩니다.

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

관련 콘텐츠