Rowversion vs Timestamp - 04-18-2005 , 07:47 PM
I read that ROWVERSION is new for SQL Server 2000, which is formerly known as
a TIMESTAMP in SQL Server 7.0. The data in a TIMESTAMP column is
automatically inserted or updated every time the data in a row is changed.
My first question is in Visual Basic .NET, when I call to retrieve value
from this TIMESTAMP column, do I use a DATETIME data type or a STRING data
type to represent this value?
Secondly, when I export table from SQL Server 2000 to SQL Server 7.0, if
this table has a ROWVERSION column, will it be automatically converted to
TIMESTAMP when it's exported to SQL Server 7.0?
RE: Rowversion vs Timestamp - 04-18-2005 , 10:16 PM
The Transact-SQL timestamp data type is not the same as the timestamp data
type defined in the SQL-92 standard. The SQL-92 timestamp data type is
equivalent to the Transact-SQL datetime data type.
In SQL 2000,
the Timestamp is just a guaranteed unique Binary number, equivilent to a
Binary(8) datatype, but the value is not very useful, because it
1) has no relation to an actual datetime, and cannot be converted to/from
2) the difference between any two timestamps is in no way proportional to
the time interval between tthe datetimes when they were generated.
3)The value of a timestamp column changes every time the row is updated.
In SQL 2005, the timestamp datatype will be the same as SQL-92, and the new
datatype caleld rowversion will be the same as the current SQL2000
TimeStamp.. For that reason, you should use the keyword RowVersion, not
TimeStamp, in SQL 2000 implementations to minimize the changes that would be
required converting to SQL 2005.
I guess the closest .Net type to a RowVersion (SQL2000 TimeStamp) would be
a byte array (Byte). Cettainly that's the best datatype to use if you
need t odump the value of a rowvresion into a .Net variable.
RE: Rowversion vs Timestamp - 04-19-2005 , 01:53 AM
Thanks. Actually it's like this. My web host is using MS SQL Server 7.0. But
my company is using MS SQL Server 2000. After development, we will export all
the data to MS SQL Server 7.0 (to our web server). That's why I'm not sure if
I'm using rowversion here, will it be recognise there. But since they are the
I'm using it for the concurrency checking. If the value of that rowversion
column before the user update the row is different from when he's updating, I
will disallow him to update. Something like this.
RE: Rowversion vs Timestamp - 04-19-2005 , 09:06 AM
From Books OnLine
"Microsoft® SQL Server™ 2000 INTRODUCES a rowversion synonym for the
timestamp data type. Use rowversion instead of timestamp wherever possible in
DDL statements. rowversion is subject to the behaviors of data type synonyms."
Since SQL 7.0, does not have the synonym 'RowVersion', if you use it in your
Sql 2000 system and then deploy it on a SQL 7.0 system, it will not work.
Yopu need t o use 'Timestamp', NOT 'RowVersion' until your client upgrades,
at lrast to Sql 2000.
Only in SQL 2005,(Yukon, Not released yet) will the Timestamp datatype
change... Then it will be Ansi-92 compliant,
Version TImeStamp Rowversion
RE: Rowversion vs Timestamp - 04-19-2005 , 09:09 AM
Version TImeStamp RowVersion
SQL 7 Binary(8) Not there
SQL2000 Binary(8) Synonym for Timestamp
SQL2005 SQL-92 Binary(8)
RE: Rowversion vs Timestamp - 04-19-2005 , 09:35 PM
Thank you. So I should stick to TimeStamp unless my host upgrade it to MS SQL
2000 or later. Is TIMESTAMP recognise in MS SQL 2000? Will the following
script be recognise in MS SQL 2000 Query Analyzer?
CREATE TABLE CUSTOMER
([CustID] numeric PRIMARY KEY,
Re: Rowversion vs Timestamp - 04-20-2005 , 03:07 AM
timestamp might change, but SQL Server 2005 is the same as 2000 regarding handling of rowversion and
timestamp. In other words, rowversion is a synonym for timestamp in SQL Server 2005 the same way as
in SQL Server 2000 (*).
Also, SQL server still does the "translation" from the word rowversion to the word timestamp at
parsing (or a similar early stage). By this I mean that if you look at the system tables, you will
se that the table is using the timestamp datatype, even if you created it using the datatype name
rowversion. This also happens when you use an ANSI synonym datatype name instead of the SQL Server
name for the datatype (like "integer" instead of "int"). You can see this, in sp_help, as well as if
you generate script. I have communicated this to MS but it was too late to make it to SQL Server
2005. Perhaps some sqlwish (AT) microsoft (DOT) com will help (assuming that I'm not the only one annoyed by
this :-) ).
(*) Tested on February CTP.
Tibor Karaszi, SQL Server MVP
"CBretana" <cbretana (AT) areteIndNOSPAM (DOT) com> wrote
Re: Rowversion vs Timestamp - 04-20-2005 , 03:08 AM
Yes, yes and yes.
Tibor Karaszi, SQL Server MVP
"wrytat" <wrytat (AT) discussions (DOT) microsoft.com> wrote