SQL Server Performance Forum – Threads Archive
Deleting rows
Hi all I need to delete 23million rows from a live database. I cannot use truncate table command because the table has a FOREIGN KEY constraint. Using delete command will slow down the database. What would be the best way to get rid off 23millon rows? PS: database is constantly adding records in the table, so i cannot drop the table. Many ThanksDipendra
you can try to delete them in batches. set rowcount to a small number of rows, and whitin a loop delete them from the table as long as @@rowcount is bigger than 0.
Do you want to delete all the rows? I don’t think that’s the case as if it was the case then the forign key constriant would have o be taken care off before deletion. Since you want to delete some (million) rows, I would second what Bambola has suggested. Also don’t forget to keep backing up the log for the database. You might also want to consider reindexing the table after the delete operation Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>