Disconnected LINQ Updates: rowversion vs. datetime with trigger?

We're using LINQ to SQL and WCF for a new middle tier, and we're using Data Transfer Objects for passing over the wire rather than using the actual LINQ classes. I'm going to be using one or the other of the methods outlined here - Linq Table Attach() based on timestamp or row version - in order to ensure that updates work correctly and that concurrency is handled correctly.

To save you folks some reading time, essentially you can either use a timestamp/rowversion column in your table or have a datetime column with a default and an update trigger - either way it gets you a column that gets a newly generated value each time an insert or update occurs, and that column is the one used by LINQ to check for concurrency.

My question is - which one is better? We already have datetime columns for "UpdatedWhen" in many of our tables (but not all - don't ask), but would be adding in the defaults and the triggers, or we could just add the rowversion (we'd have to use the timestam开发者_运维技巧p syntax for now, since we're still supporting SQL2005 for a while) to each table - either way we're modifying the DB in order to make it work, so I'd like to know whether there's a performance difference or any other important difference to note between these two alternatives. I've tried searching the web and here on SO, but no luck so far. Thanks.

I had to make the similar decision recently.

I tried rowversion solution first.
The disadvantages that I found:

• Inconvenient usages in LINQ-to-SQL, I mapped the field to byte[]. The code does not look clean when you compare byte arrays
• Theoretically rowversion can roll over and start from 0 again, so row with higher rowversion will not necessarily be older row
• Rowversion is updated on any row update, which in my case was not desirable, I needed to exclude some columns to not affect row version. Having a trigger allows to implement any level of flexibility.

As a result I used datetime2 column with a default constraint and update trigger to set the value to sysutcdatetime()
. This type has accuracy 100 nanoseconds (precision 7 digits - 23:59:59.9999999)
. Although it is possible, I never saw generation of the same value twice yet. But in my case it will not hurt if there will be duplicates. If it was important to me, I would add unique constraint, and see if this ever fails.

I used sysutcdatetime() as this value would not be affected by daylight saving.

I would lean towards using timestamp column for concurrency checks. One - triggers would have some impact upon performance and two - with date time column you'll be limiting yourself to the precision of DateTime column in SQL and C#.

MSDN:

datetime values are rounded to increments of .000, .003, or .007 seconds...

You may want to look at SO: Is there any difference between DateTime in c# and DateTime in SQL server? and MSDN: datetime (Transact-SQL) for more info.