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 ??
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.
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.
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.