SQL Server 2000 删除顶部 (1000)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1925228/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2000 Delete Top (1000)
提问by Kevin
I have a large SQL Server database with a table at about 45 million records. I am archiving this table, and need to remove all entries greater than two years ago. I have the inserting into my archive table working fine, but I'm having issues with efficiency when deleting.
我有一个包含大约 4500 万条记录的表的大型 SQL Server 数据库。我正在归档此表，需要删除两年前的所有条目。我在我的存档表中插入工作正常，但在删除时我遇到了效率问题。
My problem lies within the indexes currently on the table. I would like to delete (and archival insert) in 1000 record chunks. To do this, I need to determine the "top" 1000 records fulfilling the requirement (greater than two years old). The DateTime stamp on the row is a clustered index, so this is great for grabbing the rows. However SQL 2000 does not allow DELETE TOP 1000.... so I need to do something like:
我的问题在于当前表上的索引。我想在 1000 个记录块中删除（和档案插入）。为此，我需要确定满足要求的“前”1000 条记录（超过两年）。行上的 DateTime 标记是聚集索引，因此这非常适合抓取行。但是 SQL 2000 不允许 DELETE TOP 1000 .... 所以我需要做一些类似的事情：
DELETE FROM <table> WHERE [UniqueID] IN
(SELECT TOP 1000 [UniqueID] FROM <table> WHERE [DateTime] < @TwoYearsAgo)
This would work great, if UniqueID was indexed. Since it is not, this takes a very long time (it is scanning the table for each of the 1000 records to be deleted). There are no other indexes on the table that uniquely identify the records. I am told it would be too costly to compute an index on UniqueID, as this is a live DB. Can anyone point out a way to optimize this query?
如果将 UniqueID 编入索引，这会很好用。由于它不是，这需要很长时间（它正在扫描表以查找要删除的 1000 条记录中的每一条）。表上没有其他唯一标识记录的索引。有人告诉我，在 UniqueID 上计算索引成本太高，因为这是一个实时数据库。谁能指出优化此查询的方法？
回答by Lasse V. Karlsen
How about rewriting the query?
重写查询怎么样？
SET ROWCOUNT 1000
DELETE FROM <table> WHERE [DateTime] < @TwoYearsAgo
See documentation on SET ROWCOUNT (Transact-SQL).
请参阅有关SET ROWCOUNT (Transact-SQL) 的文档。
Also note that per the documentation for DELETE, it supports the TOPclause, but that is apparently new for SQL Server 2005 and up. I'm saying this since it sounds like it isn't supported on your database server, but have you actually tried using it? I don't have access to SQL Server 2000 documentation so I'm unsure if it is supported on that version. It very well might not be.
另请注意，根据DELETE的文档，它支持该TOP子句，但这对于 SQL Server 2005 及更高版本显然是新的。我这么说是因为听起来您的数据库服务器不支持它，但您是否真的尝试过使用它？我无权访问 SQL Server 2000 文档，因此我不确定该版本是否支持它。很可能不是。
DELETE TOP (1000) FROM <table> WHERE [DateTime] < @TwoYearsAgo
Note the difference from the way TOP on select canbe written, without the parenthesis. For UPDATE, DELETE and INSERT, the expression must be parenthesized, even if it's only a constant number like above.
注意从选择的方式TOP的差别可以被写，没有括号。对于 UPDATE、DELETE 和 INSERT，表达式必须用括号括起来，即使它只是一个像上面一样的常数。
回答by Remus Rusanu
You can delete a subquery:
您可以删除子查询：
DELETE <table> FROM (
SELECT TOP 1000 *
FROM <table>
WHERE [DateTime] < @TwoYearsAgo);
See the example E: at SQL 2000 DELETE Syntax. This is recommended over the SET ROWCOUNT approach. In SQL 2005 and later you can specify directly the TOP in DELETE.
请参见示例 E: 在SQL 2000 DELETE Syntax 中。建议使用 SET ROWCOUNT 方法。在 SQL 2005 及更高版本中，您可以直接在 DELETE 中指定 TOP。
回答by Paul Creasey
you can also do
你也可以这样做
DELETE TOP(1000) FROM <table> WHERE [DateTime] < @TwoYearsAgo
God only knows why they use top(x) for delete and top x for select, most people don't even seem to know about this feature!
天知道为什么他们使用 top(x) 进行删除和使用 top x 进行选择，大多数人甚至似乎都不知道这个功能！
edit: Apparently its 2005+ so you should probably ignore this.
编辑：显然是 2005+，所以你应该忽略这一点。
回答by AdaTheDev
You could use SET ROWCOUNT:
您可以使用SET ROWCOUNT：
SET ROWCOUNT 1000
DELETE FROM <table> WHERE [DateTime] < @TwoYearsAgo
回答by onupdatecascade
I had to do something similar a while back -- make lightweight insert and delete to move old records to an archive table. Although counterintuitive, the fastest and least impactful solution I found was:
不久前我不得不做类似的事情——进行轻量级的插入和删除，以将旧记录移动到存档表中。尽管违反直觉，但我发现的最快且影响最小的解决方案是：
Make a small #temp table with the values of IDs for the top (x) rows. If ID really can't be indexed in your scenario, you might use date AND ID instead, so the combination of the two can use an index.
begin tran
Insert into archive table where ID and DATE in ( #temp )
Delete from main table where ID and DATE in ( #temp )
commit
Truncate #temp
Repeat
使用顶部 (x) 行的 ID 值制作一个小的 #temp 表。如果在您的场景中确实无法对 ID 进行索引，则可以改用 date AND ID，这样两者的组合就可以使用索引。
开始传输
插入存档表，其中 ID 和 DATE 位于（#temp）中
从主表中删除 ID 和 DATE（#temp）
犯罪
截断#temp
重复
Having the temp table to stage the row identifiers is more total work than a straight delete, but makes the process very lightweight in cases where you want to just chip away a little at a time without blocking.
使用临时表来暂存行标识符比直接删除要完成更多的工作，但是在您希望一次只删除一点而不阻塞的情况下，该过程非常轻量级。
Also I agree with Lasse - can't see the point of a unique id with no index, and therefore no constraint, to enforce it.
我也同意 Lasse - 看不到没有索引的唯一 id 的意义，因此没有约束，强制执行它。
回答by hongliang
I wonder whether you must stick with the 1000 record chunk requirement. If it is there for the reason of server load and kind of arbitrary, you may want to try the following, since you already have a clustered index on [DateTime]:
我想知道您是否必须坚持 1000 条记录块的要求。如果它是由于服务器负载和任意类型的原因而存在的，您可能想尝试以下操作，因为您已经在 [DateTime] 上有一个聚集索引：
DELETE FROM <table>
WHERE [DateTime] < @TwoYearsAgo
and [DateTime] < (select dateadd(day, 1, min([DateTime])) from <table>)
回答by Shrikant Wakodkar
For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency with its required use in INSERT, UPDATE, MERGE, and DELETEstatements in which the parentheses are required.
为了向后兼容，括号在 SELECT 语句中是可选的。我们建议您始终使用括号TOP的SELECT语句的一致性，在其要求使用INSERT，UPDATE，MERGE，和DELETE语句所需要的括号。
USE AdventureWorks;
GO
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20120701';
GO
