多行从SQL Server中的表中删除

How I can Delete 1.5 Millions Rows From SQL Server 2000, And how much time it will take to complete this task.

如何从SQL Server 2000中删除1.5百万行,以及完成此任务需要多长时间。

I dont want to delete all records from table.... I just want to delete all records which are fullfilling WHERE condition.

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

EDITED from a comment to an answer below.

编辑从评论到下面的答案。

"I fire the same query i.e. delete from table_name with Where Clause... Is it possible to Disable Indexing at the running Query, becuase Query is going on from past 20 hr.. Also help me out how i can disable Indexing.."

“我触发相同的查询,即从table_name中删除Where子句...是否可以在运行的查询中禁用索引,因为查询是从过去20小时开始的..还帮助我如何禁用索引..”

10 个解决方案

#1

If (and only if) you want to delete all of the records in a table, you can use DROP TABLE or TRUNCATE TABLE.

如果(并且仅当)要删除表中的所有记录,则可以使用DROP TABLE或TRUNCATE TABLE。

DELETE removes one record at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE is much faster because it doesn't record the activity in the transaction log. It removes all rows from a table, but the table structure & its columns, constraints, indexes and so on remain. DROP TABLE would remove those.

DELETE一次删除一条记录,并在事务日志中为每个已删除的行记录一个条目。 TRUNCATE TABLE更快,因为它不记录事务日志中的活动。它从表中删除所有行,但表结构及其列,约束,索引等仍然存在。 DROP TABLE会删除它们。

Use caution if you decide to TRUNCATE. It's irreversible (unless you have a backup).

如果您决定TRUNCATE,请小心。这是不可逆转的(除非你有备份)。

#2

create a second table, inserting all rows from the first that you don't want deleting.

创建第二个表,插入第一个不想删除的行。

delete the first table

删除第一个表

rename the second table to be the first

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

(or a variation on the above)

(或以上的变体)

This can often be quicker than doing a delete of selected records from a big table.

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

#3

You may want to try deleting in batches too. I just tested this on a table I have and the delete operation went from 13 seconds to 3 seconds.

您可能也想尝试批量删除。我刚刚在一张桌子上测试了这个,删除操作从13秒到3秒。

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

I don't think you can use the TOP predicate if you are running SQL2000, but it works with SQL2005 and up. If you are using SQL2000, then you can use this syntax instead:

如果您运行SQL2000,我认为您不能使用TOP谓词,但它适用于SQL2005及更高版本。如果您使用的是SQL2000,那么您可以使用以下语法:

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

#4

DELETE FROM table WHERE a=b;

When deleting that many rows you may want to disable the indexes so they don't get updated on every delete. Rewriting the indexes on every deletion will significantly slow down the whole process.

删除那么多行时,您可能希望禁用索引,以便在每次删除时都不会更新。在每次删除时重写索引将显着减慢整个过程。

You'll want to disable these indexes before beginning your deletion or else there may be table locks already in place.

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

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

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

If you wish to remove all entries in a table you can use TRUNCATE.

如果要删除表中的所有条目,可以使用TRUNCATE。

#5

Does the table you are deleting from have multiple foreign keys, or cascaded deletes or triggers? All of these will impact performance.

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

Depending on what you want to do and the transactional integrity, can you delete things in small batches e.g. if you are trying to delete 1.5 million records that is 1 years worth of data, can you do it 1 week at a time?

根据您的目的和交易完整性,您可以小批量删除内容,例如如果您要删除150万条1年的数据记录,您可以一次一周地完成吗?

#6

Delete from table where condition for those 1.5 million rows

从表中删除那些150万行的条件

The time depends.

时间取决于。

#7

On Oracle it is also possible to use

在Oracle上也可以使用

truncate table <table>

Not sure if that is standard SQL or available in SQL Server. It will however clear the whole table - but then it is quicker than "delete from " (it will also conduct a commit).

不确定这是标准SQL还是SQL Server中可用。然而,它将清除整个表 - 但它比“删除”更快(它也将进行提交)。

#8

TRUNCATE will also ignore any referential integrity or triggers on the table. DELETE FROM ... WHERE will respect both. The time will depend on the indexing of your condition columns, your hardware, and any additional system load.

TRUNCATE还将忽略表上的任何引用完整性或触发器。 DELETE FROM ...哪里都会尊重。时间取决于条件列,硬件和任何其他系统负载的索引。

#9

The delete SQL is exactly the same as a normal SQL delete

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

delete from table where [your condition ]

从表中删除[你的条件]

However if your worried about time then I'll assume your question is a little deeper than this. If your table is has a significant number of non-clustered indexes then in some circumstances it may be faster to drop all these indexes first and rebuild after the delete. This is unusual but in cases where your straightforward delete is vulnerable to timeout issues it may be helpful

但是,如果你担心时间,我会假设你的问题比这更深一些。如果您的表具有大量非聚集索引,那么在某些情况下,首先删除所有这些索引并在删除后重建可能会更快。这是不寻常的,但如果您的直接删除容易受到超时问题的影响,那么它可能会有所帮助

#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;

#1