TSEQUAL ( timestamp-expression-1, timestamp-expression-2 )

BIT

The TSEQUAL function can only be used in a WHERE clause and is most commonly used as part of an UPDATE statement.

Although the TSEQUAL function can be used to compare two ordinary TIMESTAMP values, the purpose of TSEQUAL is to determine whether or not a row has been modified by another connection by comparing two special Transact-SQL TIMESTAMP values.

In a single-row UPDATE statement using TSEQUAL, if timestamp-expression-1 is equal to timestamp-expression-2 and one of these values refers to a column declared with DEFAULT TIMESTAMP and the other refers to the value of the column when the row was last fetched, then the row has not changed since it was fetched and TSEQUAL returns TRUE. If the row was changed by another user, its timestamp has been modified and the TSEQUAL function returns FALSE. If the TSEQUAL function returns FALSE in this situation, the UPDATE is not performed. The application can determine that no rows were updated by examining the number of rows affected, for example by using @@rowcount. If no rows were affected, the application can assume that the row was modified by another user and that it needs to be re-fetched.

• The data type of a timestamp column
• TIMESTAMP special value
• The special Transact-SQL timestamp column and data type

Suppose you create a TIMESTAMP column Products.LastUpdated to store the timestamp for the last time the row was updated. The following example uses the TSEQUAL function to change a row value. An update is applied to the row only when the row has not been changed since it was last fetched.

SELECT LastUpdated into old_ts_value 
FROM Products 
WHERE ID = '300';

UPDATE Products
SET Color = 'Yellow'
WHERE ID = '300'
AND TSEQUAL( LastUpdated, old_ts_value );