Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

delete query

I have a huge table with a created_date, I want to delete all the records more than 45 day. how the query works?

here is what I write

Select * from mytable where created_date>dateadd(day,+45,getdate())
0
wasabi3689
Asked:
wasabi3689
  • 7
  • 4
  • 2
  • +2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
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
0
 
Steve WalesSenior Database AdministratorCommented:
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)
0
 
lcohanDatabase AnalystCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mbizupCommented:
Try this:

DELETE FROM mytable
WHERE created_date < getdate() - 45
0
 
Chris LuttrellSenior Database ArchitectCommented:
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()) 

Open in new window

But, there is a DAY datepart that works in dateadd.
0
 
lcohanDatabase AnalystCommented:
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...
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
wasabi3689Author Commented:
I want to remove all records older than 45 days
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
wasabi3689Author Commented:
I want something that will keep removing records older than 45 days
0
 
Chris LuttrellSenior Database ArchitectCommented:
http://msdn.microsoft.com/en-us/library/ms190268.aspx
Here is the MS page for creating a SQL Server JOB
0
 
wasabi3689Author Commented:
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
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
wasabi3689Author Commented:
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?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now