timestamp vs. rowversion vs. datetime thru update triggers ? 1

[forevertechie]

I'm currently designing a SQL Server 2000 database for a small DIY application with plans to provide features such as concurrency data management built into it. I'm relatively new to SQL Server 2000 and was wondering which might be the best way to prevent concurrency issues (when the DB scales up!)

Currently, I've added a timestamp column to all the tables alongwith a DateLastUpdate column that will be updated using the insert, update and delete triggers. In SQL 6.5 days, I used the timestamp column to prevent concurrency issues on the database. But in SQL 2000, I find these 3 options:
a) Timestamp datatype
b) RowVersion
c) Datetime (updated thru triggers)

Any information on the need and purpose of the timestamp column and rowversion is highly appreciated. (NOTE:My BOL search did not give me an indepth information on this topic)

Thanks,
- Forevertechie

 

[mrdenny]

The timestamp data type is a server controlled column. The output data from a timestamp column is useless to you. I would simply use a datetime column and keep the column updated.

It will make life much easier.

Not to mention that the behavior of timestamp may change.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[stevexff]

You don't really care what it is, you just need an optimistic lock token that can be retrieved by a reader, and passed back in on a subsequent update. Some people use an int value that gets compared and incremented on update, others use a datetime value. My own personal favourite is the value of BINARY_CHECKSUM(*). It doesn't take up any space on the table, and you can't forget to update it.