SQL Server Performance Forum – Threads Archive

Deleting many rows/records from a table

I require to delete many rows/records from a huge table (maybe a few million) older than a certain date. The table has nested relationships of maybe 3-4 tables in depth. What would be the best strategy to perform the cleanup with minimum imapct on the server itself which is running actively servicing other requests. Thanks in advance!

Probably the easiest solution is to break down the delete into smaller blocks like
SET ROWCOUNT was_auch_immer
SELECT 1
WHILE @@ROWCOUNT > 0
DELETE –statement
SET ROWCOUNT 0 –Frank
http://www.insidesql.de

… during less traffic hour on the database [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

I like the rowcount option myself that Frank posted and often use it. This works great in single table deletes. If by chance, you want to keep fewer records than you want to delete, you also have the option of creating a temporary table, moving into the records you want to keep. Drop the old table and then rename the new one. It sounds like you want to delete from not just the one table, but from tables beneath it (nested relationship you refered to). If you have triggers for this, it willl be taken care of. If not, the rowcount may not work if you have one to many relationships etc. For this, you have several options. Here is just one. create #temp1(
delid int identity(1,1),
MainTablekey int,
status int —optional field) insert #temp1 (maintablekey)
select tableid from maintable where date < [deletedate] declare @numrecords int, @currentrecord int, @interval int
set @numrecords = count(*) from #temp1
set @currentrecord = 1
set @interval = 500 —this is how many ‘main table’ records to delete at one time
while @currentrecord <= @numrecords
begin delete from childtable where parentkey between @currentrecord and @currentrecord + @interval
delete from maintable where tableid between @currentrecord and @currentrecord + @interval set @currentrecord = @currentrecord + @interval
end
If your nesting is even deeper, start with the lowest table first in the series of deletes then work your way up. Like I said, just one way. There are others.

]]>