locked
change tracking for particular columns and not for whole table RRS feed

  • Question

  • Hi,

    I am using sql server 2008. I am aware that we can enable change tracking for a table using below sample script,

    ALTER TABLE dbo.test
        ENABLE CHANGE_TRACKING
            WITH(
                TRACK_COLUMNS_UPDATED = ON  
            );
    GO

    But I want to enable change tracking for only few columns in my table . Say my table has 40 columns and I want to enable change tracking for only 3 columns in that. Is that possible ? please let me know the script to check it.

    I understand that we can use:

    [CountChanged?] =
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('test'),
    'Count', 'ColumnId'), SYS_CHANGE_COLUMNS),

    but, if we can do change Tracking for the selected columns, this will reduce overhead to some extend.

    Let me know your thoughts.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Monday, April 29, 2013 2:38 PM

Answers

All replies

  • That's correct. That feature is not available.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 29, 2013 10:11 PM
  • Hi Iam_Rakesh,

    You can try SQL Server change data capture feature in only a subset of columns need to be tracked. For more detail information, please refer to the following link:

    About Change Data Capture (SQL Server)
    http://msdn.microsoft.com/en-us/library/cc645937.aspx

    sys.sp_cdc_enable_table (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/bb522475.aspx


    Allen Li
    TechNet Community Support

    Wednesday, May 1, 2013 9:40 AM
  • I we forget the fact that CDC is only available in Enterprise Edition, it's still a more heavy-duty solution even if you only track three colunms with CDC and track all column changes with Change Tracking, since CDC logs all the values and Change Tracking only that there was a change.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 1, 2013 10:28 AM