SQL Server Performance

Is there a way to know which column(s) is changed during an update

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Jun 5, 2009.

  1. WingSzeto Member

    When an update statement occurs, is there a quick way using internal SQL tracking method if any (such as dynamic view ) to find out what column(s) is changed. I can write a trigger or some sql code to compare the before and after, but it can save me time if there is some internal data SQL 2008 is collecting for it already.
    W
  2. moh_hassan20 New Member

    Change tracking is a built-in solution in SQL Server 2008 that enables storing and retrieving the changes to data in a database and access information that is related to the changes.

    You configure database , and then configure the table for change tracking , track columns update
    You need not to create triggers for that purpose.
    You can use CHANGETABLE function to query for change information
    simply:
    ALTER DATABASE mydb
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)


    ALTER TABLE mytable
    ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON)

    For more details see BOL
  3. WingSzeto Member

    Thank for the reply. I am aware of that feature. I forgot to mention that we are using SQL 2008 standard version, which the change tracking is not available.
  4. moh_hassan20 New Member

    SQL Server 2008 introduces two change tracking Technologies :
    • change tracking (in all editions including sql express)
    • change data capture CDC (Enterprise , development editions)
    .
    Both features track data that has changed on level of row (as well as use the insert, update, or delete operations to track exactly how the data was changed)

    Change data capture CDC uses an asynchronous mechanism that tracks all the changes that occur to a table or a defined set of columns of the table, including the column values themselves.
    you can use change tracking in the standard ed. , and you can implement (else you get Enterprise[;)] ) your own code to compare at the level of columns.

Share This Page