locked
Timestamp/Rowversion Datatype RRS feed

  • Question

  • Hi,

    I have recently come across Timestamp\Rowversion datatype in SQL Server. I understand this datatypes is used for row versioning and it stores a binary value in table. What I am looking for is how SQL server derives this binary value?

    Can any of you please help me understand this concept.


    Sandeep Prajapati

    Wednesday, February 6, 2019 7:26 AM

Answers

  • "relative time" is a piece of nonsense that you do best to forget that you ever have seen. I guess it is an attempt to cover up for the very unfortunate name "timestamp". (Which in ANSI SQL is the same as datetime/datetime2.)

    As for the missing zeroes, try CAST(cast(6173717 as bigint) AS varbinary). The data type of the literal 6173717 is int, not bigint, whence the difference.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 6, 2019 8:57 AM

All replies

  • What I am looking for is how SQL server derives this binary value?

    SQL Server hold a simple Counter per database, you can query the last assign value with @@DBTS

    select @@DBTS


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 6, 2019 7:36 AM
  • 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

    From here

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 6, 2019 7:48 AM
  • Thank you Visakh16 Olaf Helper  for your reply

    May I please ask what is relative time in database?

    Referring to this - "A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column."

    If i try to run below sql, 

    SELECT CAST(0x00000000005E3415 AS BIGINT), CAST(6173717 AS varbinary)

    Output I get is,

    6173717 0x005E3415

    They look similar except for additional 0s in actual binary value.

    So in case if it is a simple counter then, casting a whole number to varbinary/binary should result in similar binary string


    Sandeep Prajapati

    Wednesday, February 6, 2019 8:08 AM
  • "relative time" is a piece of nonsense that you do best to forget that you ever have seen. I guess it is an attempt to cover up for the very unfortunate name "timestamp". (Which in ANSI SQL is the same as datetime/datetime2.)

    As for the missing zeroes, try CAST(cast(6173717 as bigint) AS varbinary). The data type of the literal 6173717 is int, not bigint, whence the difference.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 6, 2019 8:57 AM
  • Hi Sandeep,

     

    When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros. For more information , please refer to binary and varbinary .

     

    In your example , 0 is used to fill in. As Erland said , you can try following script . Hope it can help you.

     

    SELECT CAST(0x00000000005E3415 AS BIGINT), CAST(CAST(6173717 as BIGINT)  AS varbinary)
    /*               
    -------------------- --------------------------------------------------------------
    6173717              0x00000000005E3415
    */


     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 7, 2019 7:56 AM