CHANGETABLE (Transact-SQL)

文章
07/05/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

傳回數據表的變更追蹤資訊。您可以使用這個語句傳回數據表的所有變更，或針對特定數據列變更追蹤資訊。

語法

CHANGETABLE (  
    { CHANGES <table_name> , <last_sync_version> 
    | VERSION <table_name> , <primary_key_values> } 
    , [ FORCESEEK ] 
    )  
[AS] <table_alias> [ ( <column_alias> [ ,...n ] )  
  
<primary_key_values> ::=  
( <column_name> [ , ...n ] ) , ( <value> [ , ...n ] )

引數

變更 table_name 、 last_sync_version
傳回自 last_sync_version 所指定版本后，已發生之數據表之所有變更的追蹤資訊。

table_name
這是要取得追蹤變更的使用者定義數據表。數據表上必須啟用變更追蹤。您可以使用一、二、三部分或四部分的數據表名稱。數據表名稱可以是數據表的同義字。

last_sync_version
可為 Null 的 bigint 純量值。表達式會導致語法錯誤。如果值為 NULL，則會傳回所有追蹤的變更。當它取得變更時，呼叫端應用程式必須指定需要變更的點。 last_sync_version會指定該點。函式會傳回自該版本以來已變更之所有數據列的資訊。應用程式正在查詢，以接收版本大於 last_sync_version的變更。一般而言，在取得變更之前，應用程式會呼叫 CHANGE_TRACKING_CURRENT_VERSION() 以取得下次需要變更時將使用的版本。因此，應用程式不需要解譯或了解實際值。由於 呼叫端應用程式會取得last_sync_version ，因此應用程式必須保存值。如果應用程式遺失此值，則必須重新初始化數據。 last_sync_version應該經過驗證，以確保它不是太舊，因為某些或所有變更資訊可能已根據針對資料庫設定的保留期間進行清除。如需詳細資訊，請參閱 CHANGE_TRACKING_MIN_VALID_VERSION （Transact-SQL）和 ALTER DATABASE SET 選項（Transact-SQL）。

VERSION table_name， { primary_key_values }
傳回指定數據列的最新變更追蹤資訊。主鍵值必須識別數據列。 primary_key_values識別主鍵數據行，並指定值。主鍵數據行名稱可以依任何順序指定。

table_name
這是要取得變更追蹤資訊的使用者定義數據表。數據表上必須啟用變更追蹤。您可以使用一、二、三部分或四部分的數據表名稱。數據表名稱可以是數據表的同義字。

column_name
指定主鍵數據行或資料行的名稱。您可以依任何順序指定多個資料行名稱。

value
這是主鍵的值。如果有多個主鍵數據行，則必須以與數據行出現在 column_name 清單中的順序相同來指定值。

[ FORCESEEK ]
適用於：SQL Server （從 SQL Server 2016 （13.x） SP2 CU16、SQL Server 2017 （14.x） CU24 和 SQL Server 2019 （15.x） CU11 開始）、Azure SQL 資料庫和 Azure SQL 受控執行個體

選擇性參數，強制使用搜尋作業來存取 table_name。在某些情況下，很少的數據列已變更，掃描作業仍可用來存取 table_name。如果掃描作業造成效能問題，請使用 FORCESEEK 參數。

[AS] table_alias [ （column_alias [ ,...n ] ] ]
提供 CHANGETABLE 所傳回結果的名稱。

table_alias
這是 CHANGETABLE 所傳回之數據表的別名名稱。 table_alias是必要的，而且必須是有效的標識符。

column_alias
這是 CHANGETABLE 所傳回之資料行的選擇性數據行別名或數據行別名清單。這可讓數據行名稱自定義，以防結果中有重複的名稱。

傳回型別

table

傳回值

CHANGETABLE 變更

指定 CHANGES 時，會傳回具有下列數據行的零個或多個數據列。

資料行名稱	資料類型	描述
SYS_CHANGE_VERSION	bigint	與數據列上次變更相關聯的版本值
SYS_CHANGE_CREATION_VERSION	bigint	與上次插入作業相關聯的版本值。
SYS_CHANGE_OPERATION	nchar（1）	指定變更的類型： U = 更新 I = 插入 D = 刪除
SYS_CHANGE_COLUMNS	varbinary（4100）	列出自last_sync_version之後已變更的數據行（基準）。請注意，計算數據行永遠不會列為已變更。當下列任一條件成立時，此值為 NULL：未啟用資料行變更追蹤。作業是插入或刪除作業。所有非主要索引鍵數據行都會在一個作業中更新。這個二進位值不應該直接解譯。相反地，若要解譯它，請使用 CHANGE_TRACKING_IS_COLUMN_IN_MASK（）。
SYS_CHANGE_CONTEXT	varbinary(128)	變更您可以選擇性地指定的內容資訊，方法是使用 WITH 子句做為INSERT、UPDATE或DELETE語句的一部分。
<主鍵數據行值>	與用戶數據表數據行相同	追蹤數據表的主鍵值。這些值可唯一識別用戶數據表中的每個數據列。

CHANGETABLE VERSION

指定 VERSION 時，會傳回一個具有下列數據行的數據列。

資料行名稱	資料類型	描述
SYS_CHANGE_VERSION	bigint	與數據列相關聯的目前變更版本值。如果變更的期間未超過變更追蹤保留期限，或啟用變更追蹤之後，數據列尚未變更，則值為 NULL。
SYS_CHANGE_CONTEXT	varbinary(128)	變更您可以選擇性地指定的內容資訊，方法是使用WITH子句做為INSERT、UPDATE或DELETE語句的一部分。
<主鍵數據行值>	與用戶數據表數據行相同	追蹤數據表的主鍵值。這些值可唯一識別用戶數據表中的每個數據列。

備註

CHANGETABLE 函式通常用於查詢的FROM子句中，就像是數據表一樣。

CHANGETABLE（CHANGES...）

若要取得新或修改之數據列的數據列，請使用主鍵數據行，將結果集聯結至用戶數據表。用戶數據表中已變更的每個數據列只會傳回一個數據列，即使自 last_sync_version 值之後，相同的數據列也發生了多次變更。

主鍵數據行變更永遠不會標示為更新。如果主鍵值變更，則會將其視為刪除舊值和插入新值。

如果您刪除數據列，然後插入具有舊主鍵的數據列，該變更會被視為數據列中所有數據行的更新。

和 SYS_CHANGE_COLUMNS 數據行傳SYS_CHANGE_OPERATION回的值會相對於指定的基準（last_sync_version）。例如，如果在版本進行插入作業，並在版本1015進行更新作業，而且如果基準last_sync_version為 12，則會報告更新。 如果last_sync_version值為 8，則會報告插入。 SYS_CHANGE_COLUMNS 永遠不會將計算數據行回報為已更新。

一般而言，會追蹤在用戶數據表中插入、更新或刪除數據的所有作業，包括 MERGE 語句。

不會追蹤影響使用者資料表資料的下列作業：

UPDATETEXT執行語句。此語句已被取代，並將在未來的 SQL Server 版本中移除。不過，會追蹤使用 .WRITE UPDATE語句的子句所做的變更。
使用 TRUNCATE TABLE刪除數據列。截斷數據表時，與數據表相關聯的變更追蹤版本資訊會重設，就好像數據表上剛啟用變更追蹤一樣。用戶端應用程式應該一律驗證其上次同步處理的版本。如果數據表已截斷，驗證就會失敗。

CHANGETABLE(VERSION...)

如果指定不存在的主鍵，則會傳回空的結果集。

如果變更未超過保留期間（例如，清除已移除變更資訊），或自從數據表啟用變更追蹤之後，數據列從未變更過，則的值 SYS_CHANGE_VERSION 可能是 NULL。

權限

SELECT需要主鍵數據行的許可權，以及VIEW CHANGE TRACKINGtable_name>值所<指定之數據表的許可權，才能取得變更追蹤資訊。

範例

A. 傳回數據的初始同步處理數據列

下列範例示範如何取得數據表數據的初始同步處理數據。查詢會傳回所有數據列數據及其相關聯的版本。然後，您可以將此資料插入或新增至將包含同步處理資料的系統。

-- Get all current rows with associated version  
SELECT e.[Emp ID], e.SSN, e.FirstName, e.LastName,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT  
FROM Employees AS e  
CROSS APPLY CHANGETABLE   
    (VERSION Employees, ([Emp ID], SSN), (e.[Emp ID], e.SSN)) AS c;

B. 列出自特定版本以來所做的所有變更

下列範例會列出自指定版本@last_sync_version) （）以來數據表中所做的所有變更。 [Emp ID] 和 SSN 是複合主鍵中的數據行。

DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT [Emp ID], SSN,  
    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,  
    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C;

C. 取得同步處理的所有已變更數據

下列範例示範如何取得已變更的所有數據。此查詢會將變更追蹤資訊與用戶數據表聯結，以便傳回用戶數據表資訊。 LEFT OUTER JOIN會使用，以便針對已刪除的數據列傳回數據列。

-- Get all changes (inserts, updates, deletes)  
DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT e.FirstName, e.LastName, c.[Emp ID], c.SSN,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,  
    c.SYS_CHANGE_COLUMNS, c.SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS c  
    LEFT OUTER JOIN Employees AS e  
        ON e.[Emp ID] = c.[Emp ID] AND e.SSN = c.SSN;

D. 使用 CHANGETABLE 偵測衝突（VERSION...）

下列範例示範只有在上次同步處理之後的數據列尚未變更時，才更新數據列。使用取得 CHANGETABLE特定數據列的版本號碼。如果數據列已更新，則不會進行變更，而且查詢會傳回數據列最近變更的相關信息。

-- @last_sync_version must be set to a valid value  
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);

另請參閱

變更追蹤函數 (Transact-SQL)
追蹤資料變更 (SQL Server)
CHANGE_TRACKING_IS_COLUMN_IN_MASK （Transact-SQL）
CHANGE_TRACKING_CURRENT_VERSION (Transact-SQL)
CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

分享方式：