Using TTL (time-to-live) column in a table to automatically set a row state

Other SQL Server Topics
,
1

Hey,

I would like to get some expert opinon on using TTL (time-to-live) column in a table.

Here is the use case:

Each row would have a default TTL value, say 3600 seconds. This field can be modified / reset by a user / administrator. I would like to have a stored procedure to automatically go through rows in a table, check that TTL hasn't expired and if it hase change the sate of that row by modifying another field in that particular row. For example a table will have a column called TTL and another one called Active, once TTL has expired, value in Active field should be changed from TRUE to FALSE.

So my questions are:

Is this a common practice?
How is automation is achieved? (stored procedure or trigger)
Is this a good idea if the table has potentially over a million rows?
is there are a better way of achieving the same results in a more efficient way?

Any help will be greatly appreciated.

Thanks a lot

2

The table schema would be helpful but let me ask a few questions in lieu of it.

  1. How is the TTL value processed? Is it a field in the table? Does it get decremented and zero means expired?
  2. 3600 seconds is relation to what? When does the TTL value start?
    Depending on the answer to these questions, a trigger might be the way to automate this? Depending on the answers, the "state" column might be superfluous. A stored procedure might contain the necessary logic but it won't automate the process; something would need to call that procedure, perhaps a SQL Job.
3

One suggestion: when you do a list of qs, put an identifier on the qs so they can be answered w/o repeating the q. For example:

A) Is this a common practice?
B) How is automation is achieved? (stored procedure or trigger)
C) Is this a good idea if the table has potentially over a million rows?
D) is there are a better way of achieving the same results in a more efficient way?

My answers:

A) Don't know. I've not seen it often, but could be common in other industries.

B) Stored procedure. Trigger would not do what you want.

C) It's workable, assuming you're on SQL 2008 or later (and thus that filtered indexes are available) and that most rows are inactive.

D) Don't think so really. You'd need a "live_start_time" column, the TTL column and the Active bit flag column. Code could compare current time to start_time + TTL to determine Active or not.