Using timestamp to identify updated rows in table

Question

Using timestamp to identify updated rows in table

malcolm kilbride

SSC Veteran

Points: 266
More actions
April 5, 2005 at 1:45 pm

#65843

Hi,
So I'm looking to identify when changes have taken place to a particular table on a sql 2000 db. Unfortunatly, it's not my db so I can't change the info held on it (or add a updatedate).
It does have a 'timestamp' column..can this be used - I understand it is not a date as such? Say to give me all rows with a 'timestamp' column greater than a specific value?
Hope that makes sense
Malcolm

Viewing 6 posts - 1 through 5 (of 5 total)

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

JN-161845 SSCrazy Points: 2052 More actions · Answer 1

Not sure what your situation is, but if Timestamp field is of data type DATETIME or SMALLDATETIME then yes, you can do following:

DECLARE @NewRowsTime DATETIME

SET @NewRowsTime = '4/5/05'

SELECT * FROM TABLE

Where TABLE.TimeStamp > @NewRowsTime

You can replace TABLE with your actual table name and TimeStamp with your actual column name for TimeStamp field.

Hope that helps!

JN

malcolm kilbride SSC Veteran Points: 266 More actions · Answer 2

Hi,

Thanks for this...unfortunatly the field is one of those 'binary rowcount' items.

Malcolm

wz700 SSCertifiable Points: 7133 More actions · Answer 3

I believe timestamp is row based, each row in a table has its unique value. So select "all rows with a 'timestamp' column greater than a specific value" would not make much sense.

You can check if a row has been updated by comparing its current value to a specific value. Any update on any field in a row will change the timestamp value.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

>Say to give me all rows with a 'timestamp' column greater than a specific value?<

Yes. The datatype is varbinary and you can find the last timestamp for the entire DB using @@DBTA kinda like this... you will need to find some way to step through the tables (MS_ForEachTable, undocumented but usefull although, if you write your own, will probably be faster).

USE pubsSELECT @@DBTS

--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)

dongadoy SSC Eights! Points: 880 More actions · Answer 5

Is your "timestamp column" a column with the timestamp datatype? It is simply a unique value within the database that automatically changes whenever the the row is updated. It does not increment and is more like a GUID with database scope. The timestamp datatype is being renamed to rowversion to avoid confusion.

In order to use timestamp for finding changes to data, the system must remember the old timestamp value. First, copy the timestamp column to a table. Later, to find changes, query rows in the original table with a timestamp value not found in the copy table. Those rows were either inserted or updated. At the same time, refresh the copy table for later. With millions of rows, this is a very inefficient method. And there is no tracking of deleted data.

Better way to track changes is with a trigger on the table. The trigger posts to a log table or does whatever you need to do on a data change. But it sounds like you are not allowed to add triggers to the database.

Replication is the next best method of tracking changes to data. Use custom replication stored procedures. The procedure posts to a log table or does whatever you need to do on a data change. You can also use triggers on the replicated tables as an alternative to custom replication stored procedures.