3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fer
  • 1,962
  • 7
  • 29
  • 58
  • 1
    `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 – Igor Borisenko Dec 30 '11 at 07:57
  • @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 – Fer Dec 30 '11 at 08:16
  • 1
    @Igor - the *name* `timestamp` is deprecated. The feature itself (under the name `rowversion`) is alive and well, and supported into the future. – Damien_The_Unbeliever Dec 30 '11 at 12:42
  • @Fer - triggers are hardly a portability paradise between different database systems. – Damien_The_Unbeliever Dec 30 '11 at 12:44

1 Answers1

5

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.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
  • 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. – Fer Dec 30 '11 at 07:48
  • 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. – Mike Nakis Dec 30 '11 at 07:52
  • 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. – Fer Dec 30 '11 at 08:22
  • @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 – gbn Dec 30 '11 at 08:44
  • @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. – Fer Dec 30 '11 at 08:54
  • @Fer any natively supported features will generally perform better than anything you roll on your own by two or more orders of magnitude. – Mike Nakis Dec 30 '11 at 08:56
  • @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 – gbn Dec 30 '11 at 08:58