9

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

Raj Rao
  • 8,872
  • 12
  • 69
  • 83
  • 3
    compared to what? if you need a rowversion, you need a rowversion.... – Mitch Wheat Oct 03 '11 at 03:46
  • 2
    I agree with Mitch. What workaround would you use to replace it, if you need it? – gbn Oct 03 '11 at 04:22
  • possible duplicate of [Does rowversion/timestamp affects performance significantly?](http://stackoverflow.com/questions/7217062/does-rowversion-timestamp-affects-performance-significantly) – Martin Smith Oct 03 '11 at 11:02
  • As opposed to not having a rowversion column. I was just wondering if I were to add a million rows to a table with a rowversion column, would I see a perceptible increase in time it takes to insert those rows – Raj Rao Oct 04 '11 at 14:20

1 Answers1

14

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
Seph
  • 8,472
  • 10
  • 63
  • 94
  • If you are here considering ROWVERSION to find updated records, take a look at Change Tracking instead. – user1843640 Jan 21 '20 at 19:15
  • From what I understand, the rowversion is DB-wide and is incremented any time a row is inserted / updated in a table that has one. Couldn't that lead to some contention if a lot of tables are having it? – yannick1976 Jan 18 '21 at 11:59