locked
Column level Tracking vs Row level Tracking RRS feed

  • Question

  • Hello,

    I have a few questions:

    1. I know what the difference is but I really don't understand why the default is Row Level tracking. i've seen this question before on this forum but not answered
    2. Since we want to use replication for low bandwith synchronization i guess the best choise for us is to use Column tacking?
    3. I read on a previous thread that column level tracking would reduce the amount of data sent by 60% (official MS answer). does this mean my sync will be about that much faster as well?

    thanks for reading!
    Andy
    Friday, June 12, 2009 8:44 AM

Answers

  • that depends on your workload.


    Column level tracking has advantages when syncing updates which occur on individual columns on a table. If your workload is characterized by inserts and deletes there will be no change. If your workload is characterized by every column in a table being updated, there will be no changed. If your work load is characterized by some of the columns being updated with the rest being left alone there may be some advantages to using column level tracking.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Aussie Monday, June 15, 2009 5:33 PM
    Monday, June 15, 2009 12:35 PM
    Answerer

All replies

  • 1) Row level tracking is faster.
    2) Yes
    3) Not necessarily. There is overhead in a sync without any changes.

    Please refer to :

    http://msdn.microsoft.com/en-us/library/aa179429(SQL.80).aspx

    For more info.

    It all really depends on the amount of changes are updates, are changes to individual columns as opposed to rows (for example if your update statements update all columns in a row, row level will be faster), and are in conflict.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, June 12, 2009 10:19 AM
    Answerer
  • Hi,

    Thanks for the answer but i'm confused by it :)

    You say: Row level tracking is faster... but for low bandwith connections Column level tracking is better because  there is less data transfer.. isn't this equal to faster syncing as well?

    Thanks for reading,
    Andy
    Monday, June 15, 2009 9:33 AM
  • that depends on your workload.


    Column level tracking has advantages when syncing updates which occur on individual columns on a table. If your workload is characterized by inserts and deletes there will be no change. If your workload is characterized by every column in a table being updated, there will be no changed. If your work load is characterized by some of the columns being updated with the rest being left alone there may be some advantages to using column level tracking.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Aussie Monday, June 15, 2009 5:33 PM
    Monday, June 15, 2009 12:35 PM
    Answerer