Deleting rows | SQL Server Performance Forums

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 Thanks

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
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.