使用變更追蹤 (SQL Server)

適用於：SQL Server Azure SQL Database Azure SQL Managed Instance Microsoft Fabric 中的 SQL 資料庫

使用變更追蹤的應用程式必須能夠取得追蹤變更、將這些變更套用至另一個資料存放區，以及更新來源資料庫。本文描述如何執行這些任務，以及在發生容錯移轉且必須從備份還原資料庫時，變更追蹤所扮演的角色。

通过使用更改跟踪函数获取更改

描述如何使用變更追蹤函數來取得變更以及對資料庫所做變更的相關資訊。

關於變更追蹤功能

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

CHANGETABLE(CHANGES ...) 作用

這個資料列集函數是用於查詢是否有變更資訊。此函數會查詢儲存在內部變更追蹤資料表中的資料。此函數會傳回結果集，其中包含已變更之資料列的主索引鍵，以及其他變更資訊，例如作業、更新的資料行及此資料列的版本。

CHANGETABLE(CHANGES ...) 會採用最後一個同步處理版本作為引數。上次同步版本是使用 @last_synchronization_version 变量获得的。上一次同步處理版本的語意如下：
呼叫端已獲得變更，並了解所有變更，直到並包括最後一次同步版本。
CHANGETABLE(CHANGES ...) 因此，會傳回在上次同步化版本之後發生的所有變更。

下圖顯示如何使用 CHANGETABLE(CHANGES ...) 來取得變更。

在此範例中，用戶端 A 上次在上午 9:30 同步，而用戶端 B 上次在上午 10:30 同步。在上午 10:00 且再次在上午 11:00，對資料進行了幾次的變更。下列範例摘要說明這些追蹤的變更。

CHANGETABLE(CHANGES...) 輸出 - 上午11:30

用戶端 A 上次在上午 9:30 同步。

`Product ID`	作業	欄位
139	更新	名稱、價格
140	刪除	-
141	插入	-

用戶端 B 上次在上午 10:30 同步。

`Product ID`	作業	欄位
139	更新	價格
140	刪除	-
141	更新	價格

CHANGE_TRACKING_CURRENT_VERSION() 作用

用於取得下一次查詢變更時將使用的目前版本。此版本表示最後提交的交易的版本。
CHANGE_TRACKING_MIN_VALID_VERSION() 作用

用來取得用戶端可以擁有的最小有效版本，並仍從取得 CHANGETABLE()有效結果。用戶端應該針對此函數所傳回的值，檢查上一次的同步處理版本。如果最後一個同步處理版本小於此函式所傳回的版本，用戶端將無法取得 CHANGETABLE() 有效的結果，而且必須重新初始化。

取得初始資料

在應用程式首次取得變更之前，應用程式必須先傳送查詢，以便取得初始資料和同步處理版本。應用程式必須直接從資料表取得適當的資料，然後使用來 CHANGE_TRACKING_CURRENT_VERSION() 取得初始版本。此版本將傳遞到 CHANGETABLE(CHANGES ...) 第一次取得變更時。

下列範例將說明如何取得初始同步處理版本和初始資料集。

declare @synchronization_version bigint;

-- 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 變更。

declare @last_synchronization_version bigint;

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 資料類型的概念很相似。

驗證上次同步處理的版本

變更的相關資訊會保留在一段有限的時間內。時間長度是由可指定為的一部分CHANGE_RETENTION的參數所ALTER DATABASE控制。

指定的 CHANGE_RETENTION 時間決定所有應用程式必須從資料庫要求變更的頻率。如果應用程式的值 last_synchronization_version 早於資料表的最小有效同步處理版本，則該應用程式無法執行有效的變更列舉。這是因為某些變更資訊可能已經清除了。在應用程式使用 CHANGETABLE(CHANGES ...)取得變更之前，應用程式必須驗證它計劃傳遞至 last_synchronization_version的值CHANGETABLE(CHANGES ...)。如果的 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 1 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;

使用資料行追蹤

資料行追蹤可讓應用程式僅針對已經變更的資料行 (而非整個資料列) 取得資料。例如，假設某份資料表具有一個或多個龐大但很少變更的資料行，而且具有其他經常變更的資料行。如果沒有使用資料行追蹤，應用程式只能判斷出某個資料列已經變更，而且必須同步處理所有資料，包括大型資料行的資料。不過，透過資料行追蹤，應用程式就可以判斷出大型資料行的資料是否已變更，而且僅同步處理該項資料 (如果已經變更的話)。

直欄追蹤資訊會出現在函數所SYS_CHANGE_COLUMNS傳回的直欄中CHANGETABLE(CHANGES ...)。

可以使用資料行追蹤，以便針對未變更的資料行傳回。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()取得下次可用來取得變更的版本。
使用 Sales取得表格的CHANGETABLE(CHANGES ...)變更。
使用 SalesOrders取得表格的CHANGETABLE(CHANGES ...)變更。

在資料庫中進行的兩個處理序可能會影響先前步驟所傳回的結果：

清除處理序會在背景執行而且會移除早於指定之保留週期的變更追蹤資訊。

清除處理序是個別的背景處理序，它會使用您針對資料庫設定變更追蹤時指定的保留週期。其問題在於，此清除處理序可能會在驗證上一次同步處理版本與呼叫 CHANGETABLE(CHANGES...) 之間的時間內進行。原本有效的上一次同步處理版本可能會在擷取變更時不再有效。因此，可能會傳回不正確的結果。
持續進行的 DML 作業會在 Sales 和 SalesOrders 資料表中進行，例如下列作業：
- 在使用取得 CHANGE_TRACKING_CURRENT_VERSION()下次版本之後，可以變更表格。因此，可能會傳回比預期更多的變更。
- 交易可能會在從 Sales 資料表中擷取變更的呼叫與從 SalesOrders 資料表中擷取變更的呼叫之間的時間內提交。因此，SalesOrder 資料表的結果可能會具有不存在於 Sales 資料表中的外部索引鍵值。

若要克服先前列出的挑戰，我們建議您使用快照集隔離。這樣做有助於確保變更資訊的一致性，並且避免發生與背景清除工作有關的競爭情形。如果您未使用快照交易，則開發使用變更追蹤的應用程式時，可能需要投入相當多的心力。

使用快照隔離

變更追蹤已設計為能夠順利支援快照隔離方式的運作。必須為資料庫啟用快照隔離。將取得變更所必要的所有步驟都必須包含在快照交易中。這樣做可確保取得變更時，快照交易內部的查詢看不到對資料所做的所有變更。

若要取得快照集交易內部的資料，請執行下列步驟：

將交易隔離等級設定為快照並開始一個交易。
使用驗證 CHANGE_TRACKING_MIN_VALID_VERSION()最後的同步化版本。
使用 CHANGE_TRACKING_CURRENT_VERSION()取得下次要使用的版本。
Sales使用CHANGETABLE(CHANGES ...)
SalesOrders使用CHANGETABLE(CHANGES ...)
提交交易。

請記住一些重點，因為取得變更的所有步驟都在快照交易中進行：

如果在驗證最後一個同步處理版本之後發生清除，則結果 CHANGETABLE(CHANGES ...) 仍會有效，因為清除所執行的刪除作業不會在交易內顯示。
在取得下一個同步化版本之後，對資料表或Sales資料表所做的SalesOrders任何變更都不會顯示，而且呼叫CHANGETABLE(CHANGES ...)永遠不會傳回版本高於所CHANGE_TRACKING_CURRENT_VERSION()傳回的變更。資料表與Sales資料表之間的SalesOrders一致性也會維護，因為在呼叫之間的CHANGETABLE(CHANGES ...)時間內認可的交易將不可見。

下列範例將說明如何針對資料庫啟用快照集隔離。

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT
    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

如需快照交易的詳細資訊，請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

清理及快照隔離

在相同資料庫或相同執行個體中兩個不同的資料庫上同時啟用快照隔離及變更追蹤，可能會導致清除程序在具有快照隔離的資料庫中有已開啟的交易時，將過期的資料列留在 sys.syscommittab 中。發生這種情況的可能原因是變更追蹤清理過程在執行清除時，會將整個實例的低水位標記 (即安全的清除版本) 納入考量範圍。這樣做的目的是確保變更追蹤自動清除程序不會移除任何資料列，因為已啟用快照隔離的資料庫中已開啟交易可能需要這些資料列。盡可能地縮短讀取已提交快照隔離的時間及快照隔離交易，以確保及時清除 sys.syscommittab 中的過期資料列。

快照隔離的替代方案

雖然我們提供了使用快照集隔離的替代方案，但是這些替代方案需要進行更多工作，才能確保符合所有應用程式需求。若要確保有效 last_synchronization_version ，且在取得變更之前清除程式不會移除資料，請遵循下列步驟：

調用last_synchronization_version後檢查CHANGETABLE()。
last_synchronization_version檢查作為每一個查詢的一部分，以使用 CHANGETABLE()來取得變更。

在取得下一次列舉的同步版本後，可能會發生變更。有兩種方式可以處理此情況。所使用的選項會因應用程式以及它如何處理每個方法的副作用而定：

忽略版本大於新同步處理版本的變更。

這種方法的副作用是，如果全新或更新的資料列是在新同步處理版本之前建立或更新，但是之後再更新，系統就會略過此資料列。如果有一個新的資料列，而且在另一份資料表中建立了參考已略過資料列的資料列，可能會發生參考完整性問題。如果有一個更新的現有資料列，就會略過該資料列，而且在下一次之前不會進行同步處理。
包含所有變更，包括那些版本號大於新同步版本的變更。

下一次同步處理時，將再次取得版本大於新同步處理版本的資料列。應用程式必須預期並處理這點。

除了上述兩個選項以外，您可以根據作業，設計出結合這兩個選項的方法。例如，您可能需要一個應用程式，其功能是忽略比建立或刪除資料列的下一個同步版本更新的變更，但不忽略更新。

注意

當您使用變更追蹤 (或任何自訂追蹤機制) 時，選擇適用於應用程式的方法需要進行大量分析。因此，使用快照隔離就簡單多了。

變更追蹤如何處理資料庫的變更

某些使用變更追蹤的應用程式會使用另一個資料存放區來執行雙向同步處理。也就是說，在 SQL Server 資料庫中進行的變更會在其他資料存放區中更新，而在其他存放區中進行的變更則會在 SQL Server 資料庫中更新。

當某個應用程式使用另一個資料存放區的更新來更新本機資料庫時，此應用程式就必須執行下列作業：

檢查是否有衝突。

如果兩個資料存放區中的相同資料同時變更，就會發生衝突。應用程式必須能夠檢查是否有衝突，而且取得足夠的資訊來解決衝突。
儲存應用程式內容資訊。

應用程式會儲存具有變更追蹤資訊的資料。當從本機資料庫中取得變更時，這些資訊會與其他變更追蹤資訊一起提供。這個內容資訊的常見範例是屬於變更來源之資料存放區的識別碼。

若要執行上述作業，同步處理應用程式可以使用下列函數：

CHANGETABLE(VERSION...)

當應用程式正在進行變更時，它可以使用此函數來檢查是否有衝突。這個函數會針對變更追蹤資料表中的指定資料列，取得最新變更追蹤資訊。此變更追蹤資訊包括上一次變更的資料列版本。這項資訊可讓應用程式判斷，此資料列在上一次應用程式同步處理之後是否已變更。
WITH CHANGE_TRACKING_CONTEXT

應用程式可以使用這個子句來儲存內容資料。

檢查是否有衝突

在雙向同步處理狀況中，用戶端應用程式必須判斷自從應用程式上一次取得變更以來，某個資料列是否未更新。

下列範例示範如何使用函 CHANGETABLE(VERSION ...) 式以最有效的方式檢查衝突，而不需要單獨的查詢。在此範例中， CHANGETABLE(VERSION ...) 會針對 SYS_CHANGE_VERSION 所指定的資料列，判斷 @product id。 CHANGETABLE(CHANGES ...) 可以取得相同的資訊，但是這樣做比較沒有效率。如果資料列的 SYS_CHANGE_VERSION 值大於 @last_sync_version 的值，就表示發生衝突。如果發生衝突，則不會更新該資料列。 ISNULL() 檢查是必要的，因為資料列可能沒有任何變更資訊可用。如果自從啟用變更追蹤或清除變更資訊以來該資料列未更新，就不會有任何變更資訊存在。

-- Assumption: @last_sync_version has been validated.
UPDATE SalesLT.Product
SET ListPrice = @new_listprice
FROM SalesLT.Product AS P
WHERE ProductID = @product_id
    AND @last_sync_version >= ISNULL((
            SELECT CT.SYS_CHANGE_VERSION
            FROM CHANGETABLE(VERSION SalesLT.Product, (ProductID), (P.ProductID)) AS CT
            ), 0);

下列程式用來檢查更新的資料列數，並能識別更多衝突相關資訊。

-- If the change cannot be made, find out more information.
IF (@@ROWCOUNT = 0)
BEGIN
    -- Obtain the complete change information for the row.
    SELECT
        CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION,
        CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS
    FROM
        CHANGETABLE(CHANGES SalesLT.Product, @last_sync_version) AS CT
    WHERE
        CT.ProductID = @product_id;

    -- Check CT.SYS_CHANGE_VERSION to verify that it really was a conflict.
    -- Check CT.SYS_CHANGE_OPERATION to determine the type of conflict:
    -- update-update or update-delete.
    -- The row that is specified by @product_id might no longer exist 
    -- if it has been deleted.
END

設定上下文資訊

藉由使用子 WITH CHANGE_TRACKING_CONTEXT 句，應用程式可以將內容資訊與變更資訊一起儲存。然後，可以從傳回SYS_CHANGE_CONTEXT的直欄取得CHANGETABLE(CHANGES ...)此資訊。

內容資訊通常是用來識別變更的來源。如果能夠識別變更的來源，再度同步處理時，資料存放區就可以使用該項資訊來避免取得變更。

-- Try to update the row and check for a conflict.
WITH CHANGE_TRACKING_CONTEXT (@source_id)
UPDATE
  SalesLT.Product
SET
  ListPrice = @new_listprice
FROM
  SalesLT.Product AS P
WHERE
  ProductID = @product_id AND
    @last_sync_version >= ISNULL (
    (SELECT CT.SYS_CHANGE_VERSION FROM CHANGETABLE(VERSION SalesLT.Product,
    (ProductID), (P.ProductID)) AS CT),
       0);

確保一致且正確的結果

當應用程式驗證 @last_sync_version 的值時，必須考慮清除處理序。這是因為資料可能在呼叫之後 CHANGE_TRACKING_MIN_VALID_VERSION() ，但在進行更新之前移除。

您應使用快照隔離並在快照交易內部進行變更。

-- Prerequisite is to ensure ALLOW_SNAPSHOT_ISOLATION is ON for the database.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    -- Verify that last_sync_version is valid.
    IF (@last_sync_version <
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('SalesLT.Product')))
    BEGIN
       RAISERROR (N'Last_sync_version too old', 16, -1);
    END
    ELSE
    BEGIN
        -- Try to update the row.
        -- Check @@ROWCOUNT and check for a conflict.
    END;
COMMIT TRAN;

注意

在快照交易啟動後，正在快照交易中更新的資料列有可能在另一個交易中已被更新。在此情況下，將會發生快照隔離更新衝突，並導致交易中止。如果發生這種情況，請重試更新。然後，這會讓變更追蹤衝突得以偵測到，而且不會變更任何資料列。

變更追蹤和資料還原

需要同步處理的應用程式必須考慮已啟用變更追蹤的資料庫還原成舊版資料的情況。從備份還原資料庫之後，當發生容錯移轉到非同步資料庫鏡像，或是使用記錄傳送時發生故障，可能會發生此情況。下列案例說明這個問題：

資料表 T1 有進行變更追蹤，且資料表的最小有效版本為 50。
用戶端應用程式會同步處理版本 100 上的資料，並取得版本 50 與 100 之間所有變更的相關資訊。
版本 100 之後會對資料表 T1 進行其他變更。
在版本 120，發生了失敗，而且資料庫管理員會還原有遺失資料的資料庫。在還原作業之後，資料表會包含直到版本 70 的資料，而最小的同步處理版本仍然是 50。

這表示同步處理的資料存放區包含了不復存在於主要資料存放區內的資料。
T1 被更新許多次。這使得當前版本升級至 130。
用戶端應用程式會再次進行同步，並提供上次同步的版本 100。用戶端將會成功驗證這個號碼，因為 100 大於 50。

用戶端會取得版本 100 與 130 之間的變更。此時，用戶端不會意識到 70 與 100 之間的變更與之前不同。用戶端與伺服器上的資料不會進行同步。

如果資料庫復原到版本 100 之後的某一點，同步處理就不會有任何問題。客戶端和伺服器將會在下一次同步間隔期間正確同步資料。

變更追蹤不提供從遺失資料中進行復原的支援。但是，有兩個選擇可用來偵測這些類型的同步處理問題：

將資料庫版本識別碼儲存在伺服器上，並在每次復原資料庫或是遺失資料時更新這個值。每一個用戶端應用程式都將儲存這個識別碼，而且每一個用戶端在同步處理資料時，都必須驗證此識別碼。如果發生資料遺失，則識別碼將不會相符，且用戶端也將重新初始化。但是有一個缺點，就是若資料遺失未跨越上一次同步處理的界限時，用戶端可能會執行不必要的重新初始化。
當用戶端查詢變更時，請在伺服器上記錄每一個用戶端上一次同步處理的版本號碼。如果資料有問題，則上一次同步處理的版本號碼將不會相符。這表示必須重新初始化。

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-18