Starting with SQL Server Compact 3.5 Service Pack 2 (SP2), SQL Server Compact exposes an interface to its change tracking infrastructure. After tracking is enabled on a table in a SQL Server Compact database, changes to the table -- that is, inserts, updates, and deletes -- are tracked by SQL Server Compact. This is also known as change tracking. You can use the data provided by change tracking to build a variety of powerful applications. For example, change tracking data can be used to build applications that provide sync services for Occasionally Connected Systems (OCS) in which devices or computers need to connect and sync data with a server or with other peers on a network.

Broadly speaking, SQL Server Compact implements change tracking by:

  • Adding three system columns to user tables on which tracking is enabled.

  • Creating three system tables for storing tracking specific metadata.

  • Storing tracking metadata in the system columns that are added to a tracked user table.

  • Storing tracking metadata in the system tables.

  • Exposing control of the tracking infrastructure to the developer through new classes and additions to existing classes in the System.Data.SqlServerCe namespace.

All tables that begin with "__sys" are system tables. These tables are created and maintained by the SQL Server Compact runtime. Applications cannot directly change or delete the data in a system table. Some methods in the SqlCeChangeTracking class provide limited ability to work with the system tables. These methods are noted, where appropriate, in following sections.

The remaining sections in this topic provide information to help you develop applications built to use change tracking.

Transaction Sequence Numbers

SQL Server Compact uses two numbers to identify the sequence in which transactions (implicit or explicit) that insert, update or delete data from a table or a set of tables begin and commit.

  • The Begin Sequence Number (BSN) is a monotonically increasing counter that uniquely identifies the sequence in which transactions begin on the database. It is assigned by SQL Server Compact when a transaction begins and is incremented for the next transaction to begin.

  • The Commit Sequence Number (CSN) is a monotonically increasing counter that uniquely identifies the sequence in which transactions commit on the database. It is assigned by the database when a transaction commits and is incremented for the next transaction to commit.

The BSN and CSN uniquely identify the sequence in which a specific transaction begins and commits relative to other transactions that are operating on the SQL Server Compact database.

Columns Added to Tracked Tables (In-row Metadata)

When tracking is enabled on a user table, SQL Server Compact adds 3 columns to the table. It stores metadata about changes that are applied to each row in two of these columns; the third column can be optionally used to store application-specific context information. When tracking is disabled, the columns are deleted.

The following table describes the columns that are added to hold this in-row metadata.

Column Name

Data Type

Description

__sysChangeTxBsn

binary(8)

The BSN of the last transaction to update the row.

__sysInsertTxBsn

binary(8)

The BSN of the transaction that inserted the row.

__sysTrackingContext

uniqueidentifier

Application-specific context information. This column is set according to the TrackingContext property of the last transaction (SqlCeTransaction) to modify the row. A developer can use this property to hold information specific to the application. For example, to identify the user or the system that is changing the data.

On deletes, in addition to deleting the row from the table, SQL Server Compact adds a row that contains information about the deleted row to the tombstone table.

System Tables

The following system tables are used in change tracking.

The Tombstone Table

When a row is deleted from a table on which tracking is enabled, a record is created for it in the tombstone table (__sysOCSDeletedRows). There is a single tombstone table in the database, and it contains information about rows deleted from any of the tracked tables in the database. The following table describes the columns of the tombstone table.

Column Name

Data Type

Description

__sysTN

nvarchar(128)

The name of the user table from which the row was deleted.

__sysDeleteTxBsn

binary(8)

The BSN of the transaction that deleted the row.

__sysInsertTxBsn

binary(8)

The BSN of the transaction that inserted the row into the user table.

__sysRK

varbinary(8000)

The row key. This is either the primary key of the user table or a column of data type uniqueidentifier in the user table that has had the ROWGUIDCOL attribute applied (ROWGUID column). The row key uniquely identifies the user table row to the tracking system. The row key is stored in a serialized format in the tombstone table.

__sysDeletedTime

datetime

The time at which the row was deleted from the user table.

The row key can be composed of a single column from the user table in the case of a single-column primary key or the ROWGUID column, or it can contain multiple columns with different data types if the primary key is composed of multiple columns. For this reason, the row key is stored in a serialized format in the tombstone table. You can use the PackTombstoneKey and the UnpackTombstoneKey methods to convert to and from this serialized format. You set the type of key on the user table (primary key or ROWGUID column) used by the tracking system by passing one of the values of the TrackingKeyType enumeration as a parameter to the EnableTracking method to enable tracking on the table.

Over time, the tombstone table can grow quite large. You can use the PurgeTombstoneTableData method to delete rows from the table. Rows can be purged from the tombstone table based on the number of days that have passed since they were deleted from the user table (a time based purge), or based on the CSN of the transaction that deleted them (a CSN based purge).

The Transaction Commit Sequence Table

Transactions can commit in a different order than that in which they were begun (out-of-sequence commit). The Commit Sequence Table (__sysTxCommitSequence) tracks these out-of-sequence transactions. A row is inserted into this table at the time a transaction commits if that transaction is doing an out-of-sequence commit. The following table describes the columns of the Transaction Commit Sequence table.

Column Name

Data Type

Description

__sysTxBsn

binary(8)

The BSN of the transaction.

__sysTxCsn

binary(8)

The CSN of the transaction.

__sysCommitTime

datetime

The time at which the transaction committed.

A row is added to the Transaction Commit Sequence table only for transactions that commit out-of-sequence.

The following example shows how transactions are inserted into the Transaction Commit Sequence table.

Consider five transactions T1, T2, T3, T4, and T5.

For these transactions the following holds true:

The transaction begin sequence (start order) is: T1, T2, T3, T4, T5.

The transaction commit sequence (commit order) is: T1, T3, T4, T2, T5.

Assume that both the CSN and the BSN for this group of transactions start at 1. The following table describes the CSN and BSN for each transaction and whether or not an entry would be made in the Transaction Commit Sequence table for the transaction.

T1

T2

T3

T4

T5

BSN

1

2

3

4

5

CSN

1

4

2

3

5

Row inserted into __sysTxCommitSequence?

No

Yes

Yes

Yes

No

Over time, the size of the Transaction Commit Sequence table can grow quite large. You can use the PurgeTransactionSequenceData method to delete rows from this system table. Rows can be purged from the table based on the number of days that have passed since they were added (a time based purge), or based on the CSN of the transaction (a CSN based purge).

Enumerating Changes to a Table

The queries in this section provide examples of how to enumerate changes to a table. The name of the table for which to enumerate changes is passed in the parameter {0} in each query.

The following parameters are also defined:

  • EBSN: Enumeration Begin Sequence Number, the hardened transaction BSN just before the sync starts enumerating the changes. This value is used to avoid picking up any changes made to the table while enumerating. 

  • ECSN: Enumeration Commit Sequence Number, the hardened transaction CSN just before the sync starts enumerating the changes. This value is used to avoid picking up any changes made to the table while enumerating. 

  • LBSN: Last Sync Begin Sequence Number, the EBSN of the last successful sync.

  • LCSN: Last Sync Commit Sequence Number, the ECSN of the last successful sync.

An upload can commit independently from a download. LBSN is set to EBSN and LCSN is set to ECSN after the upload is complete and acknowledged.

The following query enumerates inserts:

        private const string cmdEnumerateInserts =
            " select ut.* from " + 
            "   (select ut0.* from {0} as ut0 where " + 
            "     ut0.__sysInsertTxBsn IS NOT NULL AND ut0.__sysChangeTxBsn IS NOT NULL AND ut0.__sysChangeTxBsn >= @LBSN " + 
            "   ) as ut " + 
            " LEFT OUTER JOIN " + 
            "   (select txcs0.* from __sysTxCommitSequence as txcs0) as txcs " + 
            " ON (ut.__sysInsertTxBsn = txcs.__sysTxBsn) WHERE " +
            "      (txcs.__sysTxBsn IS NOT NULL and txcs.__sysTxCsn >= @LCSN AND txcs.__sysTxCsn < @ECSN) OR  " + 
            "      (txcs.__sysTxBsn IS NULL AND ut.__sysInsertTxBsn >= @LCSN AND ut.__sysInsertTxBsn < @ECSN)";

The following query enumerates updates:

        private const string cmdEnumerateUpdates =
            " SELECT ut.* FROM " +  
            "   (select ut0.* from {0} as ut0 where " + 
            "     (ut0.__sysChangeTxBsn IS NOT NULL AND ut0.__sysChangeTxBsn >= @LBSN) " + 
            "   ) as ut " + 
            " LEFT OUTER JOIN" + 
            "   (select txcs0.* from __sysTxCommitSequence as txcs0) as txcs " + 
            " ON ut.__sysChangeTxBsn = txcs.__sysTxBsn OR txcs.__sysTxBsn IS NULL WHERE " + 
            "   (txcs.__sysTxBsn IS NOT NULL AND txcs.__sysTxCsn >= @LCSN AND txcs.__sysTxCsn < @ECSN) OR " + 
            "   (txcs.__sysTxBsn IS NULL AND ut.__sysChangeTxBsn >= @LCSN AND ut.__sysChangeTxBsn < @ECSN) ";

The following query enumerates deletes:

        private const string cmdEnumerateDeletes =
            " SELECT ut.__sysRK  FROM " +
            "   (select ut0.* from __sysOcsDeletedRows as ut0 where " +
            "     (ut0.__sysInsertTxCsn IS NULL OR ut0.__sysInsertTxCsn < @LCSN) and " + // Filter INSERT + DELETE 
            "     ut0.__sysTName = \'{0}\'" + 
            "   ) AS ut " +
            " LEFT OUTER JOIN  " +
            "   (select txcs0.* from __sysTxCommitSequence as txcs0) as txcs " + 
            " ON ut.__sysDeleteTxBsn = txcs.__sysTxBsn OR txcs.__sysTxBsn IS NULL WHERE " + 
            "   (txcs.__sysTxBsn IS NOT NULL AND txcs.__sysTxCsn >= @LCSN AND txcs.__sysTxCsn < @ECSN) OR " + 
            "   (txcs.__sysTxBsn IS NULL AND ut.__sysDeleteTxBsn >= @LCSN AND ut.__sysDeleteTxBsn < @ECSN) ";

Controlling Change Tracking Programmatically

You can use new classes and also new methods and properties added to existing classes in the System.Data.SqlServerCe namespace to configure and control change tracking on SQL Server Compact databases. The following table gives a brief overview of these new artifacts.

Artifact

Definition

The PurgeType enumeration

Contains values that are used to specify whether purges of tracking data from the Tombstone table or the Transaction Commit Sequence table are time based or CSN based.

The SqlCeChangeTracking class

The main class used in change tracking. Provides methods that configure, enable, and disable change tracking on tables in SQL Server Compact databases. Methods are also provided to purge tracking data from the Tombstone table and the Transaction Commit Sequence table; and to upgrade a SQL Server Compact 3.5 SP2 database for use by Microsoft Sync Framework 1.0 SP1 and later versions of the Sync Framework.

The SqlCeTransaction class

Two new properties are added. The CurrentTransactionBsn property returns the BSN assigned by the SQL Server Compact to the transaction. The TrackingContext property is provided to hold application specific information. This information can be defined by the developer. When a user table, on which tracking is enabled, is modified, the __sysTrackingContext column in the modified row is set to the value of the TrackingContext property.

The TrackingKeyType enumeration

Contains values that specify the type of key to use to uniquely identify rows in a user table to the change tracking infrastructure. This can be either the primary key defined for the table or a column of type uniqueidentifier that has the ROWGUIDCOL attribute applied.

The TrackingOptions enumeration

Contains values that specify the types of operations to track on the user table. Inserts, updates, deletes, or a combination of any of these operations may be tracked on a table.

Reference

System.Data.SqlServerCe

SqlCeChangeTracking