Post reply

Reg Timestamp Data type

  • March 24, 2008 at 3:37 am

    #184536

    Is Timestamp value is unique in sql server?

  • March 24, 2008 at 4:51 am

    #792947

    - within a table timestamp is unique and is modified every time a row is modified.

    - if you want global unique, use uniqueidentifier (generated)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 24, 2008 at 8:48 am

    #793039

    My recollection is that a timestamp is unique within a database to a transaction. Thus multiple rows within a table (and the database) can have the same timestamp, but only if they were last written by the same transaction.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 24, 2008 at 2:00 pm

    #793208

    Timestamp values are unique within a database

    Books Online

    Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. 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 timestamp column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2008 at 5:46 am

    #793398

    Proof is always good... gotta love BOL. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 25, 2008 at 5:50 am

    #793404

    Jeff Moden (3/25/2008)

    Proof is always good... gotta love BOL. 😉

    Habit from academic writing. Cite it or prove it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2008 at 9:10 am

    #793564

    Heh... I'm the same way... Wise man once told me that "All claims require proof... extraordinary claims require extraordinary proof."

    In the Navy, we said (cleaned up version) "Why sift through the manure to see what the horse thought... just ask the horse." 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 25, 2008 at 9:38 am

    #793593

    Good old BOL did it again, thanks for the rectification.

    Here's a little test script I've found back from my test (sql7)

    Just to demonstrate simple working of it.

    /****** :) best regards ******/

    set nocount on

    go

    drop table dbo.t_test

    go

    print 'tabel dropped'

    go

    CREATE TABLE dbo.T_Test (

    Sleutel int IDENTITY (1, 1) NOT NULL ,

    Ms_Ts timestamp NOT NULL ,

    Ms_Datetime datetime NOT NULL ,

    Ms_Datetime_Last_Used datetimeNOT NULL ,

    Ms_Char char (10) NOT NULL

    )

    GO

    print 'Table reated'

    go

    ALTER TABLE dbo.T_Test WITH NOCHECK ADD

    CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,

    CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,

    CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED

    (

    Sleutel

    )

    GO

    print 'Constraints added'

    go

    --drop trigger TrU_Ms_Datetime_Last_Used

    --go

    CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test

    FOR UPDATE

    AS

    if not UPDATE(Ms_Datetime_Last_Used)

    begin

    update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

    end

    go

    print 'Trigger added'

    insert into t_test (Ms_Char) values('a')

    go

    insert into t_test (Ms_Char) values('b')

    go

    Print 'Rows inserted'

    go

    select * from t_test

    go

    Print 'Update starts here...'

    go

    update t_test set ms_char = 'c' where sleutel = 1

    go

    Print 'Sleutel 1 updated ...'

    go

    select * from t_test

    go

    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

    , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

    from t_test

    order by sleutel

    go

    update t_test set ms_char = 'D' where sleutel = 2

    go

    Print 'Sleutel 2 updated ...'

    go

    select * from t_test

    go

    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

    , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

    from t_test

    order by sleutel

    go

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 25, 2008 at 9:20 pm

    #793841

    ALZDBA (3/25/2008)

    Good old BOL did it again, thanks for the rectification.

    Here's a little test script I've found back from my test (sql7)

    Just to demonstrate simple working of it.

    Aye... nicely done, Johan...

    Take a look at @@DBTS, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 26, 2008 at 6:20 am

    #793968

    [font="Arial"]Hi,

    I had read yr script and also implement it.

    but can u explain me what is the benefit of timestamp column??

    Thanks in advance..[/font]

  • March 26, 2008 at 6:37 am

    #793978

    krishrana17 (3/26/2008)

    but can u explain me what is the benefit of timestamp column??

    About the only thing it's good for is to let you know that something has changed. Each Insert/Update to a row with a TimeStamp datatype in it will cause the TimeStamp to be changed. That means a GUI could read the row, including the TimeStamp, and check to see if the TimeStamp has changed to see if the row is "dirty" from some other Update. Of course, you can use CheckSum or Binary_CheckSum to do almost the same thing without maintaining a column. "It Depends" on what you value the most... disk/backup space or CPU time.

    The original purpose was to help some forms of recovery... here's from BOL again...

    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.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 5, 2008 at 2:13 pm

    #810907

    Copying a table using "Select Into" copies the timestamp values from the original table. In this case, the values are not unique in the database.

    Mike Wright

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply