SQL Server Performance

Timestamp

Discussion in 'General Developer Questions' started by vietcave, Sep 12, 2005.

  1. vietcave New Member

    Hi everyone,

    Although I try my best to find material explaining about timestamp data type, it is still vague to me.

    It seems timestamp is created by time, is that right? And this data type is used in 2-way replication, isn't it?

    Thanks.


  2. Madhivanan Moderator

    There is no datatype called TimeStamp in SQL Server<br />What you need to do is to have DateTime or SmallDateTime datatype and extract time from that<br /><br />Select Convert(varchar,Getdate(),10<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  3. vietcave New Member

    Hi,

    I think there is a data type called timestamp. Its size is 8 bytes.

    Thanks.
  4. FrankKalis Moderator

    Quoted from BOL

    quote:
    timestamp
    The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.

    In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.

    Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

    To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. vietcave New Member

    Thank FrankKalis,

    I now understant timestamp. It is increased when a row is inserted or updated.

Share This Page