2

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)

enter image description here

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

enter image description here

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

  • What is this value ?

  • Why Do I have it without having rowversion column ?

Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • 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? – inevio Dec 03 '12 at 11:49
  • @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) – Royi Namir Dec 03 '12 at 11:51
  • Same exact value 0x00000000000007D0. Wonder if it's a type of indicator of non-existence instead of zero. – inevio Dec 03 '12 at 11:53
  • maybe for the master table ? , but still the definition says **on a table that contains a rowversion column within the database.** – Royi Namir Dec 03 '12 at 11:54
  • http://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. – inevio Dec 03 '12 at 11:56
  • Hey, found something http://social.msdn.microsoft.com/Forums/lv/databasedesign/thread/4f357298-cc7a-43c5-a8b5-610db1d7ae70. – inevio Dec 03 '12 at 12:04
  • 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. – inevio Dec 03 '12 at 12:13
  • @@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.) – András Ottó Dec 03 '12 at 12:29

1 Answers1

6

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.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • as this value indicates an insreted /updated rows , When I create a new db - it should be 0. – Royi Namir Dec 03 '12 at 13:08
  • Also , According to definition , I shouldne get any value if i dont have any related column ( look at the bold first sentence) – Royi Namir Dec 03 '12 at 13:08
  • @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. – Damien_The_Unbeliever Dec 03 '12 at 13:10
  • @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. – Damien_The_Unbeliever Dec 03 '12 at 13:11