SQL 2008: Change Tracking Overview

Change Tracking Overview

http://msdn.microsoft.com/en-us/library/bb933875(v=sql.105).aspx –Overview of change tracking

http://msdn.microsoft.com/en-us/library/bb964713(v=sql.105).aspx –Configuring change tracking in database and table level

http://msdn.microsoft.com/en-us/library/bb934145(v=sql.105).aspx –Query change tracking information using changetable function

ALTER DATABASE Test

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 1 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE IsolationTests

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON);

SELECT * FROM sys.change_tracking_databases

select * FROM sys.change_tracking_tables

select object_name(2137058649)

–SELECT * INTO IsolationTestBackup FROM IsolationTests

select * FROM IsolationTests

select * FROM IsolationTestBackup

update IsolationTests

SET Col1 = 4 WHERE Id=2

–delete from IsolationTestBackup WHERE id = 8 OR id = 9

SELECT e.Id, e.Col1, e.Col2, e.Col3,

c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT

FROM IsolationTests AS e

CROSS APPLY CHANGETABLE

(VERSION IsolationTests,(ID),(e.Id)) AS c;

DECLARE @last_sync_version bigint;

–SET @last_sync_version = <value obtained from query>;

SELECT id

SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,

SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT

FROM CHANGETABLE (CHANGES IsolationTests, @last_sync_version) AS C;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s