How to automatically delete records on sql server after a certain time

Question

I set up temporary user accounts in my sql database, which stores user names, passwords and dates added by users of my sites.

I originally intended to delete these records programmatically, but now they are wondering if SQL Server 2008 has a built-in function that allows them to automatically delete records after they say one day.

This will solve the problem when the user can log in after closing his temporary account.

thanks

Answer 1

You can create an SQL Job to run every day and execute the specified stored procedure.

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

Answer 2

you can make your own program or configure SQL agent .

Answer 3

You may be able to get what you want without actually deleting entries. You can add a calculated column to the table to indicate 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 a record, so you cannot find them:

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

Then you can use the view to access the table:

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

Then, in your free time, you can delete the rows if you need for performance reasons.

EDIT:

In fact, you don't need a computed column if you express an opinion like:

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

Answer 4

Create a stored procedure to do this, and then schedule the execution of SPROC, as described here .

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