4

I use SQL Server 2012 Standard edition, and I activated Change Tracking function on a table.

When I list changes on a table with the CHANGETABLE function, I have a SYS_CHANGE_COLUMNS property with binary data 

0x0000000045000000460000004700000048000000

How do I know which columns have changed ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthony Brenelière
  • 60,646
  • 14
  • 46
  • 58

1 Answers1

4

Because the column is a bitmask made up of the column IDs of all the columns which were changed, it's difficult to know what it's made up of. In fact, MSDN says not to interrogate SYS_CHANGE_COLUMNS directly here: https://msdn.microsoft.com/en-us/library/bb934145.aspx

This binary value should not be interpreted directly.

However, when you are detecting changes for notification purposes, usually the notification consumer has a good idea of which columns they are interested in changing.

For this use-case, use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function.

-- Get the column ID of my column
declare @MyColumnId int
set @MyColumnId = columnproperty(object_id('MyTable'), 'MyColumn', 'ColumnId')

-- Check if it's changed
declare @MyColumnHasChanged bit
set @MyColumnHasChanged = CHANGE_TRACKING_IS_COLUMN_IN_MASK (MyColumnId, @change_columns_bitmask);

If CHANGE_TRACKING_IS_COLUMN_IN_MASK tell me if a column has changed, how can I write a script that tell me which columns have changed ? I have around 50 attributes for each table.

I'm afraid you'll need to loop through all of the columns you may be interested in... If this is too restrictive, you may have to use another change-notification approach, like Change Data Capture (CDC), or Triggers

tom redfern
  • 30,562
  • 14
  • 91
  • 126
  • If CHANGE_TRACKING_IS_COLUMN_IN_MASK tell me if a column has changed, how can I write a script that tell me which columns have changed ? I have around 50 attributes for each table. – Anthony Brenelière Nov 21 '16 at 09:41