Multiple timestamp columns in SQL Server 2000

Question

I need to create a table in SQL Server 2000.

create table TABLE (
        DBID_ bigint not null,
        CLASS_ varchar(255) not null,
        DBVERSION_ integer not null,
        HPROCI_ bigint,
        TYPE_ varchar(255),
        EXECUTION_ varchar(255),
        ACTIVITY_NAME_ varchar(255),
        START_ timestamp,
        END_ timestamp,
        DURATION_ bigint,
        TRANSITION_ varchar(255),
        NEXTIDX_ integer,
        HTASK_ bigint,
        primary key (DBID_)
    );

An error occurs when I run it.

A table can only have one timestamp column. Because table TABLE already has one, the column END_ cannot be added.

What is the best alternative for timestamp for SQL Server? How to fix this issue?

Answer 1

A timestamp is not a datetime datatype as the name suggests. It is an internal value that is relative to the server's clock, but an actual time cannot be derived from it's value. It is simply used to evaluate whether a row has been updated, and thus a table can only have one column of this type. The timestamp syntax is actually deprecated and is now named rowversion which makes a lot more sense.

Given your column names (Start, End) I assume you are trying to store actual timestamps, and should instead be using datetime as your datatype.