What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives)

What is a Cursor

A cursor is used to process through a result set one row at a time. Often times this is used for batch processing. There are four types of cursors: static, dynamic, keyset, and forward-only.

Static cursors have their data and result set fixed at the time the SELECT associated with the cursor is executed (when the cursor is opened). It will not see any modifications made by other users including inserts that may match the query criteria or deletes that would have removed rows. Tempdb is enlisted to hold this snapshot of the result set. Therefore, the result set must conform to the limits of a single table. Static cursors are not updateable.

Dynamic cursors detect all changes to the underlying result set as it scrolls. Scrolling can occur in any direction and the cursor may change its result set and/or order with every fetch (pointing to another row). This means a dynamic cursor consumes more resources performing its work than does the static cursor.

Keyset cursors, as their name implies, are sets of keys that identify the rows in the result set. As the cursor is scrolled through, the data is retrieved for those key values. Unlike dynamic cursors, the membership and order of the rows in the result set are fixed once the cursor is opened. Due to the nature of keyset cursors, it is still possible for other user's changes to data associated with the cursor and its members to be reflected in the fetch of any given row.

The final cursor type of forward-only isn't really a separate type for SQL Server as it can be specified in addition to any one of the other types. This is more of a behavior than a type as it specifies that the cursor can only scroll forward to the next row in the result set and never backward.

The behavior of a cursor is affected by two keywords: SCROLL and INSENSITIVE. SCROLL tells SQL Server you reserve the right to scroll both forward and back through the result set instead of just forward. You can specify whether the cursor will ignore changes to the underlying data as it scrolls by declaring the INSENSITIVE option. This also makes the cursor read-only. Additionally, you can specifically declare that the cursor is not updateable by using the READ ONLY option.

It is important to note that combinations of behaviors and cursor types are not recommended by Microsoft and may produce unexpected behavior. That being said, forward-only cursors default to a dynamic type. Dynamic, static, and keyset cursors default to the SCROLL behavior.

Cursor Tuning

Depending on the purpose behind the cursor, there are multiple options available for performance tuning. The goals of performance tuning are not only to increase the performance of the cursor itself, but also of other SQL Server operations that may be contending for the same data or resources.

First and foremost, make sure to tune the SELECT statement from which the cursor is built. Ensure that it only returns the number of rows necessary for the processing being performed. If need be, provide additional criteria in the WHERE clause. Also, reduce the number of columns returned by the result set to just those necessary for processing. This reduces the resources required by the cursor to keep track of the data.

If the cursor is used for reference only and not for updates, make sure the cursor specifies the READ ONLY option if it is not already read only (such as a static or insensitive cursor). This makes sure the cursor doesn't hold locks on the underlying tables thus reducing concurrency. If you only need to move forward through the cursor, FAST_FORWARD will get you the read only access you need while reducing overhead. This is similar to FORWARD_ONLY; however, FORWARD_ONLY can be used with cursors that need to update the underlying data.

If you are suffering from contention on your updates, try changing your cursor to OPTIMISTIC. This specifies that changes made to the row updated through the cursor will be timestamp checked and fail if the row was changed after it was fetched. If, however, you need all the cursor related changes to succeed, you can use SCROLL_LOCK to lock the result set rows when the results are retrieved. This method increases contention with other users of the underlying data and should be used only after much consideration.

Hopefully you don't come across this in your tuning duties, but another thing to look out for is whether the cursor is being properly closed and deallocated. Closing and deallocating the cursor allows SQL Server to free up any locks associated with the underlying tables as well as free up resources allocated to the cursor.

Alternatives to Cursors

At my work place, cursors are banned in our SQL Server standards. In order to use a cursor, we have to prove that the performance of the cursor is better than processing the rows another way. It has happened, by the way, that we've proved the cursor to be the better option, but it's rare.

The preferred way of getting around cursors where I work, is to fetch the rows into a temporary table, often with an identity column as the key. We then loop through the rows in the temporary table using the identity (or other key) column to single out the next row to process, process the row, and move on to the next row by primary key.

Of course, it is implied in our policy that all set-based options have been exhausted as a means to process these rows. Set-based operations are by far the preferred method of processing data and therefore the number one alternative to cursors. Sometimes, however, it's just not possible.

The introduction of Common Table Expressions (CTEs) in SQL Server 2005 gives developers more flexibility in working around cursors. CTEs, like derived tables, allow you to express a temporary result set within a single SELECT, INSERT, UPDATE, or DELETE. CTEs, however, can be referenced multiple times within the SQL operation.

Along those same lines, correlated sub-queries should be considered as a way to provide a similar kind of row-by-row processing. The difference being that the correlated sub-query returns matches for each of the outer rows being operated on instead of finding all the "inner" rows via a cursor and scrolling through them to process the "outer" data.

There are other methods that may work well for your situation. There is no one option that covers all needs. These are just some of the more popular solutions.

Conclusion

Cursors are an effective means of accomplishing row-by-row processing. However, there are some inherent performance issues. It is important to understand how your cursor is affecting the underlying tables, SQL Server resources, and concurrency. While there are options available to tune cursors, often times alternatives such as set-based operations or while loops are actually better performing.

For More Information

MSDN: DECLARE CURSOR
MSDN: Cursor Performance
MSDN: Cursors
MSDN: Cursor Types (Database Engine)

» See All Articles by Columnist Deanna Dicken