CHANGETABLE (Transact-SQL)
Returns change tracking information for a table.You can use this statement to return all changes for a table or change tracking information for a specific row.
CHANGETABLE CHANGES
When CHANGES is specified, zero or more rows that have the following columns are returned.
Column name | Data type | Description |
|---|---|---|
SYS_CHANGE_VERSION | bigint | Version value that is associated with the last change to the row |
SYS_CHANGE_CREATION_VERSION | bigint | Version values that are associated with the last insert operation. |
SYS_CHANGE_OPERATION | nchar(1) | Specifies the type of change: U = Update I = Insert D = Delete |
SYS_CHANGE_COLUMNS | varbinary(4100) | Lists the columns that have changed since the baseline version.  NoteComputed columns are never listed as changed. The value is NULL when any one of the following conditions is true:
|
SYS_CHANGE_CONTEXT | varbinary(128) | Change context information that you can optionally specify by using the WITH clause as part of an INSERT, UPDATE, or DELETE statement. |
<primary key column value> | Same as the user table columns | The primary key values for the tracked table. These values uniquely identify each row in the user table. |
CHANGETABLE VERSION
When VERSION is specified, one row that has the following columns is returned.
Column name | Data type | Description |
|---|---|---|
SYS_CHANGE_VERSION | bigint | Current change version value that is associated with the row. The value is NULL if a change has not been made for a period longer than the change tracking retention period, or the row has not been changed since change tracking was enabled. |
SYS_CHANGE_CONTEXT | varbinary(128) | Change context information that you can optionally specify by using the WITH clause as part of an INSERT, UPDATE, or DELETE statement. |
<primary key column value> | Same as the user table columns | The primary key values for the tracked table. These values uniquely identify each row in the user table. |
The CHANGETABLE function is typically used in the FROM clause of a query as if it were a table.
CHANGETABLE(CHANGES...)
To obtain row data for new or modified rows, join the result set to the user table by using the primary key columns. Only one row is returned for each row in the user table that has been changed, even if there have been multiple changes to the same row since the last_sync_version value.
Primary key column changes are never marked as updates. If a primary key value changes, it is considered to be a delete of the old value and an insert of the new value.
If you delete a row and then insert a row that has the old primary key, the change is seen as an update to all columns in the row.
The values that are returned for the SYS_CHANGE_OPERATION and SYS_CHANGE_COLUMNS columns are relative to the baseline version that is specified. For example, if an insert operation was made at version 10 and an update operation at version 15, and if the baseline is last_sync_version is 12, an update will be reported. If the last_sync_version value is 8, an insert will be reported. SYS_CHANGE_COLUMNS will never report computed columns as having been updated.
Generally, all operations that insert, update, or delete of data in user tables are tracked, including the MERGE statement.
The following operations that affect user table data are not tracked:
Executing the UPDATETEXT statement
This statement is deprecated and will be removed in a future version of SQL Server. However, changes that are made by using the .WRITE clause of the UPDATE statement are tracked.
Deleting rows by using TRUNCATE TABLE
When a table is truncated, the change tracking version information that is associated with the table is reset as if change tracking has just been enabled on the table. A client application should always validate its last synchronized version. The validation fails if the table has been truncated.
CHANGETABLE(VERSION...)
An empty result set is returned if a nonexistent primary key is specified.
The value of SYS_CHANGE_VERSION might be NULL if a change has not been made for longer than the retention period (for example, the cleanup has removed the change information) or the row has never been changed since change tracking was enabled for the table.
A. Returning rows for an initial synchronization of data
The following example shows how to obtain data for an initial synchronization of the table data. The query returns all row data and their associated versions. You can then insert or add this data to the system that will contain the synchronized data.
-- 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. Listing all changes that were made since a specific version
The following example lists all changes that were made in a table since the specified version (@last_sync_version). [Emp ID] and SSN are columns in a composite primary key.
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. Obtaining all changed data for a synchronization
The following example shows how you can obtain all data that has changed. This query joins the change tracking information with the user table so that user table information is returned. A LEFT OUTER JOIN is used so that a row is returned for deleted rows.
-- 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. Detecting conflicts by using CHANGETABLE(VERSION...)
The following example shows how to update a row only if the row has not changed since the last synchronization. The version number of the specific row is obtained by using CHANGETABLE. If the row has been updated, changes are not made and the query returns information about the most recent change to the row.
-- @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);
