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.

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.

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.

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.

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.

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.

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.

Reference

System.Data.SqlServerCe

SqlCeChangeTracking