使用更改跟踪 (SQL Server)

应用程序可以使用以下函数来获取在数据库中所做的更改以及有关这些更改的信息：

CHANGETABLE(CHANGES …) 函数

此行集函数用于查询更改信息。 该函数查询内部更改跟踪表中存储的数据。 该函数返回的结果集中包含已更改的行的主键和其他更改信息，例如，操作、更新的列以及行版本。

CHANGETABLE(CHANGES …) 将上次同步版本作为参数。 @last_synchronization_version variable.‘ data-guid="ad64d4a6377f4893610a73fd98bfe780">上次同步版本使用@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 table.‘ data-guid="3cdd8bfcc8b04ad12e587d35a4761fd0">例如，下面的查询获取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 table to obtain the values for the Name and ListPrice columns.‘ data-guid="19f9c8b9079f2c9593c98510e9ff77f0">例如，下面的查询与SalesLT.Product表联接在一起以获取Name和ListPrice列的值。 OUTER JOIN.‘ data-guid="08fbb31723995948ebd40eb6b513bdf1">请注意，本例中使用了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 data type.‘ data-guid="beef5d7b72372561f05674f9605895b6">从某种意义上讲，更改跟踪版本在概念上与 rowversion 数据类型类似。

更改的相关信息将保留有限的一段时间。 时间长度是由 CHANGE_RETENTION 参数控制的，可以将该参数指定为 ALTER DATABASE 的一部分。

请注意，为 CHANGE_RETENTION 指定的时间决定了所有应用程序必须每隔多长时间从数据库中请求一次更改。 last_synchronization_version that is older than the minimum valid synchronization version for a table, that application cannot perform valid change enumeration.‘ data-guid="8448f8b790a6fd97780c263420f61292">如果应用程序使用的last_synchronization_version 值早于表的最低有效同步版本，该应用程序将无法执行有效的更改枚举。 这是因为，可能已清除了某些更改信息。 last_synchronization_version that it plans to pass to CHANGETABLE(CHANGES …).‘ data-guid="39d85c476dbd50d79357cc76171f4531">在应用程序使用 CHANGETABLE(CHANGES …) 获取更改之前，该应用程序必须验证计划传递给 CHANGETABLE(CHANGES …) 的 last_synchronization_version 值。 last_synchronization_version is not valid, that application must reinitialize all the data.‘ data-guid="d5cd1309a3b7f02017462b06cd9a4d32">如果last_synchronization_version 值无效，该应用程序必须重新初始化所有数据。

last_synchronization_version for each table.‘ data-guid="cb4ccc91a1d1d74d093d29b944caeb32">下面的示例说明了如何验证每个表的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 can be checked against all tables in the database.‘ data-guid="6b2794a8aa170a125ad3bcf7b9eb457b">正如下面的示例所示，可以对照数据库中的所有表检查last_synchronization_version值的有效性。

    -- Check all tables with change tracking enabled
    IF EXISTS (
      SELECT COUNT(*) 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 column will be NULL if that column did not change.‘ data-guid="677f025afa164f00ddbbd5806140d0e1">在下面的示例中，如果CT_ThumbnailPhoto列未更改，则为该列返回NULL。 NULL because it was changed to NULL - the application can use the CT_ThumbNailPhoto_Changed column to determine whether the column changed.‘ data-guid="491796f3fd3f92b6f97caa21bd38015c">该列本身也可能为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 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

使用快照隔离的替代方法

使用快照隔离有一些替代方法，但它们需要完成更多的工作以确保满足所有应用程序要求。 last_synchronization_version is valid and data is not removed by the cleanup process before changes are obtained, do the following:‘ data-guid="35a6efd137142bbcf753187c3ae973e8">若要确保 last_synchronization_version 有效，并且清除进程在获取更改之前没有删除数据，请执行以下操作：

1. last_synchronization_version after the calls to CHANGETABLE().‘ data-guid="69d7075ecc047fb3a7f4aeee3082507e">在调用 CHANGETABLE() 后检查 last_synchronization_version。

2. last_synchronization_version as part of each query to obtain changes by using CHANGETABLE().‘ data-guid="1460242ff76fc99f9a3956320d48edfe">在使用 CHANGETABLE() 获取更改的每个查询中检查 last_synchronization_version。

在为下次枚举获取同步版本后，可能会发生数据更改。 可以使用两种方法来处理这种情况。 所使用的选项取决于应用程序及其处理每种方法的副作用的方式：

• 忽略版本高于新同步版本的更改。

  这种方法具有以下副作用：如果创建或更新的行版本早于新同步版本，将跳过新行或更新的行，但以后会对其进行更新。 如果有一个新行，并且另一个表中创建的行引用跳过的行，则可能会出现引用完整性问题。 如果更新了某个现有行，将跳过该行，并且下次才会对其进行同步。

• 包括所有更改，即便更改的版本高于新同步版本。

  下次进行同步时，将重新获取版本高于新同步版本的行。 应用程序必须能够预料并处理这种情况。

除了上述两个选项外，还可以设计结合这两个选项的方法，具体取决于所执行的操作。 例如，您可能希望应用程序最好忽略晚于下次同步版本（在该版本中创建或删除了行）的更改，但不忽略更新。

注意
若要在使用更改跟踪（或任何自定义跟踪机制）时选择适合应用程序的方法，您需要完成大量的分析工作。 因此，使用快照隔离要简单得多。

 [返回页首]

在双向同步方案中，客户端应用程序必须确定在应用程序上次获取更改后某一行是否有更新。

下面的示例说明了如何使用 CHANGETABLE(VERSION …) 函数以最有效的方式检查冲突，而不是使用单独的查询。 CHANGETABLE(VERSION …) determines the SYS_CHANGE_VERSION for the row specified by @product id.‘ data-guid="6d6c3e2517298a8d50a13b24c055cf4e">在该示例中，CHANGETABLE(VERSION …)确定@product id所指定的行的SYS_CHANGE_VERSION。CHANGETABLE(CHANGES …) can obtain the same information, but that would be less efficient.‘ data-guid="246b37460b2815db97b698b163c8adf2">CHANGETABLE(CHANGES …)可以获取相同的信息，但效率较低。 SYS_CHANGE_VERSION for the row is larger than the value of @last_sync_version, there is a conflict.‘ data-guid="57cd822c2374a606bc2ba6dcec7afb2a">如果该行的SYS_CHANGE_VERSION值大于@last_sync_version值，则说明有冲突。 如果有冲突，则不会更新该行。ISNULL() check is required because there might be no change information available for the row.‘ data-guid="6e322039d5e8b98985149a369c5ce779">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 子句，应用程序可以将上下文信息与更改信息存储在一起。 可随后从 CHANGETABLE(CHANGES …) 返回的 SYS_CHANGE_CONTEXT 列中获取此信息。

上下文信息通常用于确定更改源。 如果可以确定更改源，数据存储区在重新同步时可使用该信息来避免获取更改。

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

注意
在快照事务启动之后，在该快照事务中正在更新的行可能已经在另一个事务中进行了更新。 在这种情况下，会发生快照隔离更新冲突并导致该事务终止。 如果发生这种情况，请重试此更新。 随后，这会导致检测到更改跟踪冲突并且不会更改任何行。

 [返回页首]