column-level change tracking

Recently, I worked a lot with change tracking that’s new feature in Sql Server 2008. Now I just wanna share somethings about select changes at column level. Later, I will publish a very complete article about change tracking.


-- this is our sample table with dbo schema

create table test_changetracking
    (
      id int primary key
    , col1 varchar(200)
    , col2 varchar(200)
    ) ;

-- enable change tracking on database
-- enable change tracking on this sample table with (track_columns_updated = on)
-- insert, delete and update data
-- now select column change tracking

select tct.id
     , tct.sys_change_operation as [change operation]
     , tct.sys_change_columns as [change columns]
     , tct.sys_change_context as [change context]
     , change_tracking_is_column_in_mask(columnproperty(object_id('dbo.test_changetracking'), 'col1', 'columnid'), ct.sys_change_columns) as [col1 changes]
     , change_tracking_is_column_in_mask(columnproperty(object_id('dbo.test_changetracking'), 'col2', 'columnid'), ct.sys_change_columns) as [col2 changes]
     from 
         changetable(changes dbo.test_changetracking, null) as tct

Advertisements
This entry was posted in Joy of T-SQL. Bookmark the permalink.

Leave a comment

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