timestamp data type in MSSQL
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: timestamp data type in MSSQL

  1. #1
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    timestamp data type in MSSQL

    1. is it use in real world application?
    2. what is it for?

    now i'm doing the SELECT CAST(@@DBTS AS datetime) and the result is 1900-01-01 00:00:07.053, the value just not make sense to me

    thx

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  2. #2
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: timestamp data type in MSSQL

    timestamp (for example from @@DBTS) is guaranteed to be unique in database (wheras GetDate() not). It has "rowversion" alias which better describe its usage. It is a "mark" that U can use to indicate that row has changed.

    So timestamp can be used in manual optimistic row locking or in merge operation. Casting timestamp to datetime has no sense (it is more like GUID but with database scope).

    Best regards,
    Krzemo.

  3. #3
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: timestamp data type in MSSQL

    thanks Krzemo
    as far as i understand that every time we update the database that have timestamp value, it'll automatic update the timestamp field too..
    It is a "mark" that U can use to indicate that row has changed.
    by using the select @@DBTS it's only shown the binary value..is it possible to know which row from which table that has changed? how?

    Casting timestamp to datetime has no sense (it is more like GUID but with database scope).
    coz i'm not sure how to read the 'real' value that represented by those binary (if it's possible)

    PS : sorry, new to MS SQL

    thanks

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  4. #4
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: timestamp data type in MSSQL

    as far as i understand that every time we update the database that have timestamp value, it'll automatic update the timestamp field too..
    Yes.

    by using the select @@DBTS it's only shown the binary value..is it possible to know which row from which table that has changed?
    No it is auto-generated binary value. But it doesn't matter - U have to find if data has changed or not so U only check if it is equal to that U read before (if not than data has changed for sure).
    coz i'm not sure how to read the 'real' value that represented by those binary (if it's possible)
    What for?
    Don't mix timestamp with "SQL-92 timestamp" datatype.
    The "SQL-92 timestamp" datatype is equal to "DATETIME" in SQL Server.
    In new SQL Server releases timestamp will be renamed to "rowversion" and "DATETIME" will be renamed to timestamp (to ensure standards) so if U want 2 use timestamp use "rowversion" alias instead (to prevent confusion when U upgrade server)

    Hope that helps.

  5. #5
    Join Date
    Jan 2003
    Location
    North Carolina
    Posts
    309

    Re: timestamp data type in MSSQL

    Read this from SQL BOL and try to chang from using timestamp to rowversion so when they change the funtion in the future it will not impact you.

    http://msdn.microsoft.com/library/de...ta-tz_6fn4.asp

  6. #6
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: timestamp data type in MSSQL

    thanks Krzemo and antares686

    What for?
    to compare which value is the latest one..but it already been answered by u..

    thanks for the tip from both of u

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  7. #7
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: timestamp data type in MSSQL

    sorry to bother again

    In new SQL Server releases timestamp will be renamed to "rowversion" and "DATETIME" will be renamed to timestamp..
    how can i get rowversion datatype? can't seem to find it anywhere since from what i read rowversion datatype is used from MSSQL 2000 up

    thanks

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center