시스템 버전 임시 테이블의 데이터 수정

아티클
03/24/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance

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

데이터 삽입

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

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

새 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 column list and DEFAULT values for period columns
  INSERT INTO [dbo].[Department]
  VALUES(12, 'Production', 101, 1, default, default);

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

PERIOD 열이 HIDDEN으로 지정되면 열 목록을 지정하지 않고 INSERT를 사용하는 경우에, 표시되는 열의 값만 지정하면 됩니다. 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 IN 문에 사용되는 준비 테이블에는 SYSTEM_TIME PERIOD가 정의되어 있어야 하지만 시스템 버전의 임시 테이블일 필요는 없습니다. 이렇게 하면 준비 테이블로 데이터를 삽입하거나 미리 채워진 준비 테이블에 SYSTEM_TIME 기간이 추가되는 동안 임시 일관성 검사가 수행되도록 합니다.

/*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 문으로 현재 테이블의 데이터를 업데이트합니다. "oops" 시나리오의 기록 테이블에서 현재 테이블의 데이터를 업데이트할 수 있습니다. 그러나 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-04-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);