Microsoft SQL Server 2008 R2: Introducing Change Tracking – Part 2 of 3

In the first article, I described the characteristics of Change Tracking, how to activate and deactivate it, and how the system objects that support this feature are structured. Assuming that you have mastered these concepts, I will now describe the functions that SQL Server provides to work with Change Tracking.

The fundamentals of Change Tracking: transactions and version

Before describing each function in detail, it is probably a good idea to focus on the concepts of transaction and version, in order to understand how these two key elements work in combination with CT.

Transactions: the real authors of DML operations

As I mentioned in the first article, one of the characteristics of Change Tracking is that it “delegates” to transactions the execution of each DML operation on tracked tables. This is unavoidable – no INSERT, UPDATE, or DELETE can be successfully executed on one of these tables if it hasn’t been incorporated into a transaction.

So what exactly are the criteria for incorporation? Which rows, as a consequence, will be modified by one transaction before its version number is received (all this is fully described below). Let’s examine this in detail in the next two sections.

Implicit transactions

When a user performs DML operations on tracked tables without defining any explicit transaction (through the BEGIN TRANSACTION command), SQL Server starts (taking the place of the user) an implicit transaction by default that:
• intercepts every running DML operation
• executes it
• executes the COMMIT, if it ends without errors, (if it does not end without errors, it will execute
the ROLLBACK)
Let’s look at the following example:

--The following script consists of two DML operations
--1) Inserting two rows into the dbo.Employees table
INSERT INTO
      dbo.Employees
VALUES
      (1, 'Diaz', 'Brenda'),
      (2, 'Blythe', 'Michel')
--2) Ooops! There is a mistake! Let's correct the row with Employeeid = 2
UPDATE
      dbo.Employees
SET
      FirstName = 'Michael'
WHERE
      EmployeeID = 2

This script consists of two DML operations – one INSERT and one UPDATE; since no explicit transactions are defined, SQL Server identifies the first operation, “forwards” it to an implicit transaction and, if it is executed successfully, ends with COMMIT. It then identifies the second operation, the UPDATE, assigns it to a new implicit transaction, and if there are no errors, ends with COMMIT.

So, the first implicit transaction includes the two rows with EmployeeID 1 and 2, while the second transaction modifies one of these rows (the one with EmployeeID equal to 2).

This mechanism is repeated for each DML statement encountered that is not explicitly included in a transaction.

SQL Server uses implicit transactions, even when the DML operations on tracked tables are performed through the Edit Top 200 Rows option of SSMS (available by right-clicking on the tables in the Object Explorer) or through stored procedures. The criterion is the same: every single DML operation is accomplished by one implicit transaction.

Explicit transactions

The behavior is different when the user declares an explicit transaction into which he puts INSERT, UPDATE, and DELETE statements: all DML operations declared into it (no matter how many they are) will take effect at the time of the final COMMIT; SQL Server doesn’t split DML operations into individual transactions, so they will all be accomplished within a single transaction.

This is shown in the following example:

--Inserting two rows into the dbo.Employees table through an explicit
--transaction
BEGIN TRANSACTION
      INSERT INTO
            dbo.Employees
      VALUES
            (3, 'Barr', 'Adam')
      INSERT INTO
            dbo.Employees
      VALUES
            (4, 'Brown', 'Robert')
COMMIT TRANSACTION

This transaction inserts the two rows with EmployeeID equal to 3 and 4, respectively.

This behavior doesn’t change in the presence of nested transactions: the DML operations contained in the internal transactions are taken over by the outermost transaction, and become effective only when the COMMIT of the latter is executed. In practice, SQL Server treats everything as if it were a single explicit transaction without nesting.

For example:

-- Inserting two rows into the dbo.Employees table using an explicit transaction
-- containing a nested transaction
BEGIN TRANSACTION
      --Nested transaction
      BEGIN TRANSACTION
            --Inserting the first row
            INSERT INTO
                  dbo.Employees
            VALUES
                  (5, 'Meisler', 'Linda')
      COMMIT TRANSACTION
      --Inserting the second row
      INSERT INTO
            dbo.Employees
      VALUES
            (6, 'Corets', 'Eva')
COMMIT TRANSACTION

As you can see, it is up to the outermost transaction to insert the rows (with EmployeeID equal to 5 and 6). This behavior doesn’t change if you include explicit transactions in a stored procedure.

The sys.dm_tran_commit_table view

Every time a transaction operating on a tracked table’s data ends with COMMIT, a new record is added into the internal table sys.syscommittab. Although this table is not directly accessible, the information that it contains is recoverable through the sys.dm_tran_commit_table system view, which is composed of the following fields (the data type is indicated in brackets):
1. commit_ts (bigint): cardinal number (called version, described in the next section) indicating the
order in which each transaction was executed;
2. xdes_id (bigint): transaction id;
3. commit_lbn (bigint): number of the log block which contains the record in which the COMMIT of the
transaction is stored;
4. commit_csn (bigint): the COMMIT sequence number for the transaction at the instance level;
5. commit_time (smalldatetime): UTC date and time, indicating when the transaction’s COMMIT was
executed; to get local date and time, you can use the DATEADD() and DATEDIFF() functions.
The availability of data in this view depends on the retention_period specified for Change Tracking.

The Version: a system to track orderly DML operations (and transactions)

When you enable Change Tracking, SQL Server enables, within the database, a numbering system called version. Specifically, this is a cardinal number that is automatically and incrementally mapped:
– to each transaction that
a. performs at least one DML operation on a tracked table in a database;
b. terms with the COMMIT statement;
– to the rows that are modified by the transaction itself (according to the incorporation criteria seen
in the previous paragraph);
In practice, the version is a counter.

Upon activation of Change Tracking, the version is 0.

When you perform a transaction that matches the two conditions listed above, CT retrieves the value of the current version (i.e. the last value, or 0 if this is the first transaction performed since the activation of CT), increments it by one, and associates it with the transaction itself and the rows changed by the transaction.

The newly assigned value becomes the new current version from which Change Tracking will, when the next change occurs, start to generate the new version that will be assigned to the transaction making the changes (and to the rows involved in the transaction itself).

The current version, then, is nothing more than the total number of transactions that, up to now, have performed DML operations on the tracked tables in the database.

The assignment of the version follows the chronological order in which each transaction is committed (based on COMMIT time, which, as seen before, is saved into the sys.dm_tran_commit_table system view).

All these data are saved into internal tables and are retrievable through the appropriate functions.

Thanks to this mechanism (shown schematically in Figure 1) it is possible to obtain the order in which the DML operations occur, to know exactly what happens in each transaction and, most importantly, by always specifying the version from which to start to transfer data, to establish a start point (and an arrival point) for incremental ETL and synchronization processes.

Example

Using the scripts used above, specifically in the section dedicated to transactions, you can put into practice, in our sample database, the ground covered so far.

The current version of the CTExampleDB database is equal to 0, because since activating Change Tracking, no DML operations have yet been performed on the dbo.Employees table (and dbo.Employees is the only table in this database).

Let’s execute the first script, and see what happens:

--The following script consists of two DML operations
--1) Inserting two rows in the dbo.Employees table
INSERT INTO
      dbo.Employees
VALUES
      (1, 'Diaz', 'Brenda'),
      (2, 'Blythe', 'Michel')
--2) Ooops! There is a mistake! Let's correct the row with Employeeid = 2
UPDATE
      dbo.Employees
SET
      FirstName = 'Michael'
WHERE
      EmployeeID = 2

Both the DML operations respect the requirements for having a version assigned (the COMMIT happens “behind the scenes” since they are implicit transactions).

Therefore:
– the transaction that performs the INSERT statement and the rows having EmployeeID equal to 1 and
2 will have version 1 assigned to it;
– the transaction that performs the UPDATE statement and the row having EmployeeID equal to 2
(previously inserted) will have version 2 assigned to it (the row version has been overwritten,
because, as described in the first article, Change Tracking does not keep a history of the information
about the changes);
– the current version, once the script has been executed, has the value 2.
Now, we execute the second script:

-- Inserting two rows into the dbo.Employees table using an explicit transaction
BEGIN TRANSACTION
      INSERT INTO
            dbo.Employees
      VALUES
            (3, 'Barr', 'Adam')
      INSERT INTO
            dbo.Employees
      VALUES
            (4, 'Brown', 'Robert')
COMMIT TRANSACTION

Since we meet the requirements for applying the version, the value 3 will be assigned to the transaction and to the two inserted rows (having EmployeeID equal to 3 and 4). The value 3 becomes the new current version of the database.

We are going to conclude the series of examples with the last query:

-- Inserting two rows into the dbo.Employees table using an explicit transaction
-- containing a nested transaction
BEGIN TRANSACTION
      --Nested transaction
      BEGIN TRANSACTION
            --Inserting the first row
            INSERT INTO
                  dbo.Employees
            VALUES
                  (5, 'Meisler', 'Linda')
      COMMIT TRANSACTION
      --Inserting the second row
      INSERT INTO
            dbo.Employees
      VALUES
            (6, 'Corets', 'Eva')
COMMIT TRANSACTION

Here too, the correct execution of DML operations on the dbo.Employees table means that the transaction and the two rows having EmployeeID equal to 5 and 6 will be assigned version 4.

Since this is the last operation to be performed, the current version of the database now corresponds to 4.

Querying the sys.dm_tran_commit_table system view, you can observe the information relating to the four transactions executed in our example, including the version (Figure 2).

To obtain information on the changed rows, you must use the CHANGETABLE(CHANGES …) function (described later).

A few considerations about the system for assigning the version

The version is not assigned when:
– DML operations are executed on tables that are not tracked, even if they are in a tracked database;
– DML operations are executed on tracked tables, which, while ending without errors, do not cause changes to the data (for example, when an UPDATE or DELETE is based on a WHERE condition that is not satisfied by any of the rows contained in the table).
Conversely, the version is assigned when:
– a DML operation included in an explicit transaction generates an error, but the transaction ends with
a COMMIT command;
– an explicit transaction performs DML operations on multiple databases which are CT enabled; in this
case, the transaction will be stored in each database with a different version. This version will take
into account the current version of each database when it is assigned (for example, if database A has
its current version equal to 3 and for database B it is equal to 5, the transaction will be stored in
database A with a version of 4, and in database B with a version of 6).
Finally, if you disable and re-enable Change Tracking in the same database, the current version will not restart from 0, but from the last value assigned prior to deactivation.

This peculiarity does not alter, however, the proper functioning of CT.

The functions
SQL Server provides five functions for querying Change Tracking in order to obtain information on the changed records, the various versions, and the context from which DML operations are performed:
– CHANGE_TRACKING_CURRENT_VERSION()
– CHANGE_TRACKING_MIN_VALID_VERSION()
– CHANGETABLE()
– CHANGE_TRACKING_IS_COLUMN_IN_MASK()
– WITH CHANGE_TRACKING_CONTEXT()
To access any of this information, the entities (users, applications, etc.) that use these functions must have the necessary permissions to perform a SELECT statement on, at minimum, the fields making up the primary key of the tracked tables.

CHANGE_TRACKING_CURRENT_VERSION()
The CHANGE_TRACKING_CURRENT_VERSION() function returns the current version of the database (equivalent to the version assigned to the last transaction performed and terminated with the COMMIT statement).

The returned data type is bigint.

If Change Tracking is disabled, the function returns NULL; if it is enabled but no transactions have yet been executed, it returns the value 0.

The syntax is as follows:

CHANGE_TRACKING_CURRENT_VERSION()

In the previous example, the last transaction performed on the CTExampleDB database has received the version number equal to 4. This is the value that we will obtain by calling the function (Figure 3).

The returned value is typically used as a parameter of the CHANGETABLE() function in incremental ETL or data synchronization processes, in order to indicate from which version this function must extract information on the changes needed to update the target database (we will describe this mechanism in detail later, in the use case).

IMPORTANT: this function doesn’t work properly on database snapshots; in fact, if CT is disabled, the function returns NULL, which is correct, while if CT is enabled, the function always returns the value 0, which is obviously incorrect. To deal with this bug, you can do the following:
1) Execute the CHANGE_TRACKING_CURRENT_VERSION() function on the database snapshot;
2) If it returns NULL, CT is disabled; if the function returns 0, CT is active and you can obtain the current version as follows (figure 3 may help you to understand the following query):

SELECT MAX(commit_ts) FROM sys.dm_tran_commit_table

3) If the SELECT returns NULL, the current version is equal to 0 (no changes have been performed yet, so the system view does not contain any transactions); if the SELECT returns a value greater than 0, this will be the current version.

CHANGE_TRACKING_MIN_VALID_VERSION()

When the auto cleanup system (if active) deletes information about changed rows from the internal table, it also removes the version associated with them.

The CHANGE_TRACKING_MIN_VALID_VERSION() function returns the minimum available version for the table that we have specified as a parameter; it indicates, in other words, from which version the information on the changes is still available after each auto cleanup task.

For example, if the auto cleanup removes the information related to versions 1 and 2 from the internal tables of the CTExampleDB database, the function, when applied to the dbo.Employees table, would return the value 3.

If the auto cleanup removes the information for all the existing versions, the function will return the value of the current version (the presence of which is always guaranteed).

The function requires, as parameter, the object_id of the table we are interested in (its data type is int and it can be passed through the OBJECT_ID() function), and will return the version number as bigint data type.

If Change Tracking is disabled or the object_id of the table is incorrect or does not exist, the function returns NULL.

The syntax is the following:

CHANGE_TRACKING_MIN_VALID_VERSION(table object_id)

So, if you want to know the minimum valid version of your dbo.Employees table, you must execute the following query:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Employees'))

The value returned can be used to check if an incremental ETL process or a data synchronization process is correct or if it, conversely, needs to be “reinitialized”, by comparing – before starting a new execution – the minimum valid version with the current version extracted during the previous execution of the process. Through this, you can see if, during the elapsed time between the two executions, some versions have been lost due to an auto cleanup of the internal tables (in this case, the minimum valid version will be greater than the current version).

Beware: when Change Tracking is enabled, certain types of operations made on tracked tables (such as Data Definition Language – DDL) are not executable or may return unexpected results.

This is the case, for example, of the TRUNCATE TABLE command: if used, it causes the loss of all information regarding the tracked table and as a result makes it impossible to recover the minimum valid version; in this situation the function would return the current version, causing errors in the processes (and therefore obliging you to “reinitialize” the mechanism).

You can find the complete list of “critical” commands and their behavior at http://msdn.microsoft.com/en-us/library/bb964713(v=sql.105).aspx web page, in the section “Understanding Change Tracking Overhead”.

CHANGETABLE()
The CHANGETABLE() function returns a list of information on the changed rows of the tracked table.

Depending on the parameters passed, this information may refer to effective changes or simply to the latest version of a row.

The function must be used in the FROM clause and it returns a result set of data (if the requested information is not available, it will be empty), as if it were a real table. Furthermore, the function must always have an alias, otherwise the following error will be returned:
Msg 22104, Level 16, State 1, Line 4
A table returned by the CHANGETABLE function must be aliased.

If you try to apply the function to a table where Change Tracking is not enabled, you will get the following error:
Msg 22105, Level 16, State 1, Line 1
Change tracking is not enabled on table ‘dbo.t’.

CHANGETABLE(CHANGES…)

If CHANGES is specified, the function returns all the information about changes made to the specified table.

The function requires three parameters:
– the CHANGES keyword;
– the table name (not delimited by quotation marks);
– the version from which to extract the information (bigint data type). More precisely, the information
is extracted from the version that immediately follows the version passed as a parameter (i.e. the
specified version + 1) for reasons which will be explained below, in the use case). If you insert NULL
or 0, the function returns information on all the available changes. This parameter’s value must
always be scalar (variables may be used, but not expressions).
The syntax is as follows:

CHANGETABLE(CHANGES table_name, version) AS alias

The returned result set consists of the following fields (data type in brackets):
• SYS_CHANGE_VERSION (bigint): version value associated with the last change made on the row;
• SYS_CHANGE_CREATION_VERSION (bigint): version associated with the last INSERT operation
performed on the row (if no changes have been made after its first entry, the value of this field is
the same as the SYS_CHANGE_VERSION field).
This field contains NULL if:
a) the change is performed on a row which was inserted into the table before the activation of
Change Tracking
b) you request information relating to changes that follow an INSERT operation, and the
returned data refers to an UPDATE or a DELETE operation (visible in the
SYS_CHANGE_OPERATION field).
• SYS_CHANGE_OPERATION (nchar (1)): character indicating the type of change (U = UPDATE, I =
INSERT, D = DELETE);
• SYS_CHANGE_COLUMNS (varbinary (4100)): if, during the enabling of Change Tracking on the table,
you set the TRACK_COLUMNS_UPDATED property to ON (or to True if you used the dialog box), a
value is added to this field for each UPDATE operation; by using the
CHANGE_TRACKING_IS_COLUMN_IN_MASK() function, you can use this value to see which column has
changed. The field contains NULL if the operation performed is an INSERT or a DELETE, if the
TRACK_COLUMNS_UPDATED property is set to OFF, or if the UPDATE simultaneously affects all the
columns that are not part of the primary key. This rule also applies in the case (which can be
misleading) of tables composed of only one field other than the primary key: by changing it, the
value of SYS_CHANGE_COLUMNS will be NULL.
• SYS_CHANGE_CONTEXT (varbinary (128)): if the change took place in a context that has been
specified through the WITH CHANGE_TRACKING_CONTEXT() function, the field will contain the
identifier value of this context;
• : they contain the value of the primary
key for the modified row, and so identify it uniquely.
A few examples
The first example, in Figure 4, shows how to use the function to extract all the available information on the changes that took place in the dbo.Employees table of our sample database:

In the example in Figure 5, we instead request information on the changes since version value 3:

In this example, a primary key value is updated:

--Changing the primary key value of a row (from 6 to 7)
UPDATE
      dbo.Employees
SET
      EmployeeID = 7
WHERE
      EmployeeID = 6

This is a particular specific case: each time you modify a primary key value, Change Tracking does not perform an UPDATE but instead, (and behind the scenes), first deletes the row to be changed (in this example, the one with EmployeeID equal to 6) and then performs an INSERT of the same row with the updated value of the primary key (in this case, equal to 7). Both operations are executed by the same transaction, and they will be assigned the same version (in this case, 5).

If you request information on changes since version 4, you will obtain confirmation of this (figure 6).

The “critical” operations reported for the CHANGE_TRACKING_MIN_VALID_VERSION() function have the same impact on the CHANGETABLE(CHANGES…) function.

For example, Figure 7 shows what happens when you run a TRUNCATE TABLE on a tracked table:

Like a true table, the function can be put into a JOIN with the tracked table, in order to see the new values of each changed row.

An INNER JOIN, however, is not always sufficient, since it does not show any rows that may have been deleted from the tracked table.

You can solve this problem by using an OUTER JOIN.

Let’s see the differences we get after deleting a row from the dbo.Employees table using the following script (after which the value of the current version will be equal to 6):

-- Deleting the row with EmployeeID = 7
DELETE FROM
      dbo.Employees
WHERE
      EmployeeID = 7

In Figures 8 and 9, you can see how, when requesting the information for version 6, the INNER JOIN and the OUTER JOIN will return different result sets.

Does the SYS_CHANGE_OPERATION column work properly?
Many SQL Server users have the feeling that the SYS_CHANGE_OPERATION column does not return the correct value for the UPDATE operation.

Specifically, they have reported that, if they insert a new row and then update it, the SYS_CHANGE_OPERATION column shows the value I (corresponding to the INSERT) instead of the value U (for the UPDATE) that, occurring at a later time, should overwrite the previous value.

This behavior, in fact, is absolutely normal and depends on two factors:
– the application, by Change Tracking, of a “logical reasoning”;
– the version from which you request the information about changes.
Since, in one of our previous examples, we have a case of a consecutive INSERT-UPDATE on the same row (see figure 4, the row with EmployeeID equal to 2), let’s analyze it step by step to see what happens.

Looking at the first script in the section on the criteria for assigning the version, we notice that when the record with EmployeeID equal to 2 was inserted into the table, it received a version value of 1; then, it was immediately updated and got a version value of 2.

Now, trying to request information on all the changes (or, in general, from a version lower than the one in which this row has been inserted), Change Tracking starts to think in this way:

“I received a request for the information on changes from version 1. Recovering the changes for the required range, I notice that the row with EmployeeID equal to 2 has been inserted first and then modified, in two operations. Since the request refers to a previous (or at least equal) version to the one in which this row was inserted, that means that the client (user, application, etc.) does not yet have the record.

This situation makes me wonder if it makes sense to tell the client to enter a new row and to change it immediately, performing several operations. No, that doesn’t make sense! Does it make sense to show a new row for an UPDATE operation as a first change, without previously reporting an INSERT operation first? No! Then, I will group all the changes in a single row and return the information on the changes, showing the new record as an INSERT operation that contains the updated values for the latest UPDATE! In this way, the client will save time and resources, since he will only have to perform an INSERT of the newest and updated data.”

In fact, by performing an OUTER JOIN between the table and the function, you can see that this is exactly what happened: the row already contains the value ‘Michael’ in the FirstName field and not ‘Michel’, as erroneously inserted in the operation corresponding to version 1 (the INSERT, Figure 10).

If the INSERT operation were followed by several UPDATEs (for example: INSERT, UPDATE 1, UPDATE n), the CT would respond in the same way, again returning a record marked as an INSERT but updated to the latest UPDATE (the nth)

If you require information on the changes for a row starting from a version that follows the INSERT but precedes or equals the UPDATE (in this case, version 2), Change Tracking understands that the record is already present in the client but it still contains the old values (in this case, those dating back to the INSERT associated with version 1, with the FirstName field containing the value ‘Michel’). Therefore, it returns information indicating that the row should simply be updated. In this case, the SYS_CHANGE_OPERATION field will be filled with the ‘U’ (Figure 11). Obviously, if the row has been updated several times, the resulting values will refer to the latest UPDATE operation.

This explains why users sometimes see the ‘I’ value and sometimes the ‘U’, and think that this is a bug.

Since this mechanism has been implemented primarily to be executed automatically (within processes) and not manually (through a query), it’s not surprising that it may not be very transparent to the users. However, if your process has been implemented correctly, this mechanism works smoothly (it will never happen, for example, that Change Tracking on its own initiative will return information about version 2, indicating an UPDATE statement, without first having been provided with the version corresponding to the INSERT operation); any possible anomalies of this type will be ascribed solely to implementation errors (for example, if a process has been initialized to require version 2 instead of version 1 or if the retention period has been set too short and you have lost the information about the changes that preceded the UPDATE operation).

CHANGETABLE(VERSION…)
If VERSION is specified, the function returns, for the specified row, the version corresponding to the last change executed on it.

The function requires four parameters:
– the VERSION keyword
– the name of the table (not delimited by quotation marks) containing the relevant row;
– the fields that comprise the primary key of the tracked table (in square brackets); if a primary key
consists of several fields, you must list all of them, separated by a comma;
– the value of the primary key of the row to be displayed (in square brackets); if the primary key
consists of several fields, the values must be entered following the same order as the fields declared
in the previous parameter; if you enter the name of the fields that comprise the primary key, instead
of their values, the function returns the latest modified record (the chronological order is based on
the following criteria: row with higher version and, if more than one row has the same version, the
one that has been changed last by the transaction). It’s also possible to pass the value of the primary
key through variables or expressions.
If the record has been removed from the tracked table, or the specified primary key value does not exist, the function returns an empty result set.

The syntax is the following:

CHANGETABLE(VERSION table_name, (key_column_name[,…n]),(key_value)) AS alias

The result set consists of the following fields (the data type is indicated in brackets):
• SYS_CHANGE_VERSION (bigint): version assigned to the last change on the row; the value of this
field is NULL if: the auto cleanup has already removed the information on the row at your request
(for example, when the value 4 has been assigned to the latest update on the row, all the
information up to this version or a higher will be deleted); or if the row has not been involved in
changes since the activation of Change Tracking;
• SYS_CHANGE_CONTEXT (varbinary (128)): if the change took place in a context declared through
the WITH CHANGE_TRACKING_CONTEXT() function, the field contains the identifier value of this
context;
• : they contain the value of the primary
key of the modified row to identify it uniquely.
A few examples
Figure 12 illustrates the operation of the CHANGETABLE(VERSION…) function. In the example we want to know the version of the last change for the row with id equal to 5.

Sometimes, there may be a need to display the version for more rows simultaneously; this problem can be solved with a CROSS APPLY between the tracked table and the function.

To accomplish this, you need to enter the name of the field (or fields) that comprise the primary key of the tracked table (Figure 13) in the last parameter, instead of indicating the value of the primary key.

CHANGE_TRACKING_IS_COLUMN_IN_MASK()

As explained above, if, when activating Change Tracking on a table, you set the TRACK_COLUMNS_UPDATED property to ON, CT will add a value into the SYS_CHANGE_COLUMNS field, which is visible using the CHANGETABLE(CHANGES…) function every time you perform an UPDATE operation.

The CHANGE_TRACKING_IS_COLUMN_IN_MASK() function allows you see this value, so you will know which fields of the tracked table were changed by the UPDATE operation.

To obtain this, it’s necessary to apply the function to each individual field for which you wish to obtain this information. The function requires two parameters:
– Id of the column, recoverable through the COLUMNPROPERTY() function (this function requires, in
turn, three parameters: object_id of the tracked table, column name, and the property to be
returned for the column, in this case ‘ColumnID’);
– the SYS_CHANGE_COLUMNS field (varbinary (128)). This implies that the
CHANGE_TRACKING_IS_COLUMN_IN_MASK() and CHANGETABLE(CHANGES…) functions must be used
together, with the latter providing the data that former needs to perform its task.
The returned value is a bit:
– 0, if the column of the tracked table has not been changed by the UPDATE
– 1, if the column of the tracked table has been changed by the UPDATE
The function does not check the correctness of the column_id parameter: if the value is incorrect or does not exist, the function simply returns 0.

The behavior for the second parameter is slightly different: if you pass a value that does not exist (for example, a static value) the function returns 0; if you pass an incorrect value (for example, a different data type), it returns an error message; if the value of the second parameter is NULL, the function returns 1 for all the fields.

The syntax is as follows:

CHANGE_TRACKING_IS_COLUMN_IN_MASK(column_id, SYS_CHANGE_COLUMNS)

With the COLUMNPROPERTY() function, it becomes:

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(table_name), column_name, ‘ColumnId’), SYS_CHANGE_COLUMNS)

In the example scenario, there is an UPDATE operation on the row with EmployeeID equal to 2. Applying this function to the LastName and FirstName fields of the dbo.Employees table and extracting the information for this DML operation, results in the function returning the value 1 for the FirstName column (as shown in Figure 14).

In fact, looking back over the script that performs this operation (in the section covering transactions) you will notice that the UPDATE only acts on the value of this field (from ‘Michel’ to ‘Michael’).

WITH_CHANGE_TRACKING_CONTEXT()

The WITH CHANGE_TRACKING_CONTEXT() function allows you to “mark”, with a sort of label, the DML operations performed on a tracked table. This makes it possible to distinguish the various contexts from which these operations are performed (for example, to recognize different applications).

The function accepts a value as varbinary(128) data type, static or through a variable, but does not accept NULL.

This value will be added, by the function itself, into the SYS_CHANGE_CONTEXT column of the result set returned by the CHANGETABLE(CHANGES…) function.

The syntax is as follows:

WITH CHANGE_TRACKING_CONTEXT (context)

In the following example, a new row has been inserted into the dbo.Employees table, and the context from which the transaction is to take place has been specified (passed through a variable):

-- Declaring the context and assigning it to a variable
DECLARE     @context    varbinary(128);
SET         @context = CAST('SQLServerManagementStudio' AS varbinary(128));

-- Performing an INSERT operation from the specified context
WITH CHANGE_TRACKING_CONTEXT (@context)
      INSERT INTO
            dbo.Employees
      VALUES
            (6, 'Davis', 'Megan');

If you request the information for this change through the CHANGETABLE(CHANGES…) function, you will see the value of the context, in the varbinary (128) format, in the SYS_CHANGE_CONTEXT column (as shown in Figure 15). The SELECT statement is also a CAST through which you can retrieve and display the original name given to the context. In the SELECT statement there is also a CAST operation to retrieve and display the original name given to the context.

Conclusions

In this article, we discussed in detail the internal mechanisms adopted by Change Tracking to assign the version to each DML operation, focusing specifically on transactions. We then described, through several examples, the five functions provided by SQL Server for retrieving information about the changes performed on the tracked tables.