다음을 통해 공유

temporal 테이블

  • 아티클

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

데이터베이스 기능의 temporal 테이블(시스템 버전 temporal 테이블이라고도 함)은 현재 시점에 올바른 데이터에 대한 정보만 제공하는 것이 아니라 임의 시점에 테이블에 저장된 데이터에 대한 정보를 제공하는 것을 기본적으로 지원합니다.

‬시스템 버전 관리 temporal 테이블을 시작하고 temporal 테이블 사용 시나리오를 검토하세요.

‬시스템 버전 관리 temporal 테이블이란?

‬시스템 버전 관리 temporal 테이블은 데이터 변경 내용의 전체 기록을 유지하여 간편한 지정 시간 분석을 허용하도록 설계된 사용자 테이블의 종류입니다. 이 유형의 temporal 테이블은 각 행의 유효 기간을 시스템(즉, 데이터베이스 엔진)이 관리하기 때문에 ‬시스템 버전 관리 temporal 테이블이라고 합니다.

모든 temporal 테이블에는 명시적으로 정의된 두 개의 열이 있으며, 각 열의 데이터 형식은 datetime2 입니다. 이러한 열을 기간 열이라고 합니다. 이러한 기간 열은 행이 수정될 때마다 시스템에서 각 행의 유효 기간을 기록하는 데에만 사용됩니다. 현재 데이터를 저장하는 주 테이블을 현재 테이블 또는 temporal 테이블이라고 합니다.

이러한 기간 열 외에도 temporal 테이블은 미러된 스키마를 사용하는 다른 테이블에 대한 참조를 포함합니다(기록 테이블이라고 함). 시스템에서는 기록 테이블을 사용하여 temporal 테이블의 행이 업데이트되거나 삭제될 때마다 이전 버전의 행을 자동으로 저장합니다. temporal 테이블을 만드는 동안 사용자가 기존 기록 테이블(스키마를 준수해야 함)을 지정하거나 시스템에서 기본 기록 테이블을 만들도록 할 수 있습니다.

temporal을 사용하는 이유

실제 데이터 원본은 동적이며 비즈니스 의사 결정보다 분석가가 데이터 진화를 통해 얻을 수 있는 인사이트를 사용하는 경우가 많습니다. temporal 테이블에 대한 사용 사례는 다음을 포함합니다.

  • 모든 데이터 변경 내용을 감사하고 필요한 경우 데이터 범죄 분석 수행
  • 과거 시점의 데이터 상태 재구성
  • 시간에 따른 추세 계산
  • 의사 결정 지원 애플리케이션에 대해 느리게 변화하는 차원 유지 관리
  • 실수로 인한 데이터 변경 및 애플리케이션 오류 복구

temporal은 어떻게 작동하나요?

테이블에 대한 시스템 버전은 현재 테이블과 기록 테이블의 테이블 쌍으로 구현됩니다. 이러한 각 테이블 내에서 두 개의 추가 datetime2 열은 각 행의 유효 기간을 정의하는 데 사용됩니다.

  • 기간 시작 열: 시스템에서 이 열의 행에 대한 시작 시간을 기록합니다(일반적으로 ValidFrom 열로 표시됨).

  • 기간 종료 열: 시스템에서 이 열의 행에 대한 종료 시간을 기록합니다(일반적으로 ValidTo 열로 표시됨).

현재 테이블에는 각 행에 대한 현재 값이 포함됩니다. 기록 테이블에는 각 행에 대한 각각의 이전 값(이전 버전)과 유효 기간의 시작 시간 및 종료 시간이 포함됩니다.

Temporal 테이블의 작동 방식을 보여 주는 다이어그램

다음 스크립트는 직원 정보가 포함된 시나리오를 보여줍니다.

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

자세한 내용은 ‬시스템 버전 관리 temporal 테이블 만들기를 참조하세요.

  • 삽입: 시스템에서 시스템 클록을 기준으로 ValidFrom 열의 값을 현재 트랜잭션의 시작 시간(UTC 표준 시간대)으로 설정하고 ValidTo 열의 값을 최댓값 9999-12-31에 할당합니다. 그러면 행이 열린 것으로 표시됩니다.

  • 업데이트: 시스템에서 기록 테이블에 있는 행의 이전 값을 저장하고, 시스템 클록을 기준으로 ValidTo 열의 값을 현재 트랜잭션의 시작 시간(UTC 표준 시간대)으로 설정합니다. 행이 유효한 기간을 기록하여 행을 닫은 것으로 표시합니다. 현재 테이블에서 행은 새 값으로 업데이트되고 시스템은 ValidFrom 열 값을 시스템 클록을 기준으로 트랜잭션의 시작 시간(UTC 표준 시간대)으로 설정합니다. ValidTo 열의 현재 테이블에서 업데이트된 행의 값은 최댓값 9999-12-31로 유지됩니다.

  • 삭제: 시스템은 기록 테이블에 있는 행의 이전 값을 저장하고 시스템 클록을 기준으로 ValidTo 열의 값을 현재 트랜잭션의 시작 시간(UTC 표준 시간대)으로 설정합니다. 이전 행이 유효한 기간을 기록하여 행을 닫은 것으로 표시합니다. 현재 테이블에서 행이 제거됩니다. 현재 테이블의 쿼리는 이 행을 반환하지 않습니다. 기록 데이터를 처리하는 쿼리만 행이 닫힌 데이터를 반환합니다.

  • 병합: 작업이 MERGE 문에 동작으로 지정된 항목에 따라 정확히 최대 세 개의 문(INSERT, UPDATE 및/또는 DELETE)이 실행된 것처럼 동작합니다.

시스템 datetime2 열에 기록되는 시작 시간은 트랜잭션 자체의 시간을 기반으로 합니다. 예를 들어 단일 트랜잭션 내에 삽입된 모든 행은 SYSTEM_TIME 기간의 시작에 해당하는 열에 기록된 UTC 시간이 동일합니다.

temporal 테이블에서 데이터 수정 쿼리를 실행하면 열 값이 변경되지 않더라도 데이터베이스 엔진에서 기록 테이블에 행을 추가합니다.

temporal 데이터를 어떻게 쿼리하나요?

SELECT ... FROM <table> 문에는 현재 및 기록 테이블 전체에서 데이터를 쿼리하기 위한 5개의 temporal 하위 절이 포함된 새로운 FOR SYSTEM_TIME 절이 있습니다. 이 새 SELECT 문 구문은 단일 테이블에서 직접 지원되며 여러 조인을 통해 전파되고 여러 temporal 테이블 위에 있는 보기를 통해 전파됩니다.

5개의 하위 절 중 하나를 사용하여 FOR SYSTEM_TIME 절을 통해 쿼리하는 경우 다음 이미지와 같이 temporal 테이블의 기록 데이터가 포함됩니다.

temporal 쿼리의 작동 방식을 보여 주는 다이어그램

다음 쿼리는 2021년 1월 1일부터 2022년 1월 1일(포함) 사이에 활성 상태로 있었고 필터 조건이 WHERE EmployeeID = 1000인 직원에 대한 행 버전을 검색합니다.

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME은 유효 기간(ValidFrom = ValidTo)이 0인 행을 필터링합니다.

이러한 행은 동일한 트랜잭션 내의 동일한 기본 키에서 여러 업데이트를 수행하는 경우에 생성됩니다. 이 경우 temporal 쿼리는 트랜잭션 이전의 행 버전과 트랜잭션 이후 현재 행만 반환합니다.

분석에 해당 행을 포함해야 하는 경우 기록 테이블을 직접 쿼리합니다.

다음 표에서 행 한정의 ValidFrom 열은 쿼리 중인 테이블의 ValidFrom 열 값을 나타내고 ValidTo는 쿼리 중인 테이블의 ValidTo 열 값을 나타냅니다. 전체 구문과 예제는 FROM 절 플러스 JOIN, APPLY, PIVOT 및 ‬시스템 버전 관리 temporal 테이블의 데이터 쿼리를 참조하세요.

식 한정 행 참고 항목
AS OF date_time ValidFrom <= date_time AND ValidTo > date_time 과거의 지정된 시간에 현재였던 값이 포함된 행이 있는 테이블을 반환합니다. 내부적으로 temporal 테이블과 해당 기록 테이블 간에 공용 구조체가 수행됩니다. 결과는 date_time 매개 변수로 지정된 시점에 유효했던 행의 값을 반환하도록 필터링됩니다. system_start_time_column_name 값이 date_time 매개 변수 값보다 작거나 같고, system_end_time_column_name 값이 date_time 매개 변수 값보다 큰 경우 행에 대한 값이 유효한 것으로 간주됩니다.
FROM start_date_time TO end_date_time ValidFrom < end_date_time AND ValidTo > start_date_time FROM 인수에 대한 start_date_time 매개 변수 값 이전에 활성 상태가 시작되었든 아니면 TO 인수에 대한 end_date_time 매개 변수 값 이후에 활성 상태가 중단되었든 상관없이 지정된 시간 범위 내에 활성 상태였던 모든 행 버전의 값을 포함하는 테이블을 반환합니다. 내부적으로 temporal 테이블과 해당 기록 테이블 간에 공용 구조체가 수행됩니다. 지정된 시간 범위 중 임의의 시점에 활성 상태였던 모든 행 버전의 값을 반환하도록 결과가 필터링됩니다. FROM 엔드포인트로 정의된 하위 경계에서 정확히 활동이 중지된 행은 포함되지 않고 TO 엔드포인트로 정의된 상위 경계에서 정확히 활성화된 레코드도 포함되지 않습니다.
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time end_date_time로 정의된 상위 경계에서 활성화된 행이 반환되는 행 테이블에 포함된다는 점을 제외하고는 위의 FOR SYSTEM_TIME FROM start_date_time TO end_date_time 설명과 같습니다.
CONTAINED IN(start_date_time, end_date_time) ValidFrom >= start_date_time AND ValidTo <= end_date_time CONTAINED IN 인수에 대한 두 개의 기간 값으로 정의된 지정된 시간 범위 내에 열리고 닫힌 모든 행 버전의 값을 포함하는 테이블을 반환합니다. 정확히 하위 경계에서 활성화되거나 상위 경계에서 활성 상태가 중단된 행이 포함됩니다.
ALL 모든 행 현재 및 기록 테이블에 속하는 행의 공용 구조체를 반환합니다.

마침표 열 숨기기

마침표 열을 명시적으로 참조하지 않는 쿼리가 이러한 열(예: SELECT * FROM <table> 실행 중)을 반환하지 않도록 마침표 열을 숨기도록 선택할 수 있습니다.

숨겨진 열을 반환하려면 쿼리에서 숨겨진 열을 명시적으로 참조해야 합니다. 마찬가지로 INSERT 및 BULK INSERT 문은 이러한 새 기간 열이 존재하지 않은 것처럼 계속되며 열 값이 자동으로 채워집니다.

HIDDEN 절 사용에 대한 자세한 내용은 CREATE TABLE 및 ALTER TABLE을 참조하세요.

샘플

관련 콘텐츠