Title: Timestamp vs Int column for row versioning in SQL Server
Tags: sql;sql-server;concurrency;rowversion
Question: I want to apply data concurrency on some of tables in my SQL Server database. In order to achieve row versioning, I am thinking of adding an integer column named RowVersion, and increase the row value of the column when I update a row.
There is an other option: using timestamp column. When you use timestamp column, SQL Server automatically creates a unique value when the row was updated.
I want to know the advantages of these options. I think that inserting an int column to store row version is more generic way while inserting a timestamp column is SQL Server specific.
What is more, integer value of a row version is more human readable than timestamp column.
I want to know other advantages or disadvantages choosing integer column for row version field.
Thanks.
Comment: @lgor updating manually with a trigger seems a good option. also i will have stored the last update date value in the row. i will think about it, thanks

Comment: @Igor - the *name* `timestamp` is deprecated. The feature itself (under the name `rowversion`) is alive and well, and supported into the future.

Comment: @Fer - triggers are hardly a portability paradise between different database systems.

Comment: `Timestamp` is deprecated in SQL Server 2008+. You can create a `trigger after update` which will update some `datetime` column. Also, you may want to read this topic http://stackoverflow.com/questions/4112163/timestamp-in-sql


Here is another answer: If you let SQL server do it for you, you have guaranteed atomicity for free. (Concurrent updates on the same table are guaranteed to produce different row versions.) If you roll your own row versioning scheme, you are going to have to do whatever it takes to guarantee atomicity by yourself.
Comment for this answer: suppose i want to move my database to oracle. then using timestamp column will make harder moving to oracle? i suspect that using timestamp column will make the database more dependent to sql server.
Comment for this answer: you are right. i think i will not use ROWVERSION or TIMESTAMP types which are sql server dependent types. i will consider using integer or datetime column.
Comment for this answer: @gbn i just want a minimum dependency on sql server. you are right there is oracle version of ROWVERSION type. But the main considiration should be performans i think. i will now look for performans comparation of doing versioning manually or leaving versioning to sqlserver. i will choose the most efficent one according to performans. if you have any idea about the performance, please comment.
Comment for this answer: @Fer: that's absurd. The fact of ROWVERSION not being portable to Oracle is minor compared to all the other stuff you'll have to fix. Do yourself a favour and use ROWVERSION for the immediate requirements. Anyway, you just port it as binary(8) or such and use ORA_ROWSCN
Comment for this answer: @Fer: Rolling your own row versioning will be not be concurrency-safe if you have enough data volumes to worry about performance. The concurrency safe way will be slower because you have to use non-portable semaphores or lock hints. As this answer says "If you let SQL server do it for you, you have guaranteed atomicity for free". What is concurrency safe? See my previous answers  http://stackoverflow.com/search?tab=newest&q=user%3a27535%20%2bconcurrency%20%2bsafe
Comment for this answer: That is true. You have already anticipated this in your question by mentioning that a timestamp column is sql server specific. I only wanted to mention something different.
Comment for this answer: @Fer any natively supported features will generally perform better than anything you roll on your own by two or more orders of magnitude.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:12/30/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sql-server><concurrency><rowversion>', 'Title': 'Timestamp vs Int column for row versioning in SQL Server', 'ViewCount': 1912, 'id': 8677260, 'user_AboutMe': '<p>I am a R&amp;D Engineer, developings different software projects. I used many programming languages such as C#,Java,Android,Asp.Net,C etc. </p>\n', 'user_AccountId': 383400.0, 'user_DisplayName': 'Fer', 'user_Reputation': 1892.0, 'user_Views': 212.0, 'user_id': 739228.0}}