Most suitable index for short-lived columns
In my current project, some tables have a column named "changed" that indicates if the current row has changed since the last check. All insert and update statements include this column.
Every hour I run a scheduled task that asks for all the changed rows, does some things with those rows, and then sets null in the "changed" column.
This is a potential performance issue, since I am going to do a lot of writes and reads on this column, the index will be constantly rebuilding.
What's the best option for this scenario (rather than using such a mechanism)?
a source to share
if the table is huge, drop the column and create a dedicated table (with only primary key information) and include triggers on that table. you just need to process this little table and clear it when you finish lines. you will need to do this for every table you track.
if your tables are small, the column might not be a bad idea, but you might see a lock / lock if you have a lot of selections and updates on those tables, and if the scheduled or loop processing is very slow.
if you go with a column, it is better to have a LastChgDate column, then you just process all rows within the range (you will need to keep track of the range to process each time), but you don't need to change LastChgDate to show that it is done. It might be moot if your scheduled process is updating the actual line, but you don't say.
a source to share