Timestamps and MS SQL Server

Timestamp definition

  • SQL Server does not conform to SQL 2003, in respect with timestamp.
  • Timestamp is a 8 bytes sequential Hex number, that has nothing to do with neither the date nor the time.
  • If you need a real timestamp field, you will need to create a DATETIME column.
  • To get the current value of timestamp, use: @@DBTS.
  • It is a unique value per database.
  • Most often used for versioning to keep track of changes to a row. 
select @@dbts as 'TimeStamp current value';
go

TimeStamp current value
-----------------------
0x00000000000007D0

(1 row(s) affected)

CREATE TABLE sales(
        SALESDATE     DATETIME      NOT NULL,
        SALESSTAMP    TIMESTAMP
);
GO

INSERT INTO SALES (SALESDATE) VALUES ('October 10, 2005');
INSERT INTO SALES (SALESDATE) VALUES ('November 10, 2005 10:01:01');
INSERT INTO SALES (SALESDATE) VALUES ('December 6, 2005 11:01:01');
INSERT INTO SALES (SALESDATE) VALUES ('January 5, 2006');
INSERT INTO SALES (SALESDATE) VALUES ('02/07/2006');
INSERT INTO SALES (SALESDATE) VALUES ('03/08/2006 14:01:01');
INSERT INTO SALES (SALESDATE) VALUES ('04/06/2006 15:01:01');
INSERT INTO SALES (SALESDATE) VALUES ('20060504');
INSERT INTO SALES (SALESDATE) VALUES ('20060606 17:01:01');
INSERT INTO SALES (SALESDATE) VALUES ('20060706 19:01:01');
GO

SELECT salesdate Sales, salesstamp AS Stamp, salesqty Qty, salesprice AS Price 
FROM sales;
GO

Sales                     Stamp
---------------------------------------------
2005-10-10 00:00:00.000   0x0000000000000FA2 
2005-11-10 10:01:01.000   0x0000000000000FA3 
2005-12-06 11:01:01.000   0x0000000000000FA4 
2006-01-05 00:00:00.000   0x0000000000000FA5 
2006-02-07 00:00:00.000   0x0000000000000FA6 
2006-03-08 14:01:01.000   0x0000000000000FA7 
2006-04-06 15:01:01.000   0x0000000000000FA8 
2006-05-04 00:00:00.000   0x0000000000000FA9 
2006-06-06 17:01:01.000   0x0000000000000FAA 
2006-07-06 19:01:01.000   0x0000000000000FAB

Note the timestamp value: an 8 bytes Hex value

Timestamp equivalent

  • Eventually, MS will convert the current timestamp of a unique row number, to an actual date and time.
  • Use ROWVERSION instead of timestamp. Row version provides the same functionality and the same value as the current timestamp.

Current date and time

select getdate() as 'Current date and time';
go

Current date and time
-----------------------
2007-05-08 18:35:55.123

(1 row(s) affected)
  • If MS SQL Server generates the timestamp value the value will be unique per database.
  • You can get duplicate values by using:
    • SELECT INTO with a timestamp column is in the SELECT list.