Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-20 : 08:24:48
|
| Alwin writes "Hi there,For loading a datawarehouse, we would like to get the data incrementally from a legacy system. This system, Exact, runs on a SQL server 7 database (NT 4) and doesn't have specific date_time columns for keeping track of record updates (or updates on a specific group of columns of a record).However, SQL 7 tables seem to contain always a column 'timestamp'. This column has binary values and according to other information represents:"..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." Also: "..timestamp (T-SQL)A database-wide unique number. The storage size is 8 bytes.RemarksA table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.The value of a timestamp column is unique within a database.A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column. "So, though we only load a couple of tables from the database my guess is that we can use this number for selecting only the changed records from the tables by checking for each record whether the value of the timestamp of the records 'today' is greater than the highest value of timestamp (on any of the tables) 'yesterday'.My first question is: Is this true and always reliable? Second: I tried to convert the binary value into 'readable format by using a CAST or CONVERT function but all values were like 1-1-1900.Do I do something wrong? How exactly is the sequence order (database wide) used and do you have an example of how to convert is before I can use it to do my select on?Thanks in advance!Alwin" |
|
|
|
|
|
|
|
Using the timetamp column