Dear Group:
I am trying to find a method (haven't found it yet with Google) on determining when a table's data, not structure, was last updated. We have a process where other teams send us data, but have found that they are not always reliable in this regards.
We have added a process to know when the tables have no data, a method to check the differences in records (say the record count is 20% less than expected), but we are now finding that at times, some tables simply are not getting any updates, but the tables are keeping the data, so the data is stale as it is a week old. For instance, table xyz has 2500 records, but these were added on 06/07/2021, but checking the expected record count, it falls within the range so this check fails. The the check for no data was loaded fails as well. So is there a way to know when the data in a table was last updated?
June 14, 2021 at 2:04 pm
Seems like, ideally, you'd be best added a "ModifiedDate" column to your data, which has both a default value populated with the current date and time, and then when you perform an UPDATE you also set the value of that column. Or, if you can't change the DML statements you are working with, use a (set based) TRIGGER to UPDATE said value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 14, 2021 at 2:08 pm
Thom A:
That makes sense and we have thought about going down that path, but was hoping perhaps we could do it with a query against some system tables without the need to change structure or create triggers.
June 14, 2021 at 2:27 pm
Or, if you cannot add a ModifiedDate column, you could create Insert/Update triggers, and write to an audit table. You'd also need to manage it by deleting old audit data periodically. If you're only Inserting/Updating a few thousand records at a time, it should be low impact, but might not scale up well.
June 14, 2021 at 4:42 pm
If all you need to know is when the data changed - then adding a modified date column and possible a created date column (if you want to know when the row was created and last updated/changed) will work.
If you need to know the value that was changed - so you have a before/after version, then either audit or temporal (system versioned) tables will be needed.
Whether you use audit or temporal tables depends on the usage. If you just need to be able to track the changed data - then audit tables. If you need to be able to reconstruct the data based on a specific date/time for reporting then you probably want a temporal table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 14, 2021 at 7:05 pm
Xevents looking for this will also help. That is non-intrusive to your system.
June 15, 2021 at 7:39 pm
All:
Thank you for the help, but I have been told the way these tables are populated, adding a "Date Added" or any other column to the tables is not possible. We can only read data from the tables and nothing else such as adding columns, triggers, etc.
Is there no other method to accomplish this that doesn't involved changing / adding objects?
Audit and xEvents can capture this, but it's not a rolling level. You'd get a lot of data back. Potentially you could capture, periodically read a window of data and update a "last date" somewhere, but the volume of requests against tables doesn't lend itself to this easily.
There is limited information in indexes, if you don't a 100% accurate date: https://sqlstudies.com/2016/11/14/when-was-my-index-created-or-at-least-last-updated/
perhaps the worst idea is a trigger on the table(s) that updates a single field somewhere with a date when anything changes. I'd rate this as the worst of my ideas by far.
June 16, 2021 at 11:35 am
Thank you Steve. Will look into these. Appreciate all the help from everyone 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply