5

Is there any option like "Time to live" in SQL Server?

Recently I learned about Time to Live in Scylla DB. I would like to know is the option same available across the databases especially SQL Server.

I have searched in Internet but I couldn't get for SQL Server.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Noah
  • 77
  • 1
  • 1
  • 6
  • Please explain what the TTL feature does and then someone can tell you. – Dale K Oct 14 '19 at 09:34
  • No. You won't find that sort of stuff in any relational database system, which SQL Server is and Scylla is not. In an RDBMS, data permanence is a big thing. (Of course you can write queries or schedule jobs to emulate such behavior, but it's not part of the engine itself.) – Jeroen Mostert Oct 14 '19 at 09:35
  • 1
    Many RDBMSs on the other hand have temporal tables - they can record *when* a specific record is valid and return its contents at a specific period of time. SQL Server 2016 [added system-versioned temporal table support](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15). Even in RDBMSs that don't explicitly support that, you can emulate temporal support through `validfrom/validto` fields, triggers and views. That would take care of `TTL` too - it would be a `validTo` field whose default would be `validFrom + N` – Panagiotis Kanavos Oct 14 '19 at 09:38

2 Answers2

8

There is no direct equivalent in SQL Server. However, there's nothing stopping you doing this:

  • Add an ExpiryDateTime column to the table
  • Add a trigger that sets the ExpiryDateTime to whatever future time you think is appropriate. Make the triger fire on INSERT and UPDATE
  • Add a Agent job that periodically deletes rows that have expired

It'll end up pretty much the same as what is there with Scylla DB's TTL.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • I'm interested though, in what your use case is for auto-vanishing data. What do you use it for? – Greg Low Oct 14 '19 at 10:04
  • The use case is store customer's daily activities which is not needed after 5 years. Now in SQL Server, we developed something you mentioned above. – Noah Oct 29 '19 at 14:41
  • Most people would just have a job that removes a month's worth of data, that runs each month. – Greg Low May 30 '20 at 10:11
  • here is another example. A stateless software is deployed with a PS script on multiple instances in Azure VM Scale Set. Locking mechanism(sql row) is used to prevent simultaneous updates, however sometimes the instances fail to remove the lock set by them and the other instances stack with the old version of the app. It is known that the deployment process will not take longer than 10 minutes, so you can have 20 minutes timeout on lock, to avoid stack of other instances. – Edik Mkoyan Sep 02 '20 at 12:54
  • If I were to implement this, I'd add a view on top of the table that excludes "expired but not yet cleaned up data" and potentially partition the data by expiry date so I could truncate a partition's worth of expired data (rather than have to `delete` it). Mine are micro-optimizations on what you have, though. – Ben Thul Sep 18 '20 at 20:54
0

I take a simplistic attitude towards this. Since we don't have TTL in Microsoft SQL Server we could create TTD triggers. In other words TTD TIME TO DIE when an event happens it checks and kills everything that needs to be killed.

Barry OConnell
  • 1
  • 1