Most approprieted index for short-lived columns

Question

In my current project, some tables have a column named "changed", which indicates if the the current line had been changed since the last check. All the insert and update statements includes this column.

Every hour, I run a schedulated task that queries all changed rows, do some stuff with those rows and then sets null to it's "changed" column.

This is potentially a performance issue, since I'm going to do lot's of write and read operations in this column, the index will be constantly being rebuild.

What's the best option for this scenario(rather than not using this kind of mechanism)?

Answer 1

if your table is huge, drop the column and make a dedicated table (with primary key info only) and have triggers insert into this table. you then need to just process this small table, and clear it as you finish rows. you would need to do this for each table you are tracking.

if your tables are small, the column may not be a bad idea, but you may see blocking/locking if you have lots of selects and updates on these tables, and if your scheduled processing loops or is real slow.

if you go with a column, it might be better to have a LastChgDate column, then you just process all rows within a range (you'll need to track the range to process each time), but you won't need to change the LastChgDate to show it is "done". This might be moot if your scheduled process is updating the actual row, but you don't say.

Answer 2

Since the column likely only has two values (null and 1 for changed), an index is probably useless anyway.