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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Use of Timestamp

Author  Topic 

tap_ks
Starting Member

2 Posts

Posted - 2002-09-06 : 06:32:57
Can anybody let me know how to use timestamp datatype & also why it is use. If anybody can let me know URL or example on it.

thanks in advance.

tapks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-06 : 07:03:59
quote:
The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Every time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.

In SQL Server version 7.0, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.


Most common usage I know is if you have a multi-user environment where a row is 'checked out', 'modified' and 'checked back in', you could use the timestamp to know if anyone grabbed it out from under you....

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-06 : 07:18:58
You can also use it to detect the order in which records are changed. (if you dare).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-06 : 12:21:51
Timestamp fields are also handy for doing an incremental rebuild of a full-text index.

Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2002-11-03 : 03:21:54
Hi Jay,

quote:

Most common usage I know is if you have a multi-user environment where a row is 'checked out', 'modified' and 'checked back in', you could use the timestamp to know if anyone grabbed it out from under you....

Jay White
{0}



Could you point me to some sample code on how to do this? My app is ASP with SS2K, so VBScript would be preferable.

Is it just a matter of checking to see if the timestamp has the same value just prior to "checkin" (i.e., UPDATE) as it had at checkout?

Does SS2K automatically update the timestamp field when a change is made to the row?

TIA.


Best Regards,
Jim
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-03 : 08:04:27
quote:
Is it just a matter of checking to see if the timestamp has the same value just prior to "checkin" (i.e., UPDATE) as it had at checkout?

Does SS2K automatically update the timestamp field when a change is made to the row?
Yes and yes.

Here's some VB code:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q170380&

...and here's some C++:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q249819&

The tricky part is converting the timestamp (binary) to a string value and back again in ASP, it can't handle the native format. You can pass a timestamp variable to a stored procedure directly, or pass the string version and have the procedure convert it to a timestamp. Either way, the code for comparing them is:
CREATE PROCEDURE UpdateRow @id int, @ts timestamp, @col1 varchar(10) AS

IF EXISTS (SELECT * FROM myTable WHERE ID=@id AND NOT TSEQUAL(tsCol, @ts))
--TSEQUAL compares timestamp values, you could also use:
--IF EXISTS (SELECT * FROM myTable WHERE ID=@id AND tsCol<>@ts)
BEGIN
RAISERROR('Row has already been updated. Operation canceled.', 16,1)
RETURN
END

--if the two timestamps are equal, then the row was not updated since, and:

UPDATE myTable SET col1=@col1 WHERE ID=@id
The TSEQUAL function is probably the better method to use for comparison, since timestamps are binary in nature and I'm not sure if = and <> always do binary comparisons. So far though they've tested the same for me. TSEQUAL is not documented in Books Online though, but you can find it in

The Guru's Guide to Transact-SQL

by Ken Henderson.

Go to Top of Page

Apollois
Starting Member

49 Posts

Posted - 2002-11-03 : 15:27:23
Many thanks Rob. This is very helpful.



Best Regards,
Jim
Go to Top of Page
   

- Advertisement -