Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 rowversion (timestamp) not unique?

Author  Topic 

bergloman
Starting Member

2 Posts
Posted - 2009-03-16 : 11:26:11
hi

we use rowversion data type to generate unique identifiers by declaring table variable - e.g. tab(fieldx timestamp) -, inserting a dummy row into it and retrieving value of fieldx for use as unique identifier.

SQL Server documentation states that this is unique within the database. We've encountered a strange situation in one of our installations that this field got "reset". It started producing the same numbers again. Oh, and generated number are stored inside bigint fields later on.

This happened only once as far as we can tell. Does anyone know when can this happen (e.g. certain backup/restore sequence, ....)?

regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-16 : 15:05:09
Row version is a record "edit sequence number" only.
It's only guaranteed to be unique and increasing per record, not among table.

NEWID() is guaranteed to be unique within database. Use this instead of TIMESTAMP.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-03-16 : 16:59:52
quote:
Originally posted by Peso

Row version is a record "edit sequence number" only.
It's only guaranteed to be unique and increasing per record, not among table.

NEWID() is guaranteed to be unique within database. Use this instead of TIMESTAMP.


E 12°55'05.63"
N 56°04'39.26"


From SQL Server 2000 Books Online:

"timestamp
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes."

"@@DBTS
Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database."





CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-03-16 : 17:06:20
quote:
Originally posted by bergloman

hi

we use rowversion data type to generate unique identifiers by declaring table variable - e.g. tab(fieldx timestamp) -, inserting a dummy row into it and retrieving value of fieldx for use as unique identifier.

SQL Server documentation states that this is unique within the database. We've encountered a strange situation in one of our installations that this field got "reset". It started producing the same numbers again. Oh, and generated number are stored inside bigint fields later on.

This happened only once as far as we can tell. Does anyone know when can this happen (e.g. certain backup/restore sequence, ....)?

regards



Probably the way this could happend is if you were not actually in the correct database when you you generate it.


CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-16 : 17:59:46
Or, the master database was restored?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-16 : 18:24:24
I restored the master database and rowversion (timestamp) value begun again from 0x00000000000007D1 !


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

bergloman
Starting Member

2 Posts
Posted - 2009-03-17 : 02:56:23
@Michael: I don't switch databases, so this cannot be the cause.

@Peso: I'll check with IT personnel on site. Judging from log file we've observed the same behavior - counter started at 2001 again (7D1)

Thanks you all for your help.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-03-22 : 12:07:22
I did a test and identified the reason this happened.

When you declare a table variable, the ROWVERSION (TIMESTAMP) that it uses is from the tempdb database, not the database you are in.

Since the tempdb database is recreated every time that you restart SQL Server, the tempdb ROWVERSION starts over again with the value from the model database.

Basically, the idea of generating unique identifiers by declaring a table variable with a ROWVERSION column, inserting a dummy row into it, and geting the ROWVERSION value is invalid.




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-23 : 07:44:58
Michael, thank you.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-03-23 : 09:41:52
That explains why the rowversion value reset after you restored the master database, since you had to restart SQL Server to do that.


This soundS like bad news for the OP. Probably the easiest thing will be to convert to using IDENTITY to generate the number. Or to switch over to UNIQUEIDENTIFIER/NEWID().




CODO ERGO SUM
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-03-23 : 10:10:05
Since the OP said the value eventually ends up in a BIGINT column I thought the identity solution was a good choice.

Be One with the Optimizer
TG
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-03-23 : 10:42:33
It makes me wonder why they didn't pick IDENTITY to begin with, since it is usually much easier to implement.







CODO ERGO SUM
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site