为什么在SQL Server中使用游标被认为是不好的做法？

Why is it considered bad practice to use cursors in SQL Server?

我知道在SQL的7天中就有一些性能原因，但是在SQL Server 2005中是否仍然存在相同的问题？如果在存储过程中有一个要单独处理的结果集，则游标仍然是一个错误的选择吗？如果是这样，为什么？

因为游标占用了内存并创建了锁。

您真正要做的是尝试将基于集合的技术强制为非基于集合的功能。而且，公平地说，我应该指出游标确实有其用途，但是它们被皱眉了，因为许多不习惯使用基于集合的解决方案的人使用游标而不是弄清楚基于集合的解决方案。

但是，当您打开游标时，您基本上是在将这些行加载到内存中并锁定它们，从而创建潜在的块。然后，当您遍历游标时，您正在对其他表进行更改，并且仍保持游标的所有内存和锁处于打开状态。

所有这些都有可能导致其他用户的性能问题。

因此，通常来说，光标会皱眉。特别是如果这是解决问题的第一个解决方案。

以上关于SQL是基于集合的环境的评论都是正确的。但是，有时逐行操作很有用。考虑元数据和dynamic-sql的组合。

作为一个非常简单的示例，假设我在一个表中有100条记录，这些记录定义了要复制/截断/以其他方式表示的表的名称。哪个最好？对SQL进行硬编码以执行我需要做的事情？还是遍历此结果集并使用动态SQL(sp_executesql)来执行操作？

使用基于集合的SQL无法实现上述目标。

那么，要使用游标或while循环(伪光标)？

只要使用正确的选项，SQL游标就可以了：

INSENSITIVE将为您的结果集制作一个临时副本(使您不必为伪光标自己做这件事)。

READ_ONLY将确保基础结果集没有锁。基础结果集中的更改将反映在后续的访存中(就像从伪游标中获得TOP 1一样)。

FAST_FORWARD将创建优化的只读只读游标。

在将所有游标裁定为邪恶之前，请阅读有关可用选项的信息。

有一种关于游标的解决方法，我每次需要游标时都会使用。

我创建一个带有标识列的表变量。

将我需要使用的所有数据插入其中。

然后使用一个计数器变量创建一个while块，并使用带有标识列与计数器匹配的select语句从表变量中选择我想要的数据。

这样，我不会锁定任何东西，并使用更少的内存及其安全性，因为内存损坏或类似问题，我不会丢失任何东西。

并且易于查看和处理的块代码。

这是一个简单的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))

DECLARE @COUNT INT,
@MAX INT,
@CONCAT VARCHAR(MAX),
@COLUMN1 VARCHAR(10),
@COLUMN2 VARCHAR(10)

SET @COUNT = 1

INSERT INTO @TAB VALUES('TE1S', 'TE21')
INSERT INTO @TAB VALUES('TE1S', 'TE22')
INSERT INTO @TAB VALUES('TE1S', 'TE23')
INSERT INTO @TAB VALUES('TE1S', 'TE24')
INSERT INTO @TAB VALUES('TE1S', 'TE25')

SELECT @MAX = @@IDENTITY

WHILE @COUNT <= @MAX BEGIN
SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT

IF @CONCAT IS NULL BEGIN
SET @CONCAT = ''
END ELSE BEGIN
SET @CONCAT = @CONCAT + ','
END

SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2

SET @COUNT = @COUNT + 1
END

SELECT @CONCAT

我认为游标名称不好，因为SQL新手发现了游标，并认为"嘿，for循环！我知道如何使用它们！"然后他们继续将它们用于所有内容。

如果您将它们用于设计目的，那么我不会发现任何错误。

SQL是一种基于集合的语言，这才是它的最佳选择。

我认为游标仍然是一个错误的选择，除非您对游标有足够的了解，以证明在有限的情况下可以使用游标。

我不喜欢游标的另一个原因是清晰度。光标块非常丑陋，很难以清晰有效的方式使用它。

已经说过的话，在某些情况下游标确实是最好的-通常情况下，初学者通常不希望使用它们。

光标通常不是疾病，而是它的症状：不使用基于集合的方法(如其他答案所述)。

不了解此问题，只是简单地相信避免使用"邪恶的"游标就能解决问题，这会使情况变得更糟。

例如，用其他迭代代码(例如，将数据移动到临时表或表变量)替换游标迭代，以类似以下方式遍历行：

1	SELECT * FROM @temptable WHERE Id=@counter

或

1	SELECT TOP 1 * FROM @temptable WHERE Id>@lastId

这种方法，如另一个答案的代码所示，会使情况变得更糟，并且无法解决原始问题。这是一种称为"货物崇拜"编程的反模式：不知道为什么某些事情是不好的，因此要执行一些更糟的事情来避免这种情况！我最近将这样的代码(使用#temptable并且没有在identity / PK上的索引)更改回游标，并且对10000行以上的行进行更新仅用了1秒，而不是将近3分钟。仍然缺乏基于集合的方法(邪恶程度较小)，但那一刻我能做到的最好。

这种缺乏理解的另一个症状可能是我有时所说的"一个对象疾病"：通过数据访问层或对象关系映射器处理单个对象的数据库应用程序。通常的代码如下：

1
2
3
4
5

var items = new List<Item>();
foreach(int oneId in itemIds)
{
items.Add(dataAccess.GetItemById(oneId);
}

而不是

1	var items = dataAccess.GetItemsByIds(itemIds);

第一个通常会用大量的SELECT泛洪数据库，每个SELECT往返一次，尤其是当对象树/图形开始起作用并且臭名昭著的SELECT N 1问题出现时。

这是不理解关系数据库和基于集合的方法的应用程序端，就像使用过程数据库代码(如T-SQL或PL / SQL)时游标的方式一样！

@ Daniel P->您无需使用光标即可。您可以轻松地使用基于集合的理论来做到这一点。例如：使用Sql 2008

1
2
3
4
5
6

DECLARE @commandname NVARCHAR(1000) = '';

SELECT @commandname += 'truncate table ' + tablename + '; ';
FROM tableNames;

EXEC sp_executesql @commandname;

将简单地执行您上面所说的。并且您可以对Sql 2000进行相同的操作，但是查询的语法会有所不同。

但是，我的建议是尽可能避免使用游标。

Gayam

有时候，您需要执行的处理的性质要求使用游标，尽管出于性能原因，如果可能的话，最好使用基于集合的逻辑来编写操作。

使用游标，我不会称其为"坏习惯"，但是游标确实会消耗服务器上的更多资源(比基于等价的基于集合的方法要多)，而且经常不是不必要的。鉴于此，我的建议是在诉诸游标之前考虑其他选项。

有几种类型的游标(仅向前，静态，键集，动态)。每个都有不同的性能特征和相关的开销。确保为操作使用正确的光标类型。默认设置为"仅向前"。

使用游标的一个参数是当您需要处理和更新单个行时，尤其是对于没有良好唯一键的数据集。在这种情况下，可以在声明游标时使用FOR UPDATE子句，并使用UPDATE ... WHERE CURRENT OF。

处理更新。

请注意，"服务器端"游标曾经很流行(来自ODBC和OLE DB)，但是ADO.NET不支持它们，而AFAIK则永远不会。

在极少数情况下使用游标是合理的。几乎在任何情况下，它都不会胜过基于集合的关系型查询。有时候，程序员更容易从循环的angular进行思考，但是使用集合逻辑(例如，更新表中的大量行)将导致产生的解决方案不仅是更少的SQL代码行，但运行速度要快得多，通常要快几个数量级。

即使Sql Server 2005中的快进游标也无法与基于集合的查询竞争。与基于集合的性能相比，性能下降的图形通常看起来像是n ^ 2运算，随着数据集变得非常大，它趋向于更加线性。

光标确实有其位置，但是我认为主要是因为通常在单个select语句足以提供结果的汇总和过滤时使用它们。