시스템 버전 관리 temporal 테이블 만들기

아티클
03/22/2024

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

기록 테이블을 지정하는 방법을 고려하면 시스템 버전 temporal 테이블을 만드는 다음 세 가지 방법이 있습니다.

익명 기록 테이블이 포함된 임시 테이블: 현재 테이블의 스키마를 지정하고 시스템이 자동 생성된 이름으로 해당 기록 테이블을 만들도록 합니다.
기본 기록 테이블이 있는 temporal 테이블: 기록 테이블 스키마 이름과 테이블 이름을 지정하고 시스템에서 해당 스키마에 기록 테이블을 만들 수 있도록 합니다.
미리 만든 사용자 정의 기록 테이블이 포함된 temporal 테이블: 요구에 가장 알맞은 기록 테이블을 만든 다음 temporal 테이블을 만드는 동안 해당 테이블을 참조합니다.

익명 기록 테이블이 포함된 temporal 테이블 만들기

"익명" 기록 테이블을 사용하여 temporal 테이블 생성은 특히 프로토타입 및 테스트 환경에서 빠르게 개체를 만들 수 있는 편리한 옵션입니다. 또한 SYSTEM_VERSIONING 절에 매개 변수가 필요하지 않으므로 temporal 테이블을 만드는 가장 간단한 방법이기도 합니다. 다음 예제에서는 기록 테이블 이름을 정의하지 않고 시스템 버전 관리를 사용하여 새 테이블을 만듭니다.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON);

설명

시스템 버전 temporal 테이블에는 기본 키가 정의되어 있어야 하며 GENERATED ALWAYS AS ROW START 또는 GENERATED ALWAYS AS ROW END로 선언된 datetime2 열 2개로 PERIOD FOR SYSTEM_TIME 하나를 정확하게 정의해야 합니다.
PERIOD 열은 null 허용 여부를 지정하지 않은 경우에도 항상 null을 허용하지 않는다고 가정합니다. PERIOD 열을 명시적으로 null 허용으로 정의하면 CREATE TABLE 문이 실패합니다.
기록 테이블은 항상 열 수, 열 이름, 순서 및 데이터 형식과 관련해서 현재 또는 temporal 테이블과 스키마를 맞춰야 합니다.
익명 기록 테이블은 현재 또는 temporal 테이블과 동일한 스키마에 자동으로 생성됩니다.
익명 기록 테이블 이름의 형식은 MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]입니다. 접미사는 선택 사항이며 테이블 이름의 첫 번째 부분이 고유하지 않은 경우에만 추가됩니다.
기록 테이블은 rowstore 테이블로 생성됩니다. 가능한 경우 PAGE 압축이 적용되고 그렇지 않으면 기록 테이블이 압축 해제됩니다. 예를 들어 스파스 열과 같은 일부 테이블 구성은 압축을 허용하지 않습니다.
기본 클러스터형 인덱스는 형식 IX_<history_table_name>의 자동 생성된 이름을 가진 기록 테이블에 대해 생성됩니다. 클러스터형 인덱스에는 PERIOD 열(끝, 시작)이 포함됩니다.
현재 테이블을 메모리 최적화 테이블로 만들려면 메모리 최적화 테이블이 있는 시스템 버전 temporal 테이블을 참조하세요.

기본 기록 테이블이 포함된 temporal 테이블 만들기

이름 지정은 제어하면서 시스템이 기본 구성을 사용하여 기록 테이블을 만들도록 하려는 경우 기본 기록 테이블이 포함된 temporal 테이블을 만들면 편리합니다. 다음 예제에서는 기록 테이블 이름이 정확하게 정의된 시스템 버전 관리를 사용하여 새 테이블을 만듭니다.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

설명

기록 테이블은 명명된 기록 테이블에 명시적으로 적용되는 다음 규칙을 사용하여 "익명" 기록 테이블 만들기에 적용되는 것과 같은 규칙을 사용하여 생성됩니다.

스키마 이름은 HISTORY_TABLE 매개 변수에 필수입니다.
지정된 스키마가 없으면 CREATE TABLE 문이 실패합니다.
HISTORY_TABLE 매개 변수에서 지정한 테이블이 이미 있는 경우 스키마 일관성 및 임시 데이터 일관성 측면에서 새로 만든 temporal 테이블에 대해 확인됩니다. 잘못된 기록 테이블을 지정하면 CREATE TABLE 문이 실패합니다.

사용자 정의 기록 테이블이 포함된 temporal 테이블 만들기

사용자 정의 기록 테이블이 포함된 temporal 테이블 만들기는 특정 스토리지 옵션 및 기록 쿼리로 조정된 다른 인덱스를 가진 기록 테이블을 지정하려는 경우에 편리한 옵션입니다. 다음 예제에서는 생성된 temporal 테이블과 정렬된 스키마가 있는 사용자 정의 기록 테이블을 만듭니다. 이 사용자 정의 기록 테이블에 대해 포인트 조회를 위해 클러스터형 columnstore 인덱스 및 추가 비클러스터형 rowstore(B+ 트리) 인덱스를 만듭니다. 이 사용자 정의 기록 테이블이 생성되면 사용자 정의 기록 테이블을 기본 기록 테이블로 지정하는 시스템 버전 임시 테이블이 생성됩니다.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns ON DepartmentHistory (
    ValidTo,
    ValidFrom,
    DeptID
    );
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

설명

집계 또는 기간 지정 함수를 채택하는 기록 데이터에서 분석 쿼리를 실행할 경우 데이터 압축 및 쿼리 성능을 위해 클러스터형 columnstore를 기본 인덱스로 만드는 것이 좋습니다.
기본 사용 사례가 데이터 감사(즉, 현재 테이블에서 단일 행에 대한 기록 변경 내용 검색)인 경우 클러스터형 인덱스를 가진 rowstore 기록 테이블을 만드는 것이 좋습니다.
기록 테이블은 기본 키, 외래 키, 고유 인덱스, 테이블 제약 조건 또는 트리거를 가질 수 없습니다. 또한 변경 데이터 캡처, 변경 내용 추적, 트랜잭션 또는 병합 복제에 대해 구성할 수 없습니다.

비 temporal 테이블을 시스템 버전 temporal 테이블로 변경

사용자 지정 임시 솔루션을 기본 제공 지원으로 마이그레이션하려는 경우와 같이 기존 비 temporal 테이블에서 시스템 버전 관리를 사용할 수 있습니다. 예를 들어 트리거를 사용하여 버전 관리가 구현되는 테이블 집합이 있을 수 있습니다. 임시 시스템 버전 관리 사용은 덜 복잡하며 다음과 같은 다른 이점을 제공합니다.

변경 불가능한 기록
시간 이동 쿼리를 위한 새로운 구문
DML 성능 개선
최소 유지 관리 비용

기존 테이블을 변환하는 경우 열 이름을 명시적으로 지정하지 않아(예: 열 목록이 없는 SELECT * 또는 INSERT) 새 열을 처리하도록 설계되지 않은 기존 애플리케이션에 대한 영향이 방지되도록 HIDDEN 절을 사용하여 새 PERIOD 열(datetime2 열 ValidFrom 및 ValidTo)을 숨기는 것이 좋습니다.

비temporal 테이블에 버전 관리 추가

데이터가 포함된 비 temporal 테이블에 대한 변경 내용 추적을 시작하려면 PERIOD 정의를 추가하고 필요한 경우 SQL Server에서 자동으로 만드는 빈 기록 테이블의 이름을 제공해야 합니다.

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy
    ADD
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Important

DATETIME2의 정도는 기본 테이블의 정도와 일치해야 합니다. 다음 설명을 참조하세요.

설명

SQL Server Enterprise Edition 이외의 모든 버전에서 기본값이 있는 null이 허용되지 않는 열을 데이터가 포함된 기존 테이블에 추가하는 경우 데이터 작업(메타데이터 작업) 크기에 주의해야 합니다. SQL Server Standard 버전에 데이터가 있는 대규모 기존 기록 테이블을 사용하면 null이 아닌 열을 추가하는 데 많은 비용이 들 수 있습니다.
기간 시작 및 기간 종료 열에 대한 제약 조건을 신중하게 선택해야 합니다.
- 시작 열의 기본값은 기존 행이 유효하다고 간주되는 시점을 지정합니다. 미래의 datetime 포인트로 지정할 수 없습니다.
- 종료 시간은 지정된 datetime2 정밀도의 최댓값으로 지정해야 합니다(예: 9999-12-31 23:59:59 또는 9999-12-31 23:59:59.9999999).
PERIOD를 추가하면 기간 열의 기존 값이 유효한지 확인하기 위해 현재 테이블에서 데이터 일관성 확인이 수행됩니다.
SYSTEM_VERSIONING을 사용하도록 설정할 때 기존 기록 테이블을 지정하면 현재 테이블과 기록 테이블 모두에서 데이터 일관성 확인이 수행됩니다. DATA_CONSISTENCY_CHECK = OFF를 추가 매개 변수로 지정하면 이 작업을 건너뛸 수 있습니다.

기존 테이블을 기본 제공 지원으로 마이그레이션

이 예제에서는 기본 제공 임시 지원에 대한 트리거를 기반으로 기존 솔루션에서 마이그레이션하는 방법을 보여 줍니다. 이 예제에서는 현재 사용자 지정 솔루션이 현재 및 기록 데이터를 개별 사용자 테이블 2개(ProjectTaskCurrent 및 ProjectTaskHistory)로 분할한다고 가정합니다.

기존 솔루션이 단일 테이블을 사용하여 실제 및 기록 행을 저장하는 경우 다음 예제와 같은 마이그레이션 단계 전에 데이터를 두 테이블로 분할해야 합니다. 먼저 이후 temporal 테이블에서 트리거를 삭제합니다. 그런 다음, PERIOD 열이 null을 허용하지 않는지 확인합니다.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

설명

PERIOD 정의에 기존 열을 참조하면 암시적으로 generated_always_type을 해당 열의 AS_ROW_START 및 AS_ROW_END로 변경합니다.
PERIOD를 추가하면 기간 열의 기존 값이 유효한지 확인하기 위해 현재 테이블에서 데이터 일관성 확인이 수행됩니다.
기존 데이터에서 데이터 일관성 확인을 시행하도록 DATA_CONSISTENCY_CHECK = ON을 사용하여 SYSTEM_VERSIONING을 설정하는 것이 좋습니다.
숨겨진 열이 선호되는 경우에는 ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN; 명령을 사용합니다.