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

  • 아티클

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

temporal 테이블 데이터의 최신(현재) 상태를 가져오려면, 비temporal 테이블 쿼리와 동일한 방식으로 쿼리할 수 있습니다. PERIOD 열이 숨겨져 있지 않은 경우 해당 값은 SELECT * 쿼리에 나타납니다. PERIOD 열을 HIDDEN으로 지정하면 해당 값은 SELECT * 쿼리에 나타나지 않습니다. PERIOD 열이 숨겨진 경우 해당 열에 대한 값을 반환하기 위해 특히 SELECT 절의 PERIOD 열을 참조해야 합니다.

모든 유형의 시간 기반 분석을 수행하려면, 4개의 임시 하위 절과 함께 새로운 FOR SYSTEM_TIME 절을 사용하여 현재 및 기록 테이블에서 데이터를 쿼리합니다. 이러한 절에 대한 자세한 내용은 임시 테이블 및 FROM(Transact-SQL)을 참조하세요.

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

FOR SYSTEM_TIME은 쿼리의 각 테이블에 독립적으로 지정될 수 있습니다. 공용 테이블 식, 테이블 반환 함수, 저장 프로시저 내에 사용될 수 있습니다. temporal 테이블에서 테이블 별칭을 사용하는 경우 FOR SYSTEM_TIME 절이 temporal 테이블 이름과 별칭 사이에 포함되어야 합니다(AS OF 하위 절을 사용하여 특정 시간 쿼리 두 번째 예제 참조).

AS OF 하위 절을 사용한 특정 시간의 쿼리

과거의 특정 시간으로 데이터의 상태를 다시 구성해야 하는 경우 AS OF 하위 절을 사용합니다. PERIOD 열 정의에 지정된 datetime2 형식의 자릿수로 데이터를 다시 구성할 수 있습니다.

시간 조건을 동적으로 지정할 수 있도록 AS OF 하위 절을 상수 리터럴이나 변수와 함께 사용할 수 있습니다. 제공된 값은 UTC 시간으로 해석됩니다.

첫 번째 예는 dbo.Department 테이블의 상태를 과거의 특정 날짜로(AS OF) 반환합니다.

/*State of entire table AS OF specific date in the past*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

이 두 번째 예제에서는 행 하위 집합의 두 지점 사이의 값을 시간 단위로 비교합니다.

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(day, -1, sysutcdatetime());

/*Comparison between two points in time for subset of rows*/
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

임시 쿼리의 AS OF 하위 절에서 뷰 사용

복잡한 지정 시간 분석이 필요한 경우 뷰를 사용하면 시나리오에 유용합니다. 일반적인 예제는 이전 달의 값을 사용하여 오늘 비즈니스 보고서를 생성하는 것입니다.

일반적으로 고객은 외래 키 관계와 다수의 테이블을 포함하는 정규화된 데이터 모델을 갖습니다. 모든 테이블이 각각의 방식으로 독립적으로 변하기 때문에, 정규화된 모델의 데이터가 과거에 어떻게 보였는지 확인하는 것이 어려울 수 있습니다.

이런 경우, 가장 좋은 방법은 뷰를 만들고 전체 뷰에 AS OF 하위 절을 적용하는 것입니다. SQL Server에서 AS OF 절을 뷰 정의에 참여하는 모든 temporal 테이블에 투명하게 적용하므로 이러한 방식을 사용하면 특정 시점 분석에서 데이터 액세스 계층의 모델링을 분리할 수 있습니다. 또한 같은 뷰에서 temporal 테이블을 비 temporal 테이블과 결합할 수 있으며 AS OF는 temporal 테이블에만 적용됩니다. 뷰에서 temporal 테이블을 최소 하나 이상 참조하지 않는 경우 임시 쿼리 절을 적용하면 오류가 발생하면서 실패합니다.

다음 샘플 코드에서는 temporal 테이블 3개(Department, CompanyLocation, LocationDepartments)를 참가하는 뷰를 만듭니다.

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

이제 AS OF 하위 절과 datetime2 리터럴을 사용하여 뷰를 쿼리할 수 있습니다.

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF'2021-09-01 T10:00:00.7230011';

또는 로컬 표준 시간대 및 AT TIME ZONE과 함께 AS OF 하위 절을 사용하여 뷰를 쿼리할 수 있습니다.

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

시간에 따른 특정 행에 대한 변경 내용 쿼리

임시 하위 절 FROM ... TO, BETWEEN ... AND 및 CONTAINED IN은(는) 현재 테이블의 특정 행에 대한 모든 변경 내용이 필요한 경우에 유용합니다(데이터 감사라고도 함).

처음 두 개의 하위 절은 지정된 기간과 겹치는(즉, 지정된 기간 전에 시작되고 그 후에 종료되는) 행 버전을 반환하는 반면, CONTAINED IN은 지정된 기간 범위 내에 존재하는 것들만 반환합니다.

현재가 아닌 행 버전만 검색하는 경우 최고의 쿼리 성능을 위해 기록 테이블을 직접 쿼리해야 합니다. 아무런 제한 없이 현재 데이터와 기록 데이터를 쿼리해야 하는 경우 ALL을(를) 사용합니다.

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

다음 단계