使用 WHILE 循环的 SQL Server 批量删除不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15558073/
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 batch delete using WHILE loop does not work
提问by JCB
I have a very large table, so I am using the following to delete older entries:
我有一个非常大的表，所以我使用以下内容来删除旧条目：
WHILE (@@ROWCOUNT > 0)
BEGIN
DELETE TOP (5000) FROM myTable
WHERE date < 20130103
END
I have run this a few times using different dates. Sometimes it works fine (takes about 20 minutes), but other times the query finishes immediately and nothing has been deleted. When that happens, I just do a simple SELECT statement from that table, and then try the above WHILE statement again, and then it works! Anyone know why this is? I need to automate this query to run on a regular basis to control the table size, but I want to make sure it actually deletes properly when it runs. Thank you.
我已经使用不同的日期运行了几次。有时它工作正常（大约需要 20 分钟），但有时查询会立即完成并且没有删除任何内容。发生这种情况时，我只是从该表中执行一个简单的 SELECT 语句，然后再次尝试上面的 WHILE 语句，然后它就起作用了！有谁知道这是为什么？我需要自动执行此查询以定期运行以控制表大小，但我想确保它在运行时实际正确删除。谢谢你。
采纳答案by Gordon Linoff
Presumably, the reason is because @@ROWCOUNTis initialized to a value of 0.
想必，原因是因为@@ROWCOUNT被初始化为0的值。
You could run this query first to set it:
您可以先运行此查询来设置它：
select count(*) from myTable where date < 20130103
This would add a little bit of time to your query, but you would see the number of rows being deleted.
这会为您的查询增加一点时间，但您会看到被删除的行数。
You could also do something like:
您还可以执行以下操作：
select top 1 * from myTable
which would go much faster.
这会快得多。
回答by Michael Fredrickson
What are you running before this block of code? @@ROWCOUNTwill be set to whatever statement proceeded it.. if you run some other command beforehand, it could be 0.
你在这块代码之前运行的是什么？ @@ROWCOUNT将设置为执行它的任何语句.. 如果您事先运行其他一些命令，它可能是0.
Instead, you could force the initial count to be 1:
相反，您可以强制初始计数为1：
DECLARE @Rows INT
SET @Rows = 1
WHILE (@Rows > 0)
BEGIN
DELETE TOP (5000) FROM myTable
WHERE date < 20130103
SET @Rows = @@ROWCOUNT
END
回答by Blorgbeard is out
It's because sometimes @@ROWCOUNTis zero to start with - so the whileloop never executes, because it checks the condition beforeevery execution, including the first one.
这是因为有时@@ROWCOUNT从零开始 - 所以while循环永远不会执行，因为它在每次执行之前检查条件，包括第一个。
Here's a homemade do-whileloop, since SQL Server doesn't have one built in.
这是一个自制的do-while循环，因为 SQL Server 没有内置循环。
loop:
DELETE TOP (5000) FROM myTable
WHERE date < 20130103
if @@ROWCOUNT > 0 goto loop
回答by Eduardo Gelvis
When I delete in batch I add a WAITFOR DELAY (at least 1 or 2 seconds). Also the first statement I created outside the loop. Btw, avoid using ROWCOUNT as delimiter (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017). There are the two options:
当我批量删除时，我添加了 WAITFOR DELAY（至少 1 或 2 秒）。也是我在循环外创建的第一条语句。顺便说一句，避免使用 ROWCOUNT 作为分隔符（https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017）。有两个选项：
DECLARE @BatchSize BIGINT = 50000
SET ROWCOUNT @BatchSize
DELETE
FROM myTable
WHERE
date < 20130103
WHILE (@@ROWCOUNT > 0)
BEGIN
WAITFOR DELAY '00:00:02'
DELETE
FROM myTable
WHERE
date < 20130103
END
Or
或者
DECLARE @BatchSize BIGINT = 50000
WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:02'
DELETE TOP(@BatchSize )
FROM myTable
WHERE
date < 20130103
IF @@ROWCOUNT < @BatchSize
Break
END
回答by Ali Osman Yavuz
Basically,
基本上，
SELECT 0 -- rowcount is 1
WHILE (@@ROWCOUNT > 0)
BEGIN
DELETE TOP (5000) FROM myTable
WHERE date < 20130103
END
Or
或者
SET ROWCOUNT 5000 -- set row count to 5000
SELECT 0 -- rowcount is 1
WHILE (@@ROWCOUNT > 0)
BEGIN
DELETE FROM myTable
WHERE date < 20130103
END
SET ROWCOUNT 0 -- set rowcount to unlimited
回答by Jim Clouse
You could also write your query this way:
你也可以这样写你的查询：
SET ROWCOUNT 5000; -- set batch size
WHILE EXISTS (SELECT 1 FROM myTable WHERE date < '2013-01-03')
BEGIN
DELETE FROM myTable
WHERE date < '2013-01-03'
END;
SET ROWCOUNT 0; -- set batch size back to "no limit"
Either way, you should format your date strings properly.
无论哪种方式，您都应该正确格式化日期字符串。
Just be sure your delete criteria and the statement in your exists clause are identical or you could encounter an infinite loop.
请确保您的删除条件和您的exists 子句中的语句相同，否则您可能会遇到无限循环。
