아티클
04/01/2012

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

이 항목에서는 SQL Server 2008의 변경 내용 추적 함수와 이러한 함수를 사용하여 데이터베이스에 적용된 변경 내용과 해당 변경 내용에 대한 정보를 가져오는 방법에 대해 설명합니다.

변경 내용 추적 함수 정보

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

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 테이블에 적용된 변경 내용을 가져오려면 응용 프로그램에서 다음 단계를 수행해야 합니다.

CHANGE_TRACKING_MIN_VALID_VERSION()을 사용하여 마지막으로 동기화된 버전의 유효성을 검사합니다.
CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 변경 내용을 가져오는 데 사용할 수 있는 버전을 가져옵니다.
CHANGETABLE(CHANGES …)를 사용하여 Sales 테이블에 대한 변경 내용을 가져옵니다.
CHANGETABLE(CHANGES …)를 사용하여 SalesOrders 테이블에 대한 변경 내용을 가져옵니다.

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

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

정리 프로세스는 데이터베이스에 대한 변경 내용 추적을 구성할 때 지정된 보존 기간을 사용하는 별도의 백그라운드 프로세스입니다. 문제는 마지막 동기화 버전의 유효성을 검사한 시점과 CHANGETABLE(CHANGES…)를 호출한 시점 사이에 정리 프로세스가 발생할 수 있다는 것입니다. 방금 유효했던 마지막 동기화 버전이 변경 내용을 가져온 시점에 더 이상 유효하지 않을 수 있습니다. 따라서 잘못된 결과가 반환될 수 있습니다.
Sales 및 SalesOrders 테이블에 다음과 같이 진행 중인 DML 작업이 발생합니다.
- CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 사용할 버전을 가져온 후 테이블에 변경 내용이 적용될 수 있습니다. 따라서 예상한 것보다 많은 변경 내용이 반환될 수 있습니다.
- Sales 테이블에서 변경 내용을 가져오는 호출과 SalesOrders 테이블에서 변경 내용을 가져오는 호출 사이에 트랜잭션이 커밋될 수 있습니다. 따라서 SalesOrder 테이블에 대한 결과에 Sales 테이블에 없는 외래 키 값이 포함될 수 있습니다.

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

스냅숏 격리 사용

변경 내용 추적은 스냅숏 격리와 원활하게 작동하도록 디자인되었습니다. 데이터베이스에 스냅숏 격리를 사용하도록 설정해야 합니다. 또한 변경 내용을 가져오는 데 필요한 모든 단계를 스냅숏 트랜잭션 내에 포함해야 합니다. 이렇게 하면 변경 내용을 가져오는 동안 데이터에 적용된 모든 변경 내용이 스냅숏 트랜잭션 내의 쿼리에 표시되지 않습니다.

스냅숏 트랜잭션 내의 데이터를 가져오려면 다음 단계를 수행합니다.

트랜잭션 격리 수준을 스냅숏으로 설정하고 트랜잭션을 시작합니다.
CHANGE_TRACKING_MIN_VALID_VERSION()을 사용하여 마지막 동기화 버전의 유효성을 검사합니다.
CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 사용할 버전을 가져옵니다.
CHANGETABLE(CHANGES …)를 사용하여 Sales 테이블에 대한 변경 내용을 가져옵니다.
CHANGETABLE(CHANGES …)를 사용하여 Salesorders 테이블에 대한 변경 내용을 가져옵니다.
트랜잭션을 커밋합니다.

변경 내용을 가져오는 모든 단계가 스냅숏 트랜잭션 내에 있으므로 다음 사항에 유의해야 합니다.

마지막 동기화 버전의 유효성을 검사한 후 정리가 발생하면 정리에 의해 수행된 삭제 작업이 트랜잭션 내에 표시되지 않아 CHANGETABLE(CHANGES …)의 결과가 계속 유효합니다.
다음 동기화 버전을 가져온 후에 Sales 테이블 또는 SalesOrders 테이블에 적용된 변경 내용은 표시되지 않으며 CHANGETABLE(CHANGES …)에 대한 호출은 CHANGE_TRACKING_CURRENT_VERSION()으로 반환된 버전보다 최신인 버전의 변경 내용을 반환하지 않습니다. CHANGETABLE(CHANGES …) 호출 사이에 커밋된 트랜잭션이 표시되지 않으므로 Sales 테이블과 SalesOrders 테이블 사이의 일관성도 유지됩니다.

다음 예에서는 데이터베이스에 스냅숏 격리를 사용하도록 설정하는 방법을 보여 줍니다.

-- 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

스냅숏 트랜잭션에 대한 자세한 내용은 행 버전 관리 기반 격리 수준 사용을 참조하십시오.

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

스냅숏 격리 사용의 대체 방법이 있지만 이 경우 모든 응용 프로그램 요구 사항이 충족되려면 더 많은 작업이 필요합니다. last_synchronization_version의 유효성을 유지하며 변경 내용을 가져오기 전에 정리 프로세스로 인해 데이터가 제거되지 않도록 하려면 다음을 수행합니다.

CHANGETABLE()을 호출한 후 last_synchronization_version을 검사합니다.
CHANGETABLE()을 사용하여 변경 내용을 가져오는 각 쿼리의 일부로 last_synchronization_version을 검사합니다.

다음 열거에 대한 동기화 버전을 가져온 후 변경 내용이 발생할 수 있습니다. 이러한 상황은 다음 두 가지 방식으로 처리할 수 있습니다. 사용되는 옵션은 응용 프로그램과 해당 응용 프로그램이 각 접근 방식의 부작용을 처리할 수 있는 방식에 따라 달라집니다.

새 동기화 버전보다 큰 버전을 포함하는 변경 내용을 무시합니다.

이 접근 방식에서는 새 동기화 버전 이전에 만들어지거나 업데이트된 새 행 또는 업데이트된 행의 경우 건너뛴 다음 나중에 업데이트되는 부작용이 있습니다. 새 행의 경우 만들어진 다른 테이블에 건너뛴 행을 참조하는 행이 있으면 참조 무결성 문제가 발생할 수 있습니다. 업데이트된 기존 행의 경우 해당 행을 건너뛰고 다음 번까지 동기화하지 않습니다.
새 동기화 버전보다 큰 버전을 포함하는 변경 내용까지 모두 포함합니다.

새 동기화 버전보다 큰 버전을 포함하는 행은 다음 동기화에서 다시 얻게 됩니다. 이를 예상하여 응용 프로그램에서 처리해야 합니다.

앞의 두 옵션 외에도 작업에 따라 두 옵션을 결합한 접근 방식을 시도해 볼 수 있습니다. 예를 들어 행이 만들어지거나 삭제된 다음 번 동기화 버전 이후의 변경 내용은 무시하고 업데이트는 무시하지 않는 응용 프로그램을 고안할 수도 있습니다.

[!참고]

변경 내용 추적 또는 사용자 지정 추적 메커니즘을 사용할 때 응용 프로그램에 적합한 접근 방식을 선택하려면 상당한 분석 작업이 필요합니다. 따라서 스냅숏 격리를 사용하는 것이 훨씬 간단합니다.

참고 항목

태스크

변경 내용 추적 사용

개념

변경 내용 추적에서 데이터베이스에 대한 변경 내용을 처리하는 방법

변경 내용 추적 및 데이터 복원

변경 내역

업데이트된 내용
데이터베이스의 모든 테이블에 대해 last_synchronization_version의 유효성을 검사하는 코드 샘플에서 오타를 수정했습니다.

다음을 통해 공유

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

변경 내용 추적 함수 정보

초기 데이터 가져오기

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

버전 번호

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

열 추적 사용

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

스냅숏 격리 사용

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

참고 항목

태스크

개념

변경 내역

추가 리소스