Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Experts opinion needed on our UPDATE design

Answered Experts opinion needed on our UPDATE design

  • Tuesday, February 12, 2013 2:48 AM
     
     
    Sign In to Vote
    0

    We have a ODS system where data from OLTP is synced using delta captured thru CDC job.  

    In the process we check for record existence using primary key based on which we perform update or insert. Source and target schema are identical.

    While updating we are updating all columns of the row irrespective of whether the value was changed or not in each column, (as we felt it will be a cumbersome task to check for each column change and generate a dynamic update stmt). So essentially we are overwriting the record with same values 95% of time.

    I need a experts opinion on impact of our update design on the system - log, Indexes, performance etc. Plus any suggestion for better approach.

    SQL 2008 R2 version.


    Thanks

    Mahesh


     

All Replies

  • Tuesday, February 12, 2013 5:48 AM
     
     Answered
    Sign In to Vote
    1

    When an update happens to a column that do not change the column value, this is called 'Non-updating updates'. The behaviour and performance vary slightly for clustered index tables and heap tables (with non clustered indexes).

    For non-updating updates avoid extra logging and page flushing if the updating column is not part of clustering key. If the update is a clustering key, then this will be logged. For more details on this Paul White has an interesting post on this subject:

    The Impact of Non-Updating Updates

    The following article describe new optomization introduced from SQL Server 2005:

    Non updating updates

    Krishnakumar S