시스템 버전 관리 시간 기반 테이블의 데이터 수정
적용 대상:
SQL Server 2016 (13.x) 이상 버전 Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric의 SQL 데이터베이스
시스템 버전이 있는 시간 기반 테이블의 데이터는 일반적인 데이터 조작 언어(DML) 문장을 사용하여 수정할 수 있지만, 한 가지 중요한 차이점이 있습니다. 기간 열의 데이터는 직접 수정할 수 없습니다. 데이터가 업데이트되면 버전이 지정되고 업데이트된 각 행의 이전 버전이 기록 테이블에 삽입됩니다. 데이터가 삭제되면 삭제는 논리적이며 행이 현재 테이블의 기록 테이블로 이동되며 데이터는 영구적으로 삭제되지 않습니다.
데이터 삽입
새 데이터를 삽입할 때는 PERIOD이 아닌 경우 HIDDEN 열을 고려해야 합니다. temporal 테이블과 함께 파티션 전환을 사용할 수도 있습니다.
표시되는 기간 열을 사용하여 새 데이터 삽입
다음과 같이 INSERT 열이 표시되어 있는 경우 PERIOD 열을 설명하기 위해 PERIOD 문을 구성할 수 있습니다.
INSERT 문에서 열 목록을 지정하는 경우 시스템에서 이러한 열에 대한 값을 자동으로 생성하므로 PERIOD 열을 생략할 수 있습니다.
-- Insert with column list and without period columns
INSERT INTO [dbo].[Department] (
[DeptID],
[DeptName],
[ManagerID],
[ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);
PERIOD 문에서 열 목록에 INSERT 열을 지정한 경우, 반드시 해당 값으로 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로 설정하고 현재 및 기록 테이블의 행을 업데이트하는 경우 시스템은 변경 기록을 유지하지 않습니다.
현재 테이블 업데이트
이 예제에서는 ManagerID가 DeptID인 각 행에 대해 10 열이 업데이트됩니다.
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 작업은 INSERT 열과 관련하여 UPDATE 및 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);