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

     , 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]
         changetable(changes dbo.test_changetracking, null) as tct


Leave a comment

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s