Shulou(Shulou.com)05/31 Report--

How to delete large amounts of data quickly and conditionally in SQL SERVER? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

If your hard disk space is small, and you do not want to set the log of the database to the minimum (because you want other normal logs to still be recorded), and the speed is relatively high, and you are advised to use turncate table1 to clear all the data, because truncate is a DDL operation, does not generate rollback, does not write the log faster, and then, if there is self-increment, restore to 1, while delete will generate rollback If you delete a table with a large amount of data, it will be very slow, and it will take up a lot of rollback segments, and you have to record G-level logs at the same time. Of course, if there is a conditional deletion such as where time < '2006-3-10', can we use delete without logging? the answer is no. The SQL Server engine will log Delete operations by design. So far, there is no way to force some statements not to be recorded in the log. If you execute Delete Table1 where Time < '2006-3-10', the log records are correspondingly large (3-4G) due to the large number of records involved. If feasible, I recommend the following way: select the records you need to keep to the new table. If you use Full Recovery Mode based on the number of records in SELECT INTO, the log may be larger than Select * into Table2 From Table1 Where Time > = '2006-03-10' and then directly Truncate Table1. No matter which recovery model is not logged: Truncate table Table1 finally renamed Table2 to Table1:EC sp_rename 'Table2',' Table1'

This is the answer to the question on how to quickly and conditionally delete a large amount of data in SQL SERVER. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.