4

Related to my prior question about having a sequence without interim holes (a guarantee that the numbers that are visible to readers are always incrementing) enter link description here I'd like to ask if a solution I devised makes sense.

I created a table with a rowversion column. If I understand this correctly, SQL Server guarantees that the values will be always incrementing. Because this is just a bunch of bytes, queries like WHERE RowVer > 1567 would requires a cast and hence would cause table scan.

So I created an index view that would do the cast and I am querying the view. On the surface level it works (the query plan shows index seek) but I am not sure if the always incrementing guarantee still holds true if I go through the index. Please help.

Edit
It seems to work fine when debugging but inserting to my table block any selects against it. Need to investigate what kind of locks are being hold.

Community
  • 1
  • 1
Szymon Pobiega
  • 3,358
  • 17
  • 18

1 Answers1

5

No, it doesn't make sense

rowversion/timestamp is database unique, not table unique. And it will be changed by UPDATEs to row(s), not just INSERTs. Therefore, this is not strictly monotonically increasing as you want.

Note that the number is not guaranteed to start at any particular value

Edit, what is "database unique"?

MSDN says for rowversion (timestamp)

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion

The current value is in @@DBTS

gbn
  • 422,506
  • 82
  • 585
  • 676
  • On application level I guarantee not to edit anything. I can tolerate holes as long as they are not going to be filled when new rows are added to my table. – Szymon Pobiega May 13 '13 at 12:03
  • 2
    @SzymonPobiega: You say "I can tolerate holes as long as they are not going to be filled when new rows are added to my table". I say "why not use IDENTITY then?" And what is the point of the previous question? – gbn May 13 '13 at 12:05
  • Because with identity, when I start two transactions and the second one commits while the first one not, I can see a hole in the sequence. After the first one commits, the hole disappears. – Szymon Pobiega May 13 '13 at 12:12
  • 2
    @SzymonPobiega: I can't see this as a problem. The number assigned by the IDENTITY will not change. – gbn May 13 '13 at 12:21
  • My app is processing the rows and remembers the index of last row processed. In such case temporal holes can cause skipping rows when processing. – Szymon Pobiega May 13 '13 at 12:25
  • 1
    Well, your app is wrong then if it relies on the index (eg .net index number) not the actual database key. I suggest posting your entire problem as a new question. You are asking for a unsafe workaround because how the app has been implemented – gbn May 13 '13 at 12:27
  • @gbn, You say that timestamp is database unique, not table unique. I'm working with it as a means of version control for lookup tables and I beg to differ. If I edit one table, only it's timestamp is incremented not the timestamps in any other table. Unless I misunderstood you. – Alan Fisher Feb 01 '14 at 22:43
  • @AlanFisher: I mean that "the timestamp value is unique in the database". If you have 5 tables, each with a timestamp column, you can rely on any value being unique across all 5 columns. – gbn Feb 02 '14 at 13:42
  • 1
    @gbn, I did misunderstand you and verified what you said is true, Thanks for taking the time to clear this concept up for me. – Alan Fisher Feb 02 '14 at 18:41
  • @gbn Clearly the OPs use case was ... misguided, but in the more appropriate case of tracking changes (e.g. for ETL purposes) I would think having a non-clustered index on the rowversion would make sense as a quick way to find primary keys that are newer than a specific watermark rowversion value. This question is stated "consequences of indexing the rowversion" which is a more general (and useful) question that what was revealed in the details of the question. Is indexing a rowversion for change tracking a good idea or is it unnecessary for some reason? – Davos Sep 10 '17 at 23:44