Cursors provide a means of
processing through a set of records row-by-row. However, many companies have a
policy against using cursors in their SQL Server standards. This article examines the
problem with cursors and alternatives to using them.
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
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
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
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.
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
Cursor Types (Database Engine)
See All Articles by Columnist