How to automatically delete records in sql server after a certain amount of time

I have set up temporary user accounts on my sql database where the user names, passwords and date added of my websites users are stored.

I was initially going to have these records delete programmatically but now im wondering if sql server 2008 has a built in function that allows records to be auto-deleted after lets say one day.

This would resolve the issue of a user being able to stay logged into the system after their temporary account is closed

thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You could create a SQL Job to run each day and execute a specified stored procedure.

http://technet.microsoft.com/en-us/library/ms190268.aspx

Method 2

you can have your program do it, or set up a sql agent job.

Method 3

You might be able to get what you want without actually deleting the records. You can add a computed column into the table to say whether the record is valid.

 IsValid as (case when getdate() - CreatedAt > 1 then 0 else 1 end)

You can also do this for key fields in the record, so you cannot find them:

_name varchar(255),
name as (case when getdate() - CreatedAt < 1 then _name end)

You can then use a view to access the table:

create vw_Logins as
    select name, . . .
    from t
    where isValid = 1;

Then, at your leisure, you can actually remove the rows if you need to for performance reasons.

EDIT:

You don’t actually need a computed column, if you phrase the view as:

create vw_Logins as
    select name, . . .
    from t
    where getdate() - CreatedAt < 1;

Method 4

Create a Stored Procedure to do it and then schedule the SPROCs running as explained here.

DELETE FROM myTable
WHERE timeStamp < DATEADD(Day, DATEDIFF(Day, 0, GetDate())-1, 0)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0