what type in SQL Server to use as a writeable timestamp?

Question

I have a table in SQL Server, tblMain. There's a trigger that when a row changes, it basically does a SELECT * from tblMain and inserts the changed row into tblHistory. tblHistory is a duplicate of tblMain (only much taller) and it has one extra field for a unique id. I recently added a field of type TimeStamp (which I now understand is being deprecated, but I'll deal with that later) in order to avoid a Write Conflict problem in Microsoft Access 2007.

Obviously the trigger copies every field in tblMain to tblHistory. It's doing a Select *. However, if I put a field of type timeStamp in the history table to receive the field from tblMain, the trigger will fail, obviously. What type should I use in the history table to accept a TimeStamp source?

Answer 1

The TIMESTAMP column type is being renamed, basically, with SQL Server 2008 and up, it will be called ROWVERSION instead - it's not going away, just got a new name.

Now, the TIMESTAMP columns are indeed non-writable - so basically, in your trigger, you need to do an INSERT statement which lists all columns that you want to write - and omit the TIMESTAMP.

The TIMESTAMP/ROWVERSION is your best bet - only that type of column is guaranteed to be always unique, always ever-increasing for each subsequent insert. Any of the DATE-related columns could have duplicates (in SQL Server 2005, the DATETIME has an accuracy of 3.33ms - so you can definitely have duplicates).

You might want to have a combination of a TIMESTAMP for accuracy and uniqueness, and a DATETIME for human readability. The TIMESTAMP is handled by SQL Server automatically, for the LastModifiedDate DATETIME you could define a DEFAULT CONSTRAINT of GETDATE() so that for each insert, the current date/time would be recordded.

Answer 2

in a trigger you can do this, which will only insert the affected rows, and is much faster than parsing the original table to find what has changed.

INSERT INTO tblHistory
        (col1, col2, ...)
    SELECT
        col1, col2, ...
        FROM INSERTED

Answer 3

From the docs:

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

This works:

-- //Main table, with TIMESTAMP column
CREATE TABLE Main ( id INT, TIMESTAMP )

-- //Some values
INSERT Main VALUES ( 8, DEFAULT )
INSERT Main VALUES ( 4, DEFAULT )
INSERT Main VALUES ( 2, DEFAULT )
INSERT Main VALUES ( 7, DEFAULT )
INSERT Main VALUES ( 0, DEFAULT )

-- //See the values
SELECT * FROM Main

-- //History table
-- //with VARBINARY(8) to store a nullable TIMESTAMP
CREATE TABLE History (id INT, ts VARBINARY(8))

-- //Populate History table
INSERT History
SELECT * FROM Main

-- //See the values
SELECT * FROM History