Comparing timestamps

test

If you're unfamiliar with the TIMESTAMP data type in SQL Server, be forewarned: its name is rather misleading.

The truth is that TIMESTAMP has absolutely nothing to do with date or time. This data type is used to track the sequence of row modifications within a table. The TIMESTAMP columns contain binary data.

Each table in SQL Server can have only one TIMESTAMP column. Although you can update the column with TIMESTAMP data type manually, you really should not. SQL Server maintains columns with TIMESTAMP data type automatically.

For example, the following script creates the table with a TIMESTAMP column, adds a single row and then modifies the row. Check out how SQL Server maintains the column with TIMESTAMP data type:

SET NOCOUNT ON 

CREATE TABLE #sample (
 id_column INT IDENTITY(1, 1) NOT NULL, 
 name_column VARCHAR(50) NOT NULL, 
 ts_column TIMESTAMP NOT NULL)

INSERT #sample (
 name_column)
VALUES
 ('database')

PRINT 'before update:'

SELECT * FROM #sample 

UPDATE #sample 
SET name_column = 'db'
WHERE id_column = 1

PRINT 'after update:'

SELECT * FROM #sample

Here are the results before the update:

id_column       name_column                     ts_column          
-----------     -----------------------------   ------------------ 
1               database                        0x000000000000007C

After the update:

id_column       name_column                     ts_column          
-----------     ------------------------------  ------------------ 
1               db                              0x000000000000007D

So why would you want to track the sequence of modifications? In a read-only database there are no changes to data rows, therefore TIMESTAMP data type is fairly useless. However, in transactional systems data is modified constantly. When working with such systems it is common to allow multiple users to read data simultaneously – this allows for higher concurrency. However, you must maintain data integrity, that is, you don't want two or more users changing the same data rows at the same time. So how do you know if the data in a particular row has changed since it was retrieved for reading? Well, since SQL Server modifies the values of the TIMESTAMP column, you can compare the old TIMESTAMP with the new TIMESTAMP.

To compare TIMESTAMP values you can use the TSEQUAL function. Although "TSEQUAL" is documented as a keyword, function itself is not found in Books On Line. The syntax of the function is easy; you simply provide the two TIMESTAMP values as parameters. If the two TIMESTAMPS are equal TSEQUAL evaluates to true.

If the two timestamps are different TSEQUAL automatically returns the error number 532. For instance, the following script adds a second record to the temp table I created in previous example and then compares timestamps in two rows, which will always be different:

INSERT #sample (
 name_column)
VALUES
 ('another database')

DECLARE @timestamp1 TIMESTAMP, 
 @timestamp2 TIMESTAMP

SELECT @timestamp1 = ts_column FROM #sample WHERE id_column = 1
SELECT @timestamp2 = ts_column FROM #sample WHERE id_column = 2

IF TSEQUAL(@timestamp1, @timestamp2) 
BEGIN
 SELECT 'timestamps are equal'
END

Here are the results:

Server: Msg 532, Level 16, State 2, Line 14
The timestamp (changed to 0x0000000000000081) shows that the row has been updated by another user.

Now let's see what happens if two timestamps are equal; to do so, change the last script slightly:

DECLARE @timestamp1 TIMESTAMP, 
 @timestamp2 TIMESTAMP

/* make two variables equal */
SELECT @timestamp1 = ts_column FROM #sample WHERE id_column = 1
SELECT @timestamp2 = ts_column FROM #sample WHERE id_column = 1

IF TSEQUAL(@timestamp1, @timestamp2) 
BEGIN
 SELECT 'timestamps are equal'
END

Here is the result:

-------------------- 
timestamps are equal

There is one issue you need to be aware of when using TSEQUAL. If either of the two TIMESTAMP values provided is NULL, then TSEQUAL doesn't return an error. For instance, if I change the last example to make the second TIMESTAMP variable NULL, then TSEQUAL will return nothing. This can lead you to believe that the two TIMESTAMP values are equal.

To avoid such pitfalls, always replace a NULL TIMESTAMP value with a non-null value, as in the following example:

DECLARE @timestamp1 TIMESTAMP, 
 @timestamp2 TIMESTAMP

/* make two variables equal */
SELECT @timestamp1 = ts_column FROM #sample WHERE id_column = 1
SELECT @timestamp2 = NULL

IF TSEQUAL(@timestamp1, ISNULL(@timestamp2, 0))
 BEGIN
 SELECT 'timestamps are equal'
END

Here is the result:

Server: Msg 532, Level 16, State 2, Line 9
The timestamp (changed to 0x0000000000000081) shows that the row has been updated by another user.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in February 2005

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close