SQL Server Performance Forum – Threads Archive
Deleting millions of rowsHello, I wanna learn what is the most efficient method for deleting 100millions of rows from has-300millions table? I’ve checked out the below address but I think there should be other recommendations… Thanks a lot… http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=807
If you’ve got loads of disk space, you could select the 200 million rows you want to keep into a copy table. Once it’s complete you’d have to run it again to get any additional rows inserted then drop the original table and rename the copy. This would be more efficient than the other methods mentioned in terms of locks held on the original table, but certainly wouldn’t be more efficient in terms of disk space and probably execution time too. Dave.