locked
Rowversion Column vs Others RRS feed

  • Question

  • Hi,

    I am using rowversion datatype in one of my table. My purpose is to find the update in a record and if there is an update I have to copy the record into another table. I want to know if using rowversion datatype is the best approach than using checksum or datetime to find the update in the record.

    I didnt find much people using this datatype so as to find the 'Delta Change', they would rather go with the Checksum or DateTime. Is it a best approach to use rowversion datatype than other method to find Delta Change in a table ?

    Thanks,

    Saturday, July 31, 2010 11:09 PM

Answers

  • > But we are using Standard Edition and I believe it does not have the CDC feature in it.  So, I guess we have to go along with rowversion. Any suggestions ?

    You are mixing two features. Change Data Capture is indeed only in Enterprise. But I suggested Change Tracking and this feature is available in all editions of SQL 2008. (And CDC would be too heavy artillery for this anyway.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by nadirsql Monday, August 2, 2010 7:54 PM
    Monday, August 2, 2010 7:50 PM
  • Yes, but you need to save the rowversion column with the PK column into a snapshot table for later comparison.

    BOL 2008: "You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time."

    I wrote an article on using rowversion (formerly timestamp) for optimistic concurrency control:

    http://www.sqlusa.com/articles2005/rowversion/


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Marked as answer by nadirsql Sunday, August 1, 2010 3:28 AM
    Saturday, July 31, 2010 11:47 PM
  • Kalman has rightly pointed out. Please go with rowversion.

    • Please note that rowversion contain the data of  binary(8) / varbinary(8) value not date time values.
    • If there is a need to know when the last update happended on a row, datetime / datetime2 datatype should be used not rowversion.
    • rowversion can be used to implement optimistic concurrency.

    From BOL 2008;

    The rowversion data type is just an incrementing number and does not preserve a date or a time.
    To record a date or time, use a datetime2 data type.

    You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read. To return the current rowversion value for a database, use @@DBTS.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by nadirsql Sunday, August 1, 2010 3:28 AM
    Sunday, August 1, 2010 2:35 AM

All replies

  • Yes, but you need to save the rowversion column with the PK column into a snapshot table for later comparison.

    BOL 2008: "You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time."

    I wrote an article on using rowversion (formerly timestamp) for optimistic concurrency control:

    http://www.sqlusa.com/articles2005/rowversion/


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Marked as answer by nadirsql Sunday, August 1, 2010 3:28 AM
    Saturday, July 31, 2010 11:47 PM
  • Kalman has rightly pointed out. Please go with rowversion.

    • Please note that rowversion contain the data of  binary(8) / varbinary(8) value not date time values.
    • If there is a need to know when the last update happended on a row, datetime / datetime2 datatype should be used not rowversion.
    • rowversion can be used to implement optimistic concurrency.

    From BOL 2008;

    The rowversion data type is just an incrementing number and does not preserve a date or a time.
    To record a date or time, use a datetime2 data type.

    You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read. To return the current rowversion value for a database, use @@DBTS.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by nadirsql Sunday, August 1, 2010 3:28 AM
    Sunday, August 1, 2010 2:35 AM
  • If you are on SQL 2008, an alternative is to use Change Tracking to find changed and updated rows. This is a superior solution over timestamp(*) columns. Timestamp columns work well as long as the update rate is moderate, but if there are plenty of concurrent updates, it can become very difficult to avoid that you miss changes.

    Also, rather than using @@DBTS, use min_active_rowversion(), this is a little safer.

    (*) Yeah, "timestamp" is deprecated, but the way Microsoft have painted themselves into a corner, that name is not going away any time soon.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, August 1, 2010 9:49 AM
  • Thanks Erland,

    But we are using Standard Edition and I believe it does not have the CDC feature in it.  So, I guess we have to go along with rowversion. Any suggestions ?

    Monday, August 2, 2010 4:35 PM
  • > But we are using Standard Edition and I believe it does not have the CDC feature in it.  So, I guess we have to go along with rowversion. Any suggestions ?

    You are mixing two features. Change Data Capture is indeed only in Enterprise. But I suggested Change Tracking and this feature is available in all editions of SQL 2008. (And CDC would be too heavy artillery for this anyway.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by nadirsql Monday, August 2, 2010 7:54 PM
    Monday, August 2, 2010 7:50 PM