More DBA job interview questions and answers at
(Continued from previous question...)
The timestamp datatype in Sybase ?
The timestamp datatype is user-defined datatype supplied by Sybase, defined as:
It has a special use when used to define a table column. A table may have at most one column of type timestamp, and whenever a row containing a timestamp column is inserted or updated the value in the timestamp column is automatically updated. This much is covered in the documentation.
What isn't covered is what the values placed in timestamp columns actually represent. It is a common misconception that timestamp values bear some relation to calendar date and/or clock time. They don't - the datatype is badly-named. SQL Server keeps a counter that is incremented for every write operation - you can see its current value via the global variable @@DBTS (though don't try and use this value to predict what will get inserted into a timestamp column as every connection shares the same counter.)
The value is maintained between server startups and increases monotonically over time (though again you cannot rely on it this behaviour). Eventually the value will wrap, potentially causing huge problems, though you will be warned before it does - see Sybase Technical News Volume 5, Number 1 (see Q10.3.1). You cannot convert this value to a datetime value - it is simply an 8-byte integer.
Note that the global timestamp value is used for recovery purposes in the event of an RDMBS crash. As transactions are committed to the log each transaction gets a unique timestamp value. The checkpoint process places a marker in the log with its unique timestamp value. If the RDBMS crashes, recovery is the process of looking for transactions that need to be rolled forward and/or backward from the checkpoint event. If a transaction spans across the checkpoint event and it never competed it too needs to be rolled back.
Essentially, this describes the write-ahead log protocol described by C.J. Date in An Introduction to Database Systems.
So what is it for? It was created in order to support the browse-mode functions of DB-Library (and for recovery as mentioned above). This enables an application to easily support optimistic locking (See Q1.5.4) by guaranteeing a watch column in a row will change value if any other column in that row is updated. The browse functions checked that the timestamp value was still the same as when the column was read before attempting an update. This behaviour is easy to replicate without necessarily using the actual client browse-mode functions - just read the timestamp value along with other data retrieved to the client, and compare the stored value with the current value prior to an update.
(Continued on next question...)