변경 내용 추적 함수를 사용하여 변경 내용 가져오기

변경 내용 추적 함수 정보

응용 프로그램은 다음 함수를 사용하여 데이터베이스에 적용된 변경 내용과 해당 변경 내용에 대한 정보를 가져올 수 있습니다.

• CHANGETABLE(CHANGES …) 함수
  이 행 집합 함수는 변경 내용 정보를 쿼리하는 데 사용됩니다. 이 함수는 내부 변경 내용 추적 테이블에 저장된 데이터를 쿼리합니다. 또한 이 함수는 작업, 업데이트된 열 및 행의 버전과 같은 다른 변경 내용 정보와 함께, 변경된 행의 기본 키를 포함하는 결과 집합을 반환합니다.

  CHANGETABLE(CHANGES …)는 마지막 동기화 버전을 인수로 사용합니다. 마지막 동기화 버전은 이 항목의 예제에 나와 있는 것처럼 @last_synchronization_version 변수를 사용하여 가져옵니다. 마지막 동기화 버전의 의미 체계는 다음과 같습니다.

  • 호출 클라이언트가 변경 내용을 가져왔으며 마지막 동기화 버전까지의 모든 변경 내용에 대해 알고 있습니다.

  • 따라서 CHANGETABLE(CHANGES …)가 마지막 동기화 버전 이후 발생한 모든 변경 내용을 반환합니다.

    다음 그림에서는 CHANGETABLE(CHANGES …)를 사용하여 변경 내용을 가져오는 방법을 보여 줍니다.

    

• CHANGE_TRACKING_CURRENT_VERSION() 함수
  다음에 변경 내용을 쿼리할 때 사용할 현재 버전을 가져오는 데 사용됩니다. 이 버전은 마지막으로 커밋된 트랜잭션의 버전을 나타냅니다.

• CHANGE_TRACKING_MIN_VALID_VERSION() 함수
  클라이언트에 포함할 수 있는 올바른 최소 버전을 가져오고 CHANGETABLE()에서 올바른 결과를 가져오는 데 계속 사용됩니다. 클라이언트는 이 함수에서 반환된 값에 대해 마지막 동기화 버전을 검사해야 합니다. 마지막 동기화 버전이 이 함수에서 반환된 버전보다 작은 경우 클라이언트는 CHANGETABLE()에서 올바른 결과를 가져올 수 없으며 다시 초기화되어야 합니다.

초기 데이터 가져오기

응용 프로그램에서 처음으로 변경 내용을 가져오려면 먼저 초기 데이터 및 동기화 버전을 가져오는 쿼리를 보내야 합니다. 응용 프로그램은 테이블에서 적합한 데이터를 직접 가져온 다음 CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 초기 버전을 가져와야 합니다. 처음 변경 내용을 가져오면 이 버전이 CHANGETABLE(CHANGES …)에 전달됩니다.

다음 예에서는 초기 동기화 버전 및 초기 데이터 집합을 가져오는 방법을 보여 줍니다.

    -- Obtain the current synchronization version. This will be used next time that changes are obtained.
    SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

    -- Obtain initial data set.
    SELECT
        P.ProductID, P.Name, P.ListPrice
    FROM
        SalesLT.Product AS P

변경 내용 추적 함수를 사용하여 변경 내용 가져오기

테이블에 대해 변경된 행과 해당 변경 내용에 대한 정보를 가져오려면 CHANGETABLE(CHANGES…)를 사용합니다. 예를 들어 다음 쿼리에서는 SalesLT.Product 테이블에 대한 변경 내용을 가져옵니다.

SELECT
    CT.ProductID, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT

일반적으로 클라이언트는 행에 대한 기본 키만 가져오는 대신 해당 행에 대한 최신 데이터를 가져오려고 합니다. 따라서 응용 프로그램은 CHANGETABLE(CHANGES …)의 결과를 사용자 테이블의 데이터와 조인합니다. 예를 들어 다음 쿼리에서는 SalesLT.Product 테이블과 조인하여 Name 및 ListPrice 열에 대한 값을 가져옵니다. 여기에서는 OUTER JOIN이 사용되었습니다. 이는 사용자 테이블에서 삭제된 행에 대해 변경 내용 정보가 반환되도록 하는 데 필요합니다.

SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

다음 변경 내용 열거에서 사용할 버전을 가져오려면 아래 예와 같이 CHANGE_TRACKING_CURRENT_VERSION()을 사용합니다.

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()

변경 내용을 가져올 때 응용 프로그램은 다음 예와 같이 CHANGETABLE(CHANGES…)와 CHANGE_TRACKING_CURRENT_VERSION()을 모두 사용해야 합니다.

-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

버전 번호

변경 내용 추적을 사용하도록 설정된 데이터베이스에는 버전 카운터가 있습니다. 이 카운터는 변경 내용 추적이 설정된 테이블에 변경 내용이 적용될 때마다 증가합니다. 변경된 각 행에는 이와 연관된 버전 번호가 있습니다. 변경 내용을 쿼리하기 위해 요청이 응용 프로그램에 전송되면 버전 번호를 제공하는 함수가 호출됩니다. 이 함수는 해당 버전 이후 적용된 모든 변경 내용에 대한 정보를 반환합니다. 여러 가지 측면에서 변경 내용 추적 버전은 개념상 rowversion 데이터 형식과 유사합니다.

마지막으로 동기화된 버전의 유효성 검사

변경 내용에 대한 정보는 제한된 시간 동안 유지 관리됩니다. 시간은 ALTER DATABASE의 일부로 지정할 수 있는 CHANGE_RETENTION 매개 변수로 제어됩니다.

CHANGE_RETENTION에 대해 지정하는 시간에 따라 모든 응용 프로그램이 데이터베이스에서 변경 내용을 요청해야 하는 빈도가 결정됩니다. 응용 프로그램의 last_synchronization_version 값이 테이블에 대해 올바른 최소 동기화 버전보다 작으면 해당 응용 프로그램은 올바른 변경 내용 열거를 수행할 수 없습니다. 이는 일부 변경 내용 정보가 정리되었을 수 있기 때문입니다. 응용 프로그램이 CHANGETABLE(CHANGES …)를 사용하여 변경 내용을 가져오려면 먼저 CHANGETABLE(CHANGES …)에 전달하려고 하는 last_synchronization_version에 대한 값의 유효성을 검사해야 합니다. last_synchronization_version의 값이 잘못된 경우 해당 응용 프로그램은 모든 데이터를 다시 초기화해야 합니다.

다음 예에서는 각 테이블에 대한 last_synchronization_version 값의 유효성을 확인하는 방법을 보여 줍니다.

    -- Check individual table.
    IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(
                                       OBJECT_ID('SalesLT.Product')))
    BEGIN
      -- Handle invalid version and do not enumerate changes.
      -- Client must be reinitialized.
    END

다음 예와 같이 데이터베이스의 모든 테이블에 대해 last_synchronization_version 값의 유효성을 검사할 수 있습니다.

    -- Check all tables with change tracking enabled
    IF EXISTS (
      SELECT * FROM sys.change_tracking_tables
      WHERE min_valid_version > @last_synchronization_version )
    BEGIN
      -- Handle invalid version & do not enumerate changes
      -- Client must be reinitialized
    END

열 추적 사용

응용 프로그램에서 열 추적을 사용하면 전체 행 대신 변경된 열에 대한 데이터만 가져올 수 있습니다. 예를 들어 테이블에 크지만 거의 변경되지 않는 열이 하나 이상 있고 자주 변경되는 다른 열도 있는 시나리오가 있을 경우 열 추적을 사용하지 않으면 응용 프로그램이 행이 변경되었다는 사실만 확인할 수 있으므로 큰 열 데이터를 포함하는 데이터를 모두 동기화해야 합니다. 그러나 열 추적을 사용하면 응용 프로그램이 큰 열 데이터가 변경되었는지 여부를 확인하여 변경된 데이터만 동기화할 수 있습니다.

열 추적 정보는 CHANGETABLE(CHANGES …) 함수에서 반환하는 SYS_CHANGE_COLUMNS 열에 나타납니다.

변경되지 않은 열에 대해 NULL이 반환되도록 열 추적을 사용할 수 있습니다. 열이 NULL로 변경될 수 있는 경우 해당 열이 변경되었는지 여부를 나타내기 위해 별도의 열을 반환해야 합니다.

다음 예에서는 CT_ThumbnailPhoto 열이 변경되지 않은 경우 해당 열이 NULL이 됩니다. 또한 이 열은 NULL로 변경되었으므로 NULL이 될 수 있습니다. 응용 프로그램은 CT_ThumbNailPhoto_Changed 열을 사용하여 해당 열이 변경되었는지 여부를 확인할 수 있습니다.

DECLARE @PhotoColumnId int = COLUMNPROPERTY(
    OBJECT_ID('SalesLT.Product'),'ThumbNailPhoto', 'ColumnId')

SELECT
    CT.ProductID, P.Name, P.ListPrice, -- Always obtain values.
    CASE
           WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) = 1
            THEN ThumbNailPhoto
            ELSE NULL
      END AS CT_ThumbNailPhoto,
      CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) AS
                                   CT_ThumbNailPhoto_Changed
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
     CT.SYS_CHANGE_CONTEXT
FROM
     SalesLT.Product AS P
INNER JOIN
     CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
     P.ProductID = CT.ProductID AND
     CT.SYS_CHANGE_OPERATION = 'U'

일관되고 올바른 결과 가져오기

테이블에 대해 변경된 데이터를 가져오려면 여러 단계를 수행해야 합니다. 특정 문제를 검토하여 처리하지 않으면 일관되지 않거나 잘못된 결과가 반환될 수 있습니다.

예를 들어 Sales 테이블과 SalesOrders 테이블에 적용된 변경 내용을 가져오려면 응용 프로그램에서 다음 단계를 수행해야 합니다.

1. CHANGE_TRACKING_MIN_VALID_VERSION()을 사용하여 마지막으로 동기화된 버전의 유효성을 검사합니다.

2. CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 변경 내용을 가져오는 데 사용할 수 있는 버전을 가져옵니다.

3. CHANGETABLE(CHANGES …)를 사용하여 Sales 테이블에 대한 변경 내용을 가져옵니다.

4. CHANGETABLE(CHANGES …)를 사용하여 SalesOrders 테이블에 대한 변경 내용을 가져옵니다.

이전 단계로 반환되는 결과에 영향을 줄 수 있는 다음 두 프로세스가 데이터베이스에서 발생합니다.

• 정리 프로세스가 백그라운드에서 실행되어 지정된 보존 기간보다 오래된 변경 내용 추적 정보가 제거됩니다.

  정리 프로세스는 데이터베이스에 대한 변경 내용 추적을 구성할 때 지정된 보존 기간을 사용하는 별도의 백그라운드 프로세스입니다. 문제는 마지막 동기화 버전의 유효성을 검사한 시점과 CHANGETABLE(CHANGES…)를 호출한 시점 사이에 정리 프로세스가 발생할 수 있다는 것입니다. 방금 유효했던 마지막 동기화 버전이 변경 내용을 가져온 시점에 더 이상 유효하지 않을 수 있습니다. 따라서 잘못된 결과가 반환될 수 있습니다.

• Sales 및 SalesOrders 테이블에 다음과 같이 진행 중인 DML 작업이 발생합니다.

  • CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 사용할 버전을 가져온 후 테이블에 변경 내용이 적용될 수 있습니다. 따라서 예상한 것보다 많은 변경 내용이 반환될 수 있습니다.

  • Sales 테이블에서 변경 내용을 가져오는 호출과 SalesOrders 테이블에서 변경 내용을 가져오는 호출 사이에 트랜잭션이 커밋될 수 있습니다. 따라서 SalesOrder 테이블에 대한 결과에 Sales 테이블에 없는 외래 키 값이 포함될 수 있습니다.

앞에서 나열된 문제점을 해결하려면 스냅숏 격리를 사용하는 것이 좋습니다. 스냅숏 격리를 사용하면 변경 내용 정보의 일관성을 유지하고 백그라운드 정리 태스크와 관련된 경합 상태를 방지할 수 있습니다. 스냅숏 트랜잭션을 사용하지 않으면 변경 내용 추적을 사용하는 응용 프로그램을 개발할 때 더 많은 노력이 필요할 수 있습니다.

스냅숏 격리 사용

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT2008
    SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  -- Verify that version of the previous synchronization is valid.
  -- Obtain the version to use next time.
  -- Obtain changes.
COMMIT TRAN

스냅숏 격리 사용의 대체 방법

참고 항목

태스크

개념

변경 내역