September 5, 2019 at 4:48 pm
I understand that the timestamp type is not for storing a date or time but a number and that the number used is the value for @@DDTS. What I want to know is if I might could use timestamp in one table to determine when the change in anotehr table took place.
SCENARIO: I have 1 DB with 2 tables.
Table 1 has a timestamp column and it also has another column of type DATETIME that stores the date/time when a row was last updated. To be clear the timestamp column in this table is different from the column that is of type DATETIME.
Table 2 has a timestamp column but there is no other column of type DATETIME that is used to stored the date/time when a row was last updated.
There is a row in table 2 that I need to determine when it was last modified. It does not have a DATETIME column fro storing this so I convert the value in the TIMESTAMP column to a BIGINT and I get the value 863051103. If I then look for the first row in TABLE 1 with a value higher then 863051103 and the first row with a value less then 863051103 and I check the date/time that is stored for those 2 rows in Table 1 for when they were last modified is it reasonable to then say the row in table 1 will have been modified sometime between these 2 dates?
Thoughts?
Kindest Regards,
Just say No to Facebook!September 6, 2019 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 6, 2019 at 5:34 pm
yes that is a reasonable assumption if the 2 tables are on the same database
from https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database.
Note that the fact that the rowversion column has changed does not mean that the row has any change from the previous version - just means that an update was issued against that row - so, an as per link above, if you issue "update table set field = field where pk= 1" it will change the value of the underlying rowversion column even though you did not change the value of the field.
September 6, 2019 at 6:20 pm
You could consider using SQL's Change Tracking to keep track of the changes for you; you could then translate the tracking version into an actual datetime of change. And it's very low overhead.
Edit: This is if you're doing this for a limited number of tables. For all tables across the db, this would not be efficient, and you'd be better off with your current approach.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 6, 2019 at 8:36 pm
UPDATE:
It was no simple matter to put together everything necessary to make this work but I did and sure enough it did work. We still don't have an exact time but thanks to other tables (even though my example listed 2 tables the DB has dozens like this and most do have a column for storing last modified date/time) I was able to greatly narrow the window of when data changed to a single day and thats a big help.
Obviously its better to have this info tracked/stored in some way but when you don;t have control over the DB schema and so you have to work with what you got you can use timestamp in this way.
Kindest Regards,
Just say No to Facebook!September 6, 2019 at 8:44 pm
Scott - unfortunately this is hosted and we don;t have the ability to modify the DB structure without risking breaking support for the software that uses the DB so I'm stuck finding creative solutions like using the timestamp field in this way. That said the DB has around 80 tables that use timestamp and I did query all 80.
I have view that uses the sys.tables and sys.columns system views to provide the schema of all the tables in the DB. Using it I was able to build a query for every table with a timestamp column so I did not have to manually create the query once for every table. Each query returned the closest timestamp value before and after the one I was searching on. I then used those to find the tables with timestamp values closest to the one I was working with and luckily both had columns that stored something I could use for date/time and I was able to narrow down the time frame to an 8 hour period in a single day. Its not as good as knowing exactly when but it definitely helps knowing at least what day that change occurred.
Kindest Regards,
Just say No to Facebook!Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply