Transact-SQL游标是如何工作的

我们用下面的例子来示范游标是如何工作的。这个游标将从sysobjects表中取出各行。我们看到，只要游标的读取数据标志等于0，就说明还有可用的数据，那么游标就继续读取数据并把读到的数据放入一个变量。这样每个用户表的表名就都被放入了表名变量中。然后我们看到了结果：各个表的表名都被列了出来。

 

隐式游标转换

 

下面我们将谈论一些关于游标类型转换方面的问题。我们将讨论不同的游标类型，以及不同游标类型的特点，例如static型游标在tempdb数据库中需要占用非常大的空间，而Keyset型则不需要占用如此多的空间。理解各种类型之间的转换非常重要。因为当你选择不同的游标类型时，它们在tempdb数据库中所占的空间相差可能会特别大。

例如，假如一个游标使用top子句引用了某个视图，它马上就变成了static型。如果一个查询包含有distinct、group by之类的参数，也将立即转换成static型。如果一个查询引用了一个带有触发器的表，同样将变成静态型。而如果一个查询因为使用了order-by子句产生了内部的工作表，或者引用了远程服务器或链接服务器上的表，将产生一个keyset型的游标。

有以下几种主要的不同游标类型：dynamic、forward、static和keyset。Dynamic和forward有一些不同，但从空间利用的角度来说，它们和static类似。Static型游标需要占用tempdb数据库中的大量空间，是因为实际上它包含了整个行。我们一会儿将在脚本中解释这一点。而keyset型游标则只包含了组成某一个特定游标的一些键。在实施中Dynamic和forward only型游标的主要区别在于，dynamic型游标能够反映基础数据的更多改动。我们也将在脚本中解释这一点。

 

游标的选择

 

我们来谈一下游标的选择。选择游标的类型依赖于以下几个我们将要谈到的因素。首先是结果集的大小。我已经提到过不止一次，不同的游标类型需要向tempdb数据库中写入不同的信息。正因为如此，需要的空间以及写入信息所需要的时间就是必须要考虑的因素了。

其次是返回的结果集中有可能真正用到的数据。考虑你是否需要反映基础数据的变化。还有打开游标的性能，例如static和keyset之间的差别。这主要依赖于游标试图去取得的数据量的大小。

还有对于其它游标操作的考虑，例如滚动和定点更新。这是因为不同的游标类型支持不同的选项。

我们现在切换到示范计算机。我们将运行一些脚本，这将帮助我们理解以上要点。

我们需要打开两个脚本。第一个是游标成员，我们先运行这段脚本的一部分，用来设置一个数据库并向其中插入一些行。

我们先看看forward类型的游标。我们打开一个游标，然后向数据库中插入几个新行，接着用print语句查看我们从数据库中所取得的数据，看新插入的行是否可以被检索到。

运行这段脚本。首先是声明游标并取得第一行。当游标已经打开后，向表中插入一个新行。然后继续运行，取得下面的三行。我们看到，新插入的行被检索出来了。如果我们更新了一个主键列，然后使用fetch命令取出这一列，是可以看到刚才所作的修改的。这就是forward-only型游标。然而，如果你更新的不是主键列，而且你对非主键列的修改是你对该行的唯一改动，你将看不到你对非主键列的更新。这是关于forward-only型游标需要注意的一点。

现在我们来看另外一种类型的游标，即static-oly型。这一段设置脚本和刚才我们所看到的是类似的。我们来运行它。这将执行一个static型游标。我们重新创建一个有3行数据的表，和刚才我们所创建的一样。我们用fetch命令，取得表中的第一行数据。然后向表中插入一行新的数据。然后同刚才一样，继续运行，取得下面的三行。我们看到，没有取出新插入的行。Static型游标不能反映数据库基础数据的变化。

下面要讨论的是keyset游标。同样，我们创建一个keyset型游标，重新创建表，用fetch命令取得表中的第一行。然后向表中插入新的记录，再用fetch命令取出新插入的那条记录，看看发生了什么。我们注意到，没有取出来。但是，如果我们更新的是我们已经取出过的行，即第1、2、3行，然后把游标位置返回第一行来重新取出刚才被修改过的行，情况就不一样了。把C2列的值由5修改成3，现在用fetch命令重新取出这一行，我们确实可以看到刚才我们所作的修改了。如果我们从游标中删除一行，比如这儿我们删除C1=3的那一行，然后试图去取出那一行的话，我们可以看到这一行已经被删除，或者说被清空为零值。所以，通过keyset型游标，可以反映出对游标在键集中那一部分的更新和删除操作。

最后我们要讨论的是dynamic型游标。我们重新创建那个表，声明游标，取出第一行，然后向表中插入一行。然后试图去取出刚才插入的那一行，没有问题，可以成功取出。如果像刚才在keyset型游标中讨论的那样，我们更新基础表的数据，然后返回游标，也可以看到我们刚才所作的更新。如果我们删除一行然后试图用游标去取出这一行的话，我们可以看到它已经确实不存在了，而不是显示为空或者零。因为这里我们删除了第三行，游标将直接返回第四行的数据。

 

现在我们将打开另外一个脚本，用来演示游标类型对tempdb数据库以及空间分配上的影响。打开这个脚本文件，第一部分已经运行了，创建了一个10,000行的表。脚本的下一部分演示了在使用keyset型游标的情况下tempdb数据库的大小。我们将打开一个游标，并在游标打开前和打开后分别观察tempdb数据库中扩展页的分配情况。运行，系统显示在打开游标前tempdb数据库中使用的扩展页是29。等待游标初始化完成，我们看到游标打开后使用的扩展页增加到了34。这个增加量并不算大。如果我们打开的是一个static型游标，tempdb数据库中空间使用就会完全不一样了。现在我们来打开一个static型游标，打开前使用的扩展页是28。等待游标初始化完成。这比刚才初始化keyset型游标所花的时间要长一些，因为现在是在初始化整个行，而不是像刚才那样只初始化键集。

现在我们看看使用的扩展页，这个数值跳到了1163，和刚才使用keyset型游标相比差别非常大。现在应该对static型游标在tempdb数据库中所需要的空间有一个初步概念了吧，使用static型游标的时候应该注意这一点。

如果我们打开dynamic型游标观察它使用空间的情况的话，它所需要的空间是最少的。因为dynamic型游标不会像keyset型游标那样去初始化键集，也不会像static型游标那样初始化整个行。然而，dynamic型游标需要跟踪其它连接以检测数据是否被修改，所以需要额外的开销。正如我们前面所看到的，dynamic型游标能够反映基础数据的变化。

 

最后我们来讨论一些关于游标类型转换方面的问题。创建另外一个数据库，并在其中创建一个非常简单的表。然后我们打开游标，然后使用存储过程sp_show_cursor_details来显示游标的有关信息。现在我们打开一个dynamic型游标，察看一下这个游标的状态。状态值是3，表示现在是一个dynamic型游标。

现在我们设置游标，带上top子句引用一个视图。注意我们试图打开一个dynamic型游标。我们运行这个游标，然后我们注意到状态值变成了1，表示已经从dynamic型游标转换成static型游标了。从刚才不同游标类型所需要的空间来看，static型游标所需要的空间非常大。因此在这种情况下dynamic型游标会自动转换成static型，这是应该注意的一点。

现在我们看一看select语句中带有distinct子句的游标。同样试图以dynamic型打开，我们注意到它又自动转换成了static型。如果你没有注意到这样的类型转换，可能会给你的应用程序带来性能上的问题。

另外，我们来看看会产生内部工作表的查询会是什么情况。同样以dynamic型游标打开，运行它。正如我们所看到的，状态值变成了2，意味着已经转换成keyset型游标了。这是因为使用了内部工作表，这个查询有order-by参数，按C2列的值进行了排序。