delete query

If that shows you the records you want to delete then
Delete from mytable where created_date>dateadd(day,+45,getdate())
will delete those same records

What you're doing there is adding 45 days to today's date.

If you want to see everything older than 45 days ago that would be deleted you'd want:

Select * from mytable where created_date < dateadd(dd,-45,getdate())

Dateadd documentation is here: http://msdn.microsoft.com/en-us/library/ms186819.aspx

(There is no "day" operator for dateadd according to the documentation)

Hm.....do you have data in the future that you want to delete?

Do you have index on that column?

Is that an ONLINE table accessed (INSERT/UPDATE/DELETE) often?


I would rather schedule a job and do it in batches - something like Large batch(put all row IDentifiers in a table that meets your criteria and needs to be deleted) then use a small batch table to delete batches of xxxxxx rows from that.

yeah, I responded too fast and did not read carefully enough so it should have been

Delete from mytable where created_date < dateadd(day,-45,getdate()) 

Select allOpen in new window

But, there is a DAY datepart that works in dateadd.

I strongly suggest do a

Select count(*) from mytable where created_date < getdate()-45 before that to see how many rows you have to delete...

getdate() - 45

@mbizup, careful, the subtraction opperator in Date types is being depricated in SQL Server, I do not recommend that practice to others anymore as it will not always work.

I want to remove all records older than 45 days

are you meaning you want to do that with a manual query now, or that you want something that will keep removing records older than 45 days?  If so then you need to put one of these delete queries in a JOB scheduled to run every day.

I want something that will keep removing records older than 45 days

http://msdn.microsoft.com/en-us/library/ms190268.aspx
Here is the MS page for creating a SQL Server JOB

again,

where is better??? why


DELETE FROM mytable
WHERE created_date < getdate() - 45

or

Delete from mytable where created_date < dateadd(day,-45,getdate())


I have a huge table. I need fast delete

Either is going to work and the performance will not be different.  Delete operations of lots of records are going to be a slow operation, expecially if you have many indexes on the table.  If you can stand the down time, it might be better to delete the indexes first, then delete the rows, then reapply the indexes.  That would speed up the delete and your index stats would be correct when you rebuilt it.

someone recommends the following

Following is the TSQL code to perform batch deletions:

Use AdventureWorks
Go

–Create a copy of Sales.SalesOrderHeader as a test table
Select *
into testOrderHeaderTable
from Sales.SalesOrderHeader

–Delete 5000 Rows at a time
While 1=1
Begin
Delete Top(5000) testOrderHeaderTable

—Check for exit condition
If @@rowcount <5000 break
End

–Remove the test table
Drop table testOrderHeaderTable


What do you think?

What they are doing is breaking the deletes up into batches, because like I said deletes can be an expensive operation and puts locks the table keeping other queries from being able to use it while it is being performed.
This example is not exactly what you need because it is not date specific only raw count driven but the concept is fine.