Unable to add a new column to SQL Server table with TIMESTAMP data type

Question

I'm trying to add a new column to an existing SQL Server table with the data type of TIMESTAMP.

This is my script:

ALTER TABLE OrderDetails 
    ADD ModifiedTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

It should be not null. Running this script results in an error

Defaults cannot be created on columns of data type timestamp

I tried executing it without DEFAULT CURRENT_TIMESTAMP. But then it says

Cannot insert the value NULL into column 'ModifiedTime'

Any advises please?

Answer 1

TIMESTAMP in SQL Server has absolutely nothing to do with date & time (thanks to Sybase for screwing that one up!) - it's just a system-internal, binary counter (often used for opmistic concurrency checks). It's been deprecated, too, and renamed ROWVERSION which is much clearer as to what it is.

For date & time - use DATE (if you need only date - no time), or DATETIME2(n) datatypes:

ALTER TABLE OrderDetails 
    ADD ModifiedTime DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

See the official Microsoft docs for more details on date&time datatypes and functions