如何在没有日志的情况下删除SQL中的表大数据? [英] How to delete large data of table in SQL without log?

问题描述

我有一个大数据表. 该表中有1000万条记录.

I have a large data table. There are 10 million records in this table.

此查询的最佳方法是什么

What is the best way for this query

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

推荐答案

1. 如果要删除该表中的所有行，最简单的选择是截断表，类似于

1. If you are Deleting All the rows in that table the simplest option is to Truncate table, something like

TRUNCATE TABLE LargeTable
GO

截断表只会清空表，您不能使用WHERE子句来限制要删除的行，并且不会触发任何触发器.

Truncate table will simply empty the table, you cannot use WHERE clause to limit the rows being deleted and no triggers will be fired.

另一方面，如果要删除80％到90％以上的数据，则说如果您总共有1,100万行并且要删除1000万行，则另一种方法是插入这100万行(您要保留的记录)到另一个临时表.截断该大表，然后再插入这100万行.

On the other hand if you are deleting more than 80-90 Percent of the data, say if you have total of 11 Million rows and you want to delete 10 million another way would be to Insert these 1 million rows (records you want to keep) to another staging table. Truncate this Large table and Insert back these 1 Million rows.

或者如果权限/视图或具有该大表作为其基础表的其他对象未因删除此表而受到影响，则可以将这些相对少量的行放入另一个表中，然后将该表删除并创建另一个表具有相同的架构，然后将这些行重新导入到此前大表中.

Or if permissions/views or other objects which has this large table as their underlying table doesnt get affected by dropping this table you can get these relatively small amount of the rows into another table drop this table and create another table with same schema and import these rows back into this ex-Large table.

我能想到的最后一个选择是更改数据库的Recovery Mode to SIMPLE，然后使用while循环这样的小批量删除行.

One last option I can think of is to change your database's Recovery Mode to SIMPLE and then delete rows in smaller batches using a while loop something like this..

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable 
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

  SET @Deleted_Rows = @@ROWCOUNT;
END

并且不要忘记将恢复模式更改回完全状态，我认为您必须备份以使其完全有效(更改或恢复模式).

and dont forget to change the Recovery mode back to full and I think you have to take a backup to make it fully affective (the change or recovery modes).

这篇关于如何在没有日志的情况下删除SQL中的表大数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！