游标sql server

介绍 (Intro)

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

在关系数据库中，对一组行进行操作。 例如，SELECT语句返回一组行，这称为结果集。 有时，应用程序逻辑需要一次处理一行，而不是一次处理整个结果集。 在T-SQL中，一种实现方法是使用CURSOR。

If you possess programming skills, you would probably use a loop like FOR or WHILE to iterate through one item at a time, do something with the data and the job is done. In T-SQL, a CURSOR is a similar approach, and might be preferred because it follows the same logic. But be advised, take this path and trouble may follow.

如果您具有编程技能，则可能会使用FOR或WHILE之类的循环一次遍历一个项目，对数据做一些事情，然后工作就完成了。 在T-SQL中，CURSOR是类似的方法，由于它遵循相同的逻辑，因此可能是首选方法。 但请注意，采取这种方法可能会遇到麻烦。

There are some cases, when using CURSOR doesn’t make that much of a mess, but generally they should be avoided. Below, we will show some examples where using a CURSOR creates performance issues and we will see that the same job can be done in many other ways.

在某些情况下，使用CURSOR不会造成太多混乱，但通常应避免使用它们。 下面，我们将显示一些示例，其中使用CURSOR会导致性能问题，并且我们将看到可以以许多其他方式完成相同的工作。

For the purpose of this demonstration we will use AdventureWorks2012 database, and let’s say we want to get some data from [Production].[ProductInventory] table, for every product that requires less than a day to manufacture, that is from table [Production].[Product].

出于此演示的目的，我们将使用AdventureWorks2012数据库，假设我们要从[Production]。[ProductInventory]表中获取一些数据，对于需要少于一天生产的每种产品，即从表[Production] 。[产品]。

游标示例 (A cursor example)

Let’s start by using a CURSOR, and write the following syntax:

让我们从使用CURSOR开始，并编写以下语法：

 USE AdventureWorks2012GODECLARE @id int  DECLARE cursorT CURSOR--LOCAL STATIC--LOCAL FAST_FORWARD--LOCAL READ_ONLY FORWARD_ONLYFORSELECT ProductIdFROM AdventureWorks2012.Production.ProductWHERE DaysToManufacture <= 1 OPEN cursorT FETCH NEXT FROM cursorT INTO @idWHILE @@FETCH_STATUS = 0BEGIN          SELECT * FROM Production.ProductInventory          WHERE ProductID=@id          FETCH NEXT FROM cursorT INTO @idENDCLOSE cursorT DEALLOCATE cursorT  

After a short coffee break, the query finished executing, returning 833 rows in the time shown below. It’s important to mention the chosen syntaxes above are only for demo purposes, and I made no index tuning to speed things up.

短暂的咖啡休息后，查询完成执行，在下面显示的时间中返回833行。 重要的是要提到上面选择的语法仅用于演示目的，并且我没有进行索引调整来加快速度。

In the cursor execution, we have two steps. Step one, the positioning, when the cursor sets its position to a row from the result set. Step two, the retrieval, when it gets the data from that specific row in an operation called the FETCH.

在游标执行中，我们有两个步骤。 第一步，定位，当光标将其位置从结果集中移到一行时。 第二步，检索，当它从称为FETCH的操作中从特定行中获取数据时。

In our example, the cursor sets its position to the first row returned by the first SELECT and fetches the ProductID value that matches WHERE condition in @id variable. Then the second SELECT uses the variable value to get data from [Production].[ProductInventory] and the next row is fetched. These operations are repeated until there are no more rows to work with.

在我们的示例中，游标将其位置设置为第一个SELECT返回的第一行，并在@id变量中获取与WHERE条件匹配的ProductID值。 然后，第二个SELECT使用变量值从[Production]。[ProductInventory]中获取数据，并提取下一行。 重复这些操作，直到没有更多行可以使用为止。

Finally, CLOSE syntax releases the current result set and removes the locks from the rows used by the cursor, and DEALLOCATE removes cursor reference.

最后，CLOSE语法释放当前结果集并从游标使用的行中删除锁，而DEALLOCATE则删除游标引用。

Our demo tables are relative small containing roughly 1,000 and 500 rows. If we had to loop through tables with millions of rows it would last a considerable amount of time and the results would not please us.

我们的演示表相对较小，大约包含1,000和500行。 如果我们必须遍历具有数百万行的表，则将花费相当长的时间，而结果将使我们不满意。

Let’s give our cursor another chance and uncomment the line –LOCAL STATIC. There are many arguments we can use in a cursor definition, more on that on this link , but for now let’s focus on what this two words mean.

让我们再给光标一个机会，取消注释–LOCAL STATIC行。 我们可以在游标定义中使用许多参数，更多有关此链接的 ，但现在让我们集中讨论这两个词的含义。

When we specify LOCAL keyword, the scope of the cursor is local to the batch in which it was created and it is valid only in this scope. After the batch finishes executing, the cursor is automatically deallocated. Also, the cursor can be referenced by a stored procedure, trigger or by a local cursor variable in a batch.

当我们指定LOCAL关键字时，游标的作用域在创建它的批次中是局部的，并且仅在该作用域中有效。 批处理执行完毕后，将自动释放游标。 同样，可以通过存储过程，触发器或批处理中的本地游标变量来引用游标。

The STATIC keyword makes a temporary copy of the data used by the cursor in tempdb in a temporary table. Here we have some gotchas. A STATIC cursor is read-only and is also referred to as a snapshot cursor because it only works with the data from the time it was opened, meaning that it won’t display any changes made in database on the set of data used by the cursor. Basically, no updates, deletes or inserts made after the cursor was open will be visible in the cursors result set unless we close and reopen the cursor.

STATIC关键字对临时表中tempdb中的游标使用的数据进行临时复制。 在这里，我们有一些陷阱。 STATIC游标是只读的，也称为快照游标，因为它仅从打开时就对数据起作用，这意味着它不会在数据库所使用的数据集上显示对数据库所做的任何更改。光标。 基本上，除非打开并重新打开游标，否则在游标结果集中将看不到任何更新，删除或插入操作。

Be aware of this before using these arguments and check if it matches your needs. But let’s see if our cursor is faster. Below we have the results:

在使用这些参数之前，请注意这一点，并检查其是否符合您的需求。 但是，让我们看看我们的光标是否更快。 下面是结果：

12 seconds are a lot better that 4 minutes and 47 seconds, but keep in mind the restrictions explained above.

12秒比4分47秒要好得多，但请记住上述限制。

If we run the syntax using this argument LOCAL READ_ONLY FORWARD_ONLY we get the same results. READ_ONLY FORWARD_ONLY cursor can be scrolled only from the first row to the last one. If the STATIC keyword is missing, the cursor is dynamic and uses a dynamic plan if available. But there are cases when a dynamic plan is worse than a static one.

如果使用此参数LOCAL READ_ONLY FORWARD_ONLY运行语法，我们将得到相同的结果。 READ_ONLY FORWARD_ONLY光标只能从第一行滚动到最后一行。 如果缺少STATIC关键字，则光标是动态的，并使用动态计划（如果有）。 但是在某些情况下，动态计划比静态计划更糟糕。

What happens when we uncomment –LOCAL FAST_FORWARD?

当我们取消注释–LOCAL FAST_FORWARD时会发生什么？

FAST_FORWARD is equivalent to READ_ONLY and FORWARD_ONLY cursor but has the ability to choose the better plan from either a static or a dynamic one.

FAST_FORWARD等效于READ_ONLY和FORWARD_ONLY游标，但是能够从静态或动态计划中选择更好的计划。

LOCAL FAST_FORWARD seems to be the best choice because of its flexibility to choose between a static or dynamic plan, but FORWARD_ONLY also does the job very good. Although we got the result in an average of 12 seconds using both of this methods, these arguments should be properly tested before choosing one of them.

LOCAL FAST_FORWARD似乎是最佳选择，因为它可以在静态或动态计划之间灵活选择，但是FORWARD_ONLY的工作也非常出色。 尽管使用这两种方法平均可以在12秒内得到结果，但是在选择其中一种参数之前，应正确测试这些参数。

There are many ways of obtaining the same result, much quicker and with less impact on performance.

有很多方法可以更快地获得相同的结果，并且对性能的影响较小。

游标替代 (Cursor alternative)

One method is using a JOIN, and as we can see next, the results are considerable better.

一种方法是使用JOIN，然后我们可以看到，结果要好得多。

First, we have to enable statistics time to measure SQL Server execution time, and make an INNER JOIN between two tables, [Production].[ProductInventory] and [Production].[Product] on ProductID column. After hitting the EXECUTE button, we produced the same results in 330 ms, compared to 04:47 time from the cursor method, and we have a smile on our face.

首先，我们必须启用统计时间来度量SQL Server执行时间，并在两个表（ProductID列的[Production]。[ProductInventory]和[Production]。[Product]）之间建立INNER JOIN。 按下EXECUTE按钮后，我们在330毫秒内得到了相同的结果，而使用光标方法的时间是04:47，相比之下，我们脸上露出了微笑。

 USE AdventureWorks2012GO SET STATISTICS TIME ONSELECT * FROM Production.ProductInventory as pinvINNER JOIN Production.Product as ppON pinv.ProductID=pp.ProductIDWHERE pp.DaysToManufacture <= 1 

We do not need to iterate through every row to get what we need, we have no more loops, no while clause, no iterations, we are working with sets of data instead, getting what we want faster and writing less code.

我们不需要遍历每一行来获得所需的内容，我们没有更多的循环，没有while子句，没有迭代，我们正在处理数据集，从而更快地获取我们想要的东西并减少编写代码。

游标的适当使用 (An appropriate use of cursor )

Now that we’ve seen how much damage a cursor can do, let’s see an example where we can make use of it.

既然我们已经了解了游标可以造成多大的损害，下面让我们来看一个可以使用它的示例。

Let’s assume we want to select the size and number of rows for only certain tables from a database. To achieve this, we will get all table names based on criteria from information_schema.tables and using a CURSOR we will loop through each of that table name and execute the stored procedure sp_spaceused by passing one table name at a time to get the information we need.

假设我们只想为数据库中的某些表选择行的大小和行数。 为了实现这一点，我们将基于基于information_schema.tables中的条件的所有表名，并使用CURSOR遍历每个表名，并通过一次传递一个表名来获取所需信息，从而执行存储过程sp_spaceused 。 。

We will use the same AdventureWorks2012 database, and get all tables from Sales schema that contains the name ‘Sales‘. For every table name returned, we want to see all the info from information_schema.tables.

我们将使用相同的AdventureWorks2012数据库，并从Sales模式获取包含名称“ Sales ”的所有表。 对于返回的每个表名，我们希望查看来自information_schema.tables的所有信息 。

Below we have the T-SQL syntax and the obtained results:

下面我们有T-SQL语法和获得的结果：

 USE AdventureWorks2012GODECLARE @TableName VARCHAR(50)  -- table name from 'Sales' schemaDECLARE @Param VARCHAR(50)           -- parameter for 'sp_spaceused' procedure DECLARE db_cursor CURSOR FOR  --select only 'Sales' tablesSELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME like '%Sales%' and TABLE_TYPE='BASE TABLE'     OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @TableName   WHILE @@FETCH_STATUS = 0  BEGIN      --concatenate each table name in a variable and pass it to the stored procedure     SET @Param='Sales.'+@TableName     --execute stored procedure for every table name at a time                EXEC sp_spaceused @Param                          FETCH NEXT FROM db_cursor INTO @TableName  --gets the next table nameEND   CLOSE db_cursor  DEALLOCATE db_cursor 

This is one method where CURSOR is helpful by iterating through some data one row at a time and gets the result needed. In this particular case, the cursor gets the job done without having implications on performance and is easy to use.

这是一种CURSOR有用的方法，它可以一次迭代一行数据并获得所需的结果。 在这种特殊情况下，游标可以完成工作而不会影响性能，并且易于使用。

结论 (Conclusions)

There we have it. We showed some examples with the good, the bad and the ugly when using cursors. In most cases, we can use JOINS, even WHILE clauses, SSIS packages or other alternative methods to get the same result quicker, with less impact on performance output and even writing fewer lines of syntax.

在那里，我们有它。 我们展示了一些使用游标时的好，坏和丑陋的示例。 在大多数情况下，我们可以使用JOINS，甚至WHILE子句，SSIS包或其他替代方法来更快地获得相同的结果，而对性能输出的影响较小，甚至可以编写更少的语法。

When we are dealing with OLTP environment and large sets of data to process, the word ‘CURSOR’ should not be spoken.

当我们处理OLTP环境和大量数据要处理时，不应说出“ CURSOR”一词。

In SQL, it’s a good practice to think at making operations on sets of data, rather than think in a programmatic way, using iterations or loops, because this kind of approach is not recommended nor intended for this use. Trying to use loops like FOR or FOREACH from programming languages and associate that logic with SQL operations, is an obstacle for getting the right solution to our needs. We have to think at set-based operations rather than one row at a time to get the data we need.

在SQL中，最好不要使用迭代或循环来对数据集进行操作，而应以编程方式进行思考，因为不建议也不要将这种方法用于这种用途。 尝试使用编程语言中的FOR或FOREACH之类的循环并将该逻辑与SQL操作相关联，这是获得正确解决方案以满足我们需求的障碍。 为了获得所需的数据，我们必须考虑基于集合的操作，而不是每次仅考虑一行。

Cursors could be used in some applications for serialized operations as shown in example above, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.

游标可以在某些应用程序中用于序列化操作，如上面的示例所示，但是通常应避免使用游标，因为它们会对性能产生负面影响，尤其是在处理大量数据时。

翻译自:

游标sql server