Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Question Unanswered: Timestamp curiousity

    Hi all,

    I'm developing a Java-application in which I read data and update a series of documents from several tables in SQLServer 2000. Now - finally seeing the benefit of timestamp columns - I want to only update the the documents where the data in the SQL tables has changed (performance and so forth).

    I started out by reading the timestamp column as a String object thinking that if I saved this value in my documents I would have something to compare the value against. But it seems to me that this value changes everytime I query the table (its string-representation anyway). So now I'm curious: Event though I do not update the tables (my query is defined as READ_ONLY) does that change the timestamp value. Or should I convert the value to something else that a String. Or is it just plain imposible to do any calculus on a timestamp-column ??

    Any help will be greatly appreciated....

    Regards - and thanks for your time

    Kim Hansen

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    per bol, you can use variables of type varbinary(8) for nullable and binary(8) for non-nullable timestamp fields.

    but no matter how hard i tried, i couldn't make the value change between selects. are you sure you're doing a straight select, not something like xxx.edit....xxx.update?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wouldn't use a timestamp column. Use a datetime value or the BINARYCHECKSUM() function to identify or mark records that have changed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In MS-SQL, a TIMESTAMP column is sort of like a binary "change counter" for your database. Everything that changes something in the database will cause the timestamp to change.

    I like the fact that any row that includes a timestamp will "automagically" change the value of the timestamp if anything changes that row. By holding the key value and the timestamp on the client side of an operation, I can be absolutely certain that I'll catch any change to that row, even one that isn't obvious (for example changing a 1 to a 1, which isn't really a change).

    Both a DATETIME column and a checksum have to be updated by either application code or a trigger. This makes it possible to "forget", which can mean a missed change. A TIMESTAMP column never has that particular problem.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I like BINARYCHECKSUM precisely because it realizes that a value hasn't really changed if it is updated to the identical value. Yeah, it isn't automatic so you have to write code, but you'd have to write code to compare timestamp columns as well.

    What was Stephen Wright's line? "Somebody broke into my house last night and replaced everything with exact duplicates."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    True, but that means that I have to make sure that no bozo (like me) ever does any dirty, low-down, high-faluting trickery that fails to update the checksum. I'd be happy to include both a checksum AND a timestamp, but I'm rather attached to those little beggars for this kind of problem.

    Then again, I've written so many different variations of the optimistic concurrency routines that I really find them more useful than generic stuff anymore. It doesn't take rocket science to clone a hash in Perl, or to compare two hashes for changes. My VC class does all the majik behind the scenes. It is just when I have to do javascript or VB that things get clumsy.

    -PatP

Posting Permissions

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