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.
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} |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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) ASIF 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 inThe Guru's Guide to Transact-SQLby Ken Henderson. |
|
|
Apollois
Starting Member
49 Posts |
Posted - 2002-11-03 : 15:27:23
|
Many thanks Rob. This is very helpful.Best Regards,Jim |
|
|
|
|
|
|
|