Presents your 
SQL SERVER E-NEWSLETTER for June 10, 2003

<------------------------------------------->

USE ROWVERSION INSTEAD OF TIMESTAMP IN DDL STATEMENTS

SQL Server 2000's timestamp is a confusing data type that you'll want to
avoid using when possible. Part of the problem is that timestamp means
different things in SQL Server 2000 and in the SQL-92 standard.

In SQL Server 2000, timestamp is a data type that creates an
automatically generated binary number that is guaranteed to be unique. 
This is typically used as a mechanism for version-stamping rows. 
In the SQL-92 standard, the timestamp data type is equivalent to the datatime
data type, which is a combined data and time value.

To make matters even more perplexing, Microsoft may replace timestamp
with a rowversion data type in its next release of SQL Server.

Microsoft has created a rowversion data type synonym. A data type
synonym can be used in the place of its equivalent data type in data 
definition language (DDL) statements such as CREATE TABLE, 
CREATE PROCEDURE, etc. But, like other data type synonyms, there are differences
between using rowversion and timestamp.

If you use timestamp in a CREATE TABLE or ALTER TABLE statement, you
don't have to supply a column name for the timestamp type; SQL Server 
will generate a column name of timestamp for you.

CREATE TABLE exampleTable (PriKey int PRIMARY KEY, timestamp)

If you use rowversion, however, you must supply the column name when 
you specify rowversion.

A table can have only one timestamp/rowversion column. Every time you
add a new row, the timestamp/rowversion field is populated. If you update a
row, the timestamp/rowversion field is updated as well. This is probably
the foremost reason why timestamp/rowversion columns are bad candidates
for primary keys.

J.E. Harvey, MCSD, MCDBA, has been tinkering, writing, and consulting
about technology for more than two decades.

----------------------------------------