Share via

Purpose to use timestamp datatype in sql server?

JJ TT 141 Reputation points
2021-01-20T22:12:30.113+00:00

Hello,

  1. What is the purpose to use timestamp datatype in sql server?
  2. Do you have a concrete example of using timestamp in real business case?
  3. When is it relevant to use timestampe datatype?

Thank you!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,367 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2021-01-20T22:57:23.357+00:00

    This datatype is also known as "rowversion", and the original name is really unfortunate.

    A timestamp column is automatically updated whenever a row is updated, and the value is monotonically increasing in the database. There is absolutely no relation to date and time.

    The main purpose of timestamp is to implement optimistic concurrency. The client reads a row from the database gets the timestamp value from the database and sends it back on UPDATE. The UPDATE operation includes a condition on timestamp in the WHERE clause, and if the timestamp value has changed since the client read the value, the update is not performed - and the update that came in-between is not lost.

    timestamp/rowversion does not seem to be that popular these days, but I have worked with a system where have many of these columns for exactly this reason.

    Because it is monotonically increasing, some people also use it to find the most recently changed rows in a table, but this is not what it is designed for, and it does not work well in high-concurrency environment. SQL Server offers better features for this purpose, for instance Change Tracking.

    8 people found this answer helpful.
  2. CathyJi-MSFT 21,136 Reputation points Microsoft Vendor
    2021-01-21T03:10:33.603+00:00

    Hi @JJ TT ,

    >What is the purpose to use timestamp datatype in sql server?

    According to MS document, the timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    TIMESTAMP Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard. It has nothing to do with date and time. Timestamp is a synonym for rowversion.

    Rowversion data type is not a date or time data type. Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

    Please refer to MS document rowversion (Transact-SQL) to get more information.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    1 person found this answer helpful.
    0 comments
  3. Tom Phillips 17,721 Reputation points
    2021-01-20T23:46:34.94+00:00

    Just to add to Erland's answer.

    You should use "rowversion" instead of "timestamp" data type. "Timestamp" is actually depreciated and will be removed at a later time. This data type is not related to a date/time. You cannot convert a rowversion to a datetime. That is not its intended purpose.

    Rowversion is an internally generated and managed id which changes every time the row is updated. If the rowversion has changed, the row has been updated.

    Please see:
    https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver15