SQL Server quickly conditionally deletes massive data

Recently a friend asked me, he said that he deleted millions of to tens of millions of data in SQL Server is very slow, to help him analyze, some of the following comments, perhaps useful for many people, and also good long did not write a blog, together to explore

If your hard disk space is small and you do not want to set the database log to be minimal (because you want other normal logs to still be logged), and high speed requirements, and clear all the data suggest you use Turncate table1, because truncate is a DDL operation, Do not produce rollback, do not write log faster, and then if there is an increase, restore to the beginning of 1, and delete will produce rollback, if you delete large data table speed will be very slow, and will occupy a lot of rollback segments, At the same time to record the G-level log, of course, if there are conditions to delete such as where time< ' 2006-3-10 ' How to do, can not log with delete, the answer is not, the SQL Server engine design on the delete operation will be logged. So far there is no way to force some statements are not recorded in the log, if the implementation of the deletetable1wheretime< ' 2006-3-10 ' due to the number of records involved, so the log record is also very large (3-4g), if feasible, I suggest the following ways:

Select the records you want to keep to the new table. If you use Fullrecoverymode

Depending on the number of records in the Selectinto, the log may be larger

select*intotable2fromtable1wheretime>= ' 2006-03-10 '

Then direct TruncateTable1. No recovery mode is logged

TruncatetableTable1

Finally, the Table2 was renamed to Table1

Ecsp_rename ' Table2 ', ' Table1 '