CHANGETABLE (Transact-SQL)

Syntax

CHANGETABLE (
    { CHANGES table , last_sync_version
    | VERSION table , <primary_key_values> } )
[AS] table_alias [ ( column_alias [ ,...n ] )

<primary_key_values> ::=
( column_name [ , ...n ] ) , ( value [ , ...n ] )

Arguments

• CHANGES table , last_sync_version
  Returns tracking information for all changes to a table that have occurred since the version that is specified by last_sync_version.

  • table
    Is the user-defined table on which to obtain tracked changes. Change tracking must be enabled on the table. A one-, two-, three-, or four-part table name can be used. The table name can be a synonym to the table.

  • last_sync_version
    When it obtains changes, the calling application must specify the point from which changes are required. The last_sync_version specifies that point. The function returns information for all rows that have been changed since that version. The application is querying to receive changes with a version greater than last_sync_version.

    Typically, before it obtains changes, the application will call CHANGE_TRACKING_CURRENT_VERSION() to obtain the version that will be used the next time changes are required. Therefore, the application does not have to interpret or understand the actual value.

    Because last_sync_version is obtained by the calling application, the application has to persist the value. If the application loses this value then it will need to re-initialize data.

    .

    last_sync_version is bigint. The value must be scalar. An expression will cause a syntax error.

    If the value is NULL, all tracked changes are returned.

    last_sync_version should be validated to ensure that it is not too old, because some or all the change information might have been cleaned up according to the retention period configured for the database. For more information, see CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) and ALTER DATABASE SET Options (Transact-SQL).

• VERSION table, { <primary_key_values> }
  Returns the latest change tracking information for a specified row. Primary key values must identify the row. <primary_key_values> identifies the primary key columns and specifies the values. The primary key column names can be specified in any order.

  • Table
    Is the user-defined table on which to obtain change tracking information. Change tracking must be enabled on the table. A one-, two-, three-, or four-part table name can be used. The table name can be a synonym to the table.

  • column_name
    Specifies the name of primary key column or columns. Multiple column names can be specified in any order.

  • Value
    Is the value of the primary key. If there are multiple primary key columns, the values must be specified in the same order as the columns appear in the column_name list.

• [AS] table_alias [ (column_alias [ ,...n ] ) ]
  Provides names for the results that are returned by CHANGETABLE.

  • table_alias
    Is the alias name of the table that is returned by CHANGETABLE. table_alias is required and must be a valid identifier.

  • column_alias
    Is an optional column alias or list of column aliases for the columns that are returned by CHANGETABLE. This enables column names to be customized in case there are duplicate names in the results.

Return Types

table

Return Values

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 last_sync_version (the baseline). Note Computed columns are never listed as changed. The value is NULL when any one of the following conditions is true: Column change tracking is not enabled. The operation is an insert or delete operation. All nonprimary key columns were updated in one operation. This binary value should not be interpreted directly. Instead, to interpret it, use CHANGE_TRACKING_IS_COLUMN_IN_MASK().
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

Remarks

The CHANGETABLE function is typically used in the FROM clause of a query as if it were a table.

CHANGETABLE(CHANGES...)

CHANGETABLE(VERSION...)

Permissions

Requires the following permissions on the table that is specified by the table value to obtain change tracking information:

• SELECT permission on the primary key columns

• VIEW CHANGE TRACKING

Examples

A. Returning rows for an initial synchronization of 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

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

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

-- @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);

See Also

Reference

Concepts