RowVersion and Performance

Question

I was wondering if there was an performance implications of adding a rowversion column on a table in a Sql-Server database?

Answer 1

There are few performance implications, rowversion is just a new name for the old timestamp datatype. So your database will need to store the additional binary field. Your performance will suffer much more when you try to do queries on this data such as:

SELECT *
FROM MyTable
WHERE rowVersion > @rowVersion

Which is the common way that can be used to get the list of updated items since last @rowVersion. This looks fine and will work perfect for a table with say 10,000 rows. But when you get to 1M rows you will quickly discover that it has always been doing a tablescan and your performance hit is because your table now no longer fits entirely within the RAM of the server.

This is the common problem that is encountered with the rowVersion column, it is not magically indexed on it's own. Also, when you index a rowVersion column you have to accept that the index will often get very fragmented over time, because the new updated values are always going to be at the bottom of the index, leaving gaps throughout the index as you update existing items.

Edit: If you're not going use the rowVersion field for checking for updated items and instead you're going to use it for consistency to ensure that the record isn't updated since you last read, then this is going to be a perfectly acceptable use and will not impact.

UPDATE MyTable SET MyField = ' @myField
WHERE Key = @key AND rowVersion = @rowVersion