Question about deleting old records from a database

So I have a SQL Se开发者_运维百科rver table.

This table has a column that shows the datetime of when it was inserted.

How can I have something run that always deletes records that are 24 hours old?

---

Yes, take a look at this: How to: Schedule a Job (SQL Server Management Studio)

then just schedule your query:

delete from table_name where date_created < dateadd(d,-1,GETDATE())

---

I couple of things to add to this discussion...

1. To avoid scanning the primary key ensure there is an index (unique or not depending on the data) on the given datetime column.
2. Also review the execution plans of the statement you are trying to use and consider wrapping it in a transaction with a subsequent rollback so that you can repeat the process and evaluate the differences.

For example: Comparing

date_created < dateadd(d,-1,GETDATE())

To

date_created < getdate()-1

Shows that the dateadd function adds considerable overhead to the evaluation and while it would be faster then scanning the PK it's always better to shoot for the fastest option altogether.

---

DELETE FROM <tableName> WHERE <col> < getdate()-1