CODEWITHSUNDEEP
sql-servert-sql
Scott
Scott

Reputation: 4210

Question about deleting old records from a database

So I have a SQL Server 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?

Upvotes: 0

Views: 1595

Answers (3)

artofsql
artofsql

Reputation: 613

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.

Upvotes: 3

Ortiga
Ortiga

Reputation: 8824

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())

Upvotes: 3

Quantum Elf
Quantum Elf

Reputation: 752

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

Upvotes: 2

Related Questions