Solved

delete query

Posted on 2013-01-25
15
189 Views
Last Modified: 2013-02-11
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
Comment
Question by:wasabi3689
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820684
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38820690
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
 
LVL 39

Expert Comment

by:lcohan
ID: 38820698
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38820700
Try this:

DELETE FROM mytable
WHERE created_date < getdate() - 45
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820702
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
 
LVL 39

Expert Comment

by:lcohan
ID: 38820706
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820710
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
 

Author Comment

by:wasabi3689
ID: 38820718
I want to remove all records older than 45 days
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820732
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
 

Author Comment

by:wasabi3689
ID: 38820759
I want something that will keep removing records older than 45 days
0
 
LVL 26

Expert Comment

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

Author Comment

by:wasabi3689
ID: 38820791
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820804
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
 

Author Comment

by:wasabi3689
ID: 38820819
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38821646
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question