使用變更追蹤函數來取得變更

關於變更追蹤函數

應用程式可以使用下列函數來取得在資料庫中所做的變更和這些變更的相關資訊：

• 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…) 之間的時間內進行。原本有效的上一次同步處理版本可能會在取得變更時不再有效。因此，可能會傳回不正確的結果。

• 持續進行的 DML 作業會在 Sales 和 SalesOrders 資料表中進行，例如下列作業：

  • 已經使用 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

使用快照集隔離的替代方案

請參閱

工作

概念

變更記錄