MS SQL Server - CURSOR 什么时候好用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1479680/
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
MS SQL Server - When is a CURSOR good?
提问by jonathanpeppers
Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure.
很多时候，当我编写存储过程等时，我首先使用 CURSOR，后来发现我的过程存在一些性能问题。
Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same.
我读到的每件事都说 CURSORS 很糟糕，会导致不必要的锁定等，性能测试证明也是如此。
My question is when do you use a CURSOR and in what situations are they useful or good?
我的问题是你什么时候使用 CURSOR 以及它们在什么情况下有用或好？
If there is no use, why would they make such a bad control structure/type for SQL?
如果没有用，他们为什么要为 SQL 制作如此糟糕的控制结构/类型？
采纳答案by HLGEM
Normally they are to be avoided, but the feature is there for a reason and there are times to use them. I'd say 90+% of the cursors I've seen are not needed. If you are using them for CRUD operations, that can almost always be redone in a set-based fashion. I've often seen people use cursors for this because they don't know how to use joins in an update or delete or that they can use a select statment instead of a values clause in an insert. Another unnecessary use when people think they need them for slightly more complex processing that actually could easily be handled with a case statement.
通常应避免使用它们，但该功能存在是有原因的，有时会使用它们。我会说我见过的 90+% 的游标都是不需要的。如果您将它们用于 CRUD 操作，则几乎总是可以以基于集合的方式重做。我经常看到人们为此使用游标，因为他们不知道如何在更新或删除中使用连接，或者他们可以在插入中使用 select 语句而不是 values 子句。当人们认为他们需要它们进行稍微复杂的处理时，另一种不必要的使用，实际上可以通过 case 语句轻松处理。
Cursors are sometimes faster for calculating something like a running total.
光标有时会更快地计算诸如运行总数之类的东西。
Cursors are also handy for multiple executions of a stored proc that is set up to handle only one input value at a time. I do not use this feature for running user stored procs (unless I know I will be hitting a very small set of data) but it is very handy for database admins when needing to run system procs against multiple tables.
对于设置为一次仅处理一个输入值的存储过程的多次执行，游标也很方便。我不使用此功能来运行用户存储的 procs（除非我知道我将访问非常小的数据集）但是当需要针对多个表运行系统 procs 时，它对于数据库管理员来说非常方便。
If you are creating emails in SQl (not the best place to do it, but in some systems that's where they do it) and do not want the entire audience of the email to see the other people on the list or you want to personalize each email with information about the addressee, cursors are the way to go.
如果您在 SQl 中创建电子邮件（不是最好的地方，但在某些系统中是他们这样做的地方）并且不希望电子邮件的所有受众看到列表中的其他人，或者您想个性化每个人带有收件人信息的电子邮件，光标是要走的路。
Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems.
如果整个基于集合的插入/更新/删除将花费太长时间并锁定表，则还可以使用游标或循环来处理批量记录。这是游标和基于集合的解决方案之间的一种混合，并且通常是生产系统上进行大量更改的最佳解决方案。
回答by keithwarren7
I asked a guy on the SQL Server team one time, if you could add one feature that would make the product better for everyone what would it be?
有一次我问 SQL Server 团队的一个人，如果你可以添加一个功能来让每个人都更好地使用产品，那会是什么？
His response was 'Add? Huh, I would take one away. If you get rid of cursors you force programmers all over the world to start thinking about things in a SET based way and that will be the biggest world wide increase in DB performance you will ever see.'
他的回答是“添加？呵呵，我要带走一个。如果你摆脱了游标，你就会迫使全世界的程序员开始以基于 SET 的方式思考事情，这将是你所见过的世界范围内最大的数据库性能提升。
For my part however I tend to see a pattern, there seems to be a lot of procedural coders who use cursors because they need to be able to do an operation one element at a time and miss the old fashion WHILE loop concept. Same basic idea without the cursor overhead. Still not near as fast/effective as something SET based but 90% of the time when someone claims 'I cant do this set based, I have to use cursors' I can get them to do it with a while loop.
然而，就我而言，我倾向于看到一种模式，似乎有很多程序编码员使用游标，因为他们需要能够一次执行一个元素的操作，而错过了旧时尚的 WHILE 循环概念。没有游标开销的相同基本思想。仍然没有基于 SET 的东西那么快/有效，但是当有人声称“我不能基于这个集合，我必须使用游标”时，90% 的时间我可以让他们用 while 循环来做。
回答by RBarryYoung
Here's an article by a rather opinionated fellow, who gives reasoning for not using Cursors and some answers as to how they came to be: There Must be 15 Ways to Lose Your Cursors.
这是一个相当固执己见的人的文章，他给出了不使用游标的原因以及它们如何产生的一些答案：必须有 15 种方法可以丢失您的游标。
回答by GilaMonster
Only time I'll use them is when whatever is been done inside the cursor absolutely has to be done one item at a time and where whatever is been done inside the cursor takes so long that the overhead of the cursor fades into insignificance.
只有当在游标内所做的任何事情都必须一次完成一项时，我才会使用它们，而在游标内完成的任何事情都需要很长时间，以至于游标的开销变得无关紧要。
Eg database backups, integrity checks, index rebuilds. In short, admin tasks.
例如数据库备份、完整性检查、索引重建。简而言之，管理任务。
回答by womp
The MCTS prep manual for SQL Server 2008 that I'm studying recommends using external CLR code anywhere that a CURSOR would be required in T-SQL, especially now that SQL Server 2008 supports custom aggregate functions.
我正在研究的 SQL Server 2008 的 MCTS 准备手册建议在 T-SQL 中需要 CURSOR 的任何地方使用外部 CLR 代码，尤其是现在 SQL Server 2008 支持自定义聚合函数。
5 years ago, I worked with them for extensive reporting features, but I don't think I could come up with a good use case for them now. CLR aggregates and functions perform similarly to built-in aggregate functions.
5 年前，我与他们合作开发了广泛的报告功能，但我认为我现在无法为他们提出一个好的用例。CLR 聚合和函数的执行类似于内置聚合函数。
回答by Iconiu
OMG, how did I forget about Group By? I took the cursor based query you see below and replaced it with the one after it. Now I get a single result set so there are no issues with using sqlsrv_next_result() in php.
OMG，我怎么忘了Group By？我采用了您在下面看到的基于游标的查询，并将其替换为后面的查询。现在我得到了一个结果集，所以在 php 中使用 sqlsrv_next_result() 没有问题。
DECLARE @thisday datetime;
DECLARE daycursor CURSOR FOR
SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as thisday
FROM computerusedata
OPEN daycursor;
FETCH NEXT FROM daycursor
INTO @thisday;
WHILE @@FETCH_STATUS = 0
BEGIN
select distinct left(ComputerName,5) as CompGroup,DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day
FROM computerusedata
where DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) = @thisday
order by CompGroup;
FETCH NEXT FROM daycursor;
END;
CLOSE daycursor;
DEALLOCATE daycursor;";
select DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day,left(ComputerName,5) as CompGroup
from ComputerUseData
group by DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)),left(ComputerName,5)
order by day,CompGroup
回答by dance2die
You use cursor for rebuilding or reorganizing table indexes individually
unless there is a way of running ALTER INDEX...as a set-based operation.
除非有一种方法可以将ALTER INDEX...作为基于集合的操作运行，否则您可以使用游标来单独重建或重组表索引。
回答by Dane
Cursors are useful when 1) you need to do something that you cannot do with a set operation, or 2) it doesn't make sense to do the same work by making iterative calls from the application layer. Or sometimes you have a procedure that must remain on the database layer, and you simply can't break back out to the app layer midstream to iterate over some result set.
在以下情况下，游标很有用：1) 您需要执行 set 操作无法执行的操作，或 2) 通过从应用程序层进行迭代调用来执行相同的工作没有意义。或者有时您有一个必须保留在数据库层上的过程，并且您根本无法在中游返回到应用程序层以迭代某些结果集。
One recommendation I would make though, is that people use cursor variables rather than normal cursors, because you avoid the cursor allocation/deallocation issues that surround normal cursors. With a normal cursor, if you don't deallocate them they persist, which can be a source of memory leaks. Not so with variable-based cursors (i.e. DECLARE @cursor CURSOR).
不过，我要提出的一个建议是，人们使用游标变量而不是普通游标，因为您可以避免围绕普通游标的游标分配/解除分配问题。使用普通游标，如果您不释放它们，它们会持续存在，这可能是内存泄漏的来源。对于基于变量的游标（即 DECLARE @cursor CURSOR），情况并非如此。
The bottom line is, avoid them if you possibly can, and if you can't, use them minimally and wisely.
最重要的是，如果可能，请避免使用它们，如果不能，则尽量少且明智地使用它们。
回答by Matt Wrock
I don't typically use cursors but when I do, it must be a "one-off" query that I'm running locally or a daily job. You want to refrain from having production code call a cursor that would be invoked frequently like in response to a web request.
我通常不使用游标，但是当我使用游标时，它必须是我在本地或日常工作中运行的“一次性”查询。您希望避免让生产代码调用会像响应 Web 请求一样频繁调用的游标。
