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

This article mainly explains "how to delete massive data quickly and conditionally in SQL SERVER". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to delete massive data quickly and conditionally in SQL SERVER".

Recently, a friend asked me that it was very slow for him to delete millions to tens of millions of data in SQLSERVER. I helped him analyze it and put forward some of the following suggestions, which may be useful to many people. 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 turncatetable1 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 rollbacksegments, and you have to record G-level logs at the same time. Of course, if there is a conditional deletion such as wheretime < '2006-3-10', can we use delete without logging? the answer is no. The SQLServer 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 DeleteTable1whereTime < '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 FullRecoveryMode based on the number of records in SELECTINTO, the log may be larger Select*intoTable2FromTable1WhereTime > = '2006-03-10' and then directly TruncateTable1. No matter which recovery model it is, it is not logged: TruncatetableTable1 finally renamed Table2 to Table1:ECsp_rename'Table2','Table1'

Thank you for reading, the above is the content of "how to delete massive data quickly and conditionally in SQL SERVER". After the study of this article, I believe you have a deeper understanding of how to delete massive data quickly and conditionally in SQL SERVER, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.