SQL Server中从表中删除多行

Question

如何从SQL Server 2000中删除150万行数据？完成这个任务需要多长时间。

我不想删除表中的所有记录...我只想删除满足WHERE条件的所有记录。

根据下面的答案编辑：

“我使用相同的查询，即带有Where条件的删除表名...是否可以在运行查询时禁用索引，因为查询已经进行了20小时。另外，请告诉我如何禁用索引。”

- Manglesh

---

这是我知道的...但想知道删除或完成查询需要多长时间。 - Manglesh

你是想要删除所有记录还是不删除？ - Joe Phillips

我不想从表中删除所有记录... 我只想删除满足WHERE条件的所有记录... - Manglesh

需要多少时间取决于必须检查的相关表的数量，如果您正在执行级联删除，如果表上有触发器以及其他因素，例如服务器的性能规格和访问表的其他用户数量。这个问题无法在论坛上回答。 - HLGEM

Answer 1

如果你想删除一个表中的所有记录，只能使用DROP TABLE或TRUNCATE TABLE。

DELETE每次只能删除一条记录，并且为每个删除的行在事务日志中记录一个条目。TRUNCATE TABLE要快得多，因为它不会在事务日志中记录活动。它会从表中删除所有行，但是表结构及其列、约束、索引等仍然存在。DROP TABLE将删除这些内容。

如果你决定使用TRUNCATE，请谨慎操作。它是不可逆的（除非你有备份）。

- DOK

---

这不回答他的问题。 - Joe Phillips

它回答了最初提出的问题。并为其他有类似但不完全相同情况的读者提供了信息。一个人从来不知道当自己提供答案时，原始问题是否会被改变，对吧？ - DOK

Answer 2

创建第二个表格，将第一个表格中不需要删除的所有行插入其中。

删除第一个表格。

将第二个表格重命名为第一个表格。

（或者做出以上操作的变体）

这通常比从大型表格中删除选定记录更快。

- user58849

Answer 3

您可以尝试分批删除。我刚在我的一个表上测试了这个操作，删除时间从13秒缩短到3秒。

While Exists(Select * From YourTable Where YourCondition = True)
  Delete Top (100000)
  From   YourTable
  Where  YourCondition = True

如果你在运行SQL2000，我认为你不能使用TOP谓词，但它可以在SQL2005及以上版本中使用。如果你正在使用SQL2000，则可以使用以下语法：

Set RowCount 100000
While Exists(Select * From YourTable Where YourCondition = True)
   Delete 
   From YourTable
   Where  YourCondition = True

- G Mastros

---

非常感谢你，但如果查询已经运行了20个小时，你能否建议我该怎么做？我应该取消查询吗？ - Manglesh

取消查询可能需要20个小时，因为它可能会回滚您的事务。 - George Mastros

我不确定，但我认为在服务器重新启动后，SQL将继续回滚。 SQL Server内置了许多智能功能，以确保数据完整性得到维护。 - George Mastros

Answer 4

你要删除的表是否有多个外键、级联删除或触发器？所有这些都会影响性能。

根据你想要做什么和事务完整性，你可以分批次删除数据吗？例如，如果你要删除150万条记录，那就是一年的数据，你可以每周删除1次吗？

- Miles D

Answer 5

DELETE FROM table WHERE a=b;

当删除如此多的行时，您可能希望禁用索引，以便它们不会在每次删除时更新。在每次删除时重写索引将显着减慢整个过程。

在开始删除之前，您需要禁用这些索引，否则可能已经存在表锁定。

--Disable Index
ALTER INDEX [IX_MyIndex] ON MyTable.MyColumn DISABLE

--Enable Index
ALTER INDEX [IX_MyIndex] ON MyTable.MyColumn REBUILD

如果您想要删除表中的所有条目，可以使用TRUNCATE命令。

- d03boy

---

我执行相同的查询，即带有Where子句的delete from table_name...在运行查询时是否可以禁用索引，因为查询已经持续了20个小时。请告诉我如何禁用索引... - Manglesh

我可以在查询运行阶段禁用索引吗？ - Manglesh

你或许想要取消你的查询。 - Joe Phillips

Answer 6

从表格中删除符合那1.5百万行的条件

时间取决于。

- Sebastian Sedlak

Answer 7

TRUNCATE 命令还将忽略表中的任何引用完整性或触发器。DELETE FROM ... WHERE 命令将同时遵守这两者。执行时间将取决于您条件列的索引、硬件以及任何额外的系统负载。

- Ankush

Answer 8

删除 SQL 与普通的 SQL 删除完全相同

从表中删除 where [your condition]

但是，如果您担心时间问题，那么我会认为您的问题比这更深入。如果您的表具有大量非聚集索引，则在某些情况下，先删除所有这些索引，然后在删除后重建可能更快。这种情况很少见，但在您的简单删除容易出现超时问题的情况下，可能会有所帮助。

- Cruachan

---

我在20小时前使用了带有表名和Where条件的Delete查询。查询仍在进行中... 需要多长时间才能完成...... 在运行查询时是否有禁用索引的可能性？ - Manglesh

很可能是因为日志...您可以将日志更改为SIMPLE，然后进行删除，并在完成删除后将日志更改回来。这将更快。 - Jason

Answer 9

在Oracle中，也可以使用。

truncate table <table>

不确定那是否为标准 SQL 或可用于 SQL Server。 但它将会 清空整个表格 - 不过它比 "delete from " 更快（它也会进行提交）。

- Werner Lehmann

Answer 10

CREATE TABLE new_table as select <data you want to keep> from old_table;

index new_table
grant on new table
add constraints on new_table
etc on new_table

drop table old_table
rename new_table to old_table;

- Brian