SQL Server RowVersion

Question

Hey guys,

Quick question Re: Implementation of RowVersion.

I am looking to include it in my next database (Designing it now) and I am pondering 2 different ways:

1. RowVersion row in all tables
2. A separate RowVersion table which has the ID of the table as a foreign key and linked to it.

Anyone tried this? If so what pitfalls or pro's did you experience?

I will be using LINQ to access the data using C# 2008 and SQL Server 2008.

Thanks,

James

Answer 1

If you are using LINQ to SQL then it is a good practice to include a timestamp / rowversion column for each entity table, as this allows LINQ to use optimistic concurrency very easily and results in slightly better performance for updates (as LINQ only has to query the timestamp column and does not have to compare other columns for changes).

So go for the first option. The second option is a non-starter, really.

Answer 2

Assume by RowVersion you mean timestamp (bad name) datatype.

Use a Rowversion column in all tables.

SQLServer updates the column automatically.

I have never used LINQ to SQL (like my SQL raw) but Rowversion column are very useful in updates (concurrency).

Just use "WHERE ID=@ID AND ROWVERSION=@RV" to handle concurrent updates.

Do NOT use your second option!

Answer 3

Add your rowversion to the primary table - putting it in a second table and linking to it will reduce performance.

As Sam said above, LINQ benefits from having rowversions on the table - but I don't think it will work with a linked table.