SQL server ROWVERSION definition is not accurate?

Question

From msdn

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.

Also

To return the current rowversion value for a database, use @@DBTS.

I have a database ( believe me it has just one table and no timestamp / rowversion fields)

But When I run select @@DBTS , I get a result as if I had a rowversion column :

(p.s. - inserting / updating - doesn't update this value) .

• What is this value ?

• Why Do I have it without having rowversion column ?

Cyclops

It doesn't update because there are no timestamp fields in the database to affect the last used timestamp. Not sure of its initial value probably just something to start if you ever add a timestamp value to track? – Archaeo

@matthewnreid I know , as I was writing , I never had that kind of column. try run this command in your db ( where you know it never had this column) – Cyclops

Same exact value 0x00000000000007D0. Wonder if it's a type of indicator of non-existence instead of zero. – Archaeo

maybe for the master table ? , but still the definition says on a table that contains a rowversion column within the database. – Cyclops

msdn.microsoft.com/en-us/library/ms182776.aspx This seems to use a very similar value in the example 7D3 as an increment. I would assume it needs the initial value for some reason if you ever choose to add a rowversion column but I have found no definite answer. It's interesting. – Archaeo

Hey, found something social.msdn.microsoft.com/Forums/lv/databasedesign/thread/…. – Archaeo

Don't think so, it still occurs in SQL 2012. They seem to indicate it's an uninitialized value or at least a non-deterministic value when initialized, that would align with values for uninitialized pointers. The bug in the list there seems more an issue with Azure handling the values correctly. – Archaeo

@@DBTS returns the last-used timestamp value of the current database, so this one is the initial start. I just created a new db and got the exact same value as you. I think it is getting a value because it is working like a nonnullable identifier. (If you check you have it rigth after finishing with a database creation without tables or shema or any content in the db.) – Bothwell

Answer 1

What is this value ?

It's the value of the counter in the database that is used to implement rowversion columns.

Why Do I have it without having rowversion column ?

The documentation you've quoted says that each database has such a counter. It then states that it will be incremented by operations against tables that contain rowversion columns.

It doesn't say that the existence of this counter depends on there being any such tables in your database.

---

Parse it out as:

A) Each database has a counter

B) that is incremented

C) for each insert or update operation that is performed

D) on a table

E) that contains a rowversion column

F) within the database.

(E) and (F) are conditions that apply to (D). (C) indicates what operations on (D) cause (B) to occur. None of (B-F) affect the truth or otherwise of (A).

---

There's also no documentation (that I can find) that indicates that it should start at 0x0000000000000000 or any other value. Given that the only purpose of this column is to allow ordering to be determined (X happened after Y or before Y), it shouldn't matter what the actual values returned are.

Agiotage

as this value indicates an insreted /updated rows , When I create a new db - it should be 0. – Cyclops

Also , According to definition , I shouldne get any value if i dont have any related column ( look at the bold first sentence) – Cyclops

@RoyiNamir - to your third comment, no. As I say, the documentation indicates that this counter exists. It then tells you how it gets incremented. As I say in my third paragraph, it doesn't indicate that the existence of this counter depends on the existence of any rowversion columns in your database. – Agiotage

@RoyiNamir - to your other comments, I'd expect its value to be equal to the value of the counter in the model database, since that's the basis of any newly created database. – Agiotage