locked
Should all my tables have a TIMESTAMP (ROWVERSION) column? RRS feed

  • Question

  • I'm designing a SQL Sever database whose primary front end will be Access, though I might have other front ends (Excel, SharePoint, custom apps).  I read in a Microsoft document that having a TIMESTAMP (aka ROWVERSION) column can improve performance on the Access front end by helping Access determine when records have changed.

    My question then - should I have a TIMESTAMP column in ALL my tables?    Is there any drawback to having the column in all the tables?  The only drawback I've seen is people thinking it is a real time stamp and asking me questions on how to convert it to a date.

    Thanks!

    CGN

    • Moved by Tom PhillipsEditor Monday, June 20, 2011 1:57 PM Database Design question (From:SQL Server Database Engine)
    Friday, June 17, 2011 2:21 PM

Answers

  • Yes, your accessement regarding timestamp is correct and you should have it in all tabels in SQL especially if you have Access front end.

    This will eliminate a possible problem with Access that another user has changed or modified a row since you have opened the record.

    >>Is there any drawback to having the column in all the tables? 

    storage size for timestamp (where timestamp is binary and not date as you have said) is about 8 bytes per record so you are storing some uncessary data but it will speed up your updates and deletes when you have Access front end. 


    • Marked as answer by carlosn1313 Monday, June 20, 2011 2:00 PM
    Friday, June 17, 2011 2:43 PM

All replies

  • Yes, your accessement regarding timestamp is correct and you should have it in all tabels in SQL especially if you have Access front end.

    This will eliminate a possible problem with Access that another user has changed or modified a row since you have opened the record.

    >>Is there any drawback to having the column in all the tables? 

    storage size for timestamp (where timestamp is binary and not date as you have said) is about 8 bytes per record so you are storing some uncessary data but it will speed up your updates and deletes when you have Access front end. 


    • Marked as answer by carlosn1313 Monday, June 20, 2011 2:00 PM
    Friday, June 17, 2011 2:43 PM
  • Hi carlosn1313,

    The article that introduces the usage and benefit of TIMESTAMP in SQL Server may helpful to you: http://aspalliance.com/1785_Understanding_TIMESTAMP_ROWVERSION_in_SQL_Server.1.


    Best Regards,
    Stephanie Lv

    Monday, June 20, 2011 7:10 AM
  • Hi,

    taken from this Microsoft Support Article

    http://support.microsoft.com/kb/128808

    Timestamps

    On servers that support them (such as Microsoft SQL Server), timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time the record is updated. If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an attached table, re-attach the table in order to inform Microsoft Access of the new field.

    and

    http://www.sql-server-performance.com/2007/access-sql/ (credits to SQL Server MVP Brad McGehee)

    One way to increase performance of DELETEs or UPDATEs made by your Access front-end to a SQL Server backend is to ensure that the table on SQL Server has a timestamp column. If a table does have a timestamp column, then when Access DELETEs or UPDATEs a row, Access will automatically check the timestamp column to see if it has changed during the time that the transaction begun and the current time that the deletion or update is being made. If the timestamp column has changed, then the DELETE or UPDATE will be aborted. If a timestamp column does not exist in the table, then Access will have to compare all the field values when the transaction begun to their current value to determine if there were any changes. This is much slower for Access to perform than checking the timestamp column.

     

     


    Monday, June 20, 2011 1:25 PM
  • Thank you all for the pointers.  Bottom line - TIMESTAMP columns good :)

    It also solved an issue I was having with Access giving me the silly "another user has edited this record" when nobody else had touched it.

    Carlos

    Monday, June 20, 2011 1:59 PM
  • Hi Carlos,
     
    In general, you're right - Timestamp (aka RowVersion) is good in every
    SQL table that is used in an Access front-end.  It does speed up
    concurrency checking and avoids the "record updated by another user"
    error that can occur with Null Bit fields and floating point numbers.
     
    However, there is one possible downside.  It may not affect you, but
    you should be aware of it.
     
    If you add a Timestamp, you lose partial row concurrency.  That is, if
    you update any field in a row, the entire row is "dirtied" and will
    block other edits that are already in progress.
     
    If you don't have a Timestamp, you can do partial row concurrency.  If
    you are careful to omit some fields from an update operation, another
    operation can update them even during an edit.  This can be helpful
    when you have some fields that are edited from an Access form, but
    another process needs to sweep through and update a different field in
    the same table.
     
    I have a slide on this topic in my PowerPoint deck "Best of Both
    Worlds" at our free J Street Downloads page:
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    Monday, June 20, 2011 5:36 PM