Best way in ASP.NET of configuring rows in a database to delete after a certain time

Question

Suppose I have a table in my database like

                           Registrants
=======================================================================
  id |    name    |            email        |    registration_date
=======================================================================
  1  |   "Sam"    | "[email protected]"  |    "2016-03-26T14:25:10"
-----------------------------------------------------------------------
  2  |   "Bob"    | "[email protected]"   |    "2015-12-01T10:09:30"
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .
-----------------------------------------------------------------------
  .  |     .      |           .             |              .

and I want every registrant to automatically be deleted, say, 100 days after registering. What is a proper way to do this, and what is the best way?

The shoddy way I was planning on doing it was going to be to create a sproc

CREATE PROCEDURE FlushOldRegistrants
AS
BEGIN
   DELETE FROM Registrants WHERE DATEADD(day,100,registration_date) < GETDATE()
END

and in my server-side code invoke that sproc every once in a while like

Script.SetInterval(delegate {
    using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("FlushOldRegistrants", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}
 }, 60000); // flush old registrants every hour

But, can I do that all at the database level? Is it possible to create a T-SQL TRIGGER that does this? Or is there an even better way?

Answer 1

The most obvious method is to use SQL Server Agent. Set up a job that runs every day or once a week, and have it call your stored procedure.

Another option is to leave all the data in the table and create a view:

create view v_Registrants as
   select r.*
   from Registrants r
   where registration_date < DATEADD(day, -100, getdate()) ;

---

When you access the data through the view, you will only see the most recent data. One advantage of this approach is that it is precise -- you will never get data that is 102 days old or 100 days and 8 hours old -- which is a danger using a scheduled job. In fact, if the exact cut-off is important, you might want this view as well as the job.

In both cases, you are safer using this where statement, because it can make use of indexes (and hence should be faster).