Most efficient method to expire records after 10 minutes from creation in sql server
I want to implement a system in sql server that will automatically delete each record after 10 minutes. Since this expiry time will be different for every row, it means that using a job would be incredibly ineffective in performing this task. What is the most efficient way to achieve this? Of course, there are other problems,job such as every 10 runs, and if row insert at 9: 1 , Because jobs will be created once every ten minutes and this row must be removed at 9:11, at 9:20 deleted
I would say you should insert these records into a single table with a datetime field that defaults to getdate(). Then expose the contents of this using a VIEW that only shows those records where that datetime field is within the last ten minutes. And periodically run a job that clears out anything older than that.