Henry K., from Learning Tree’s class Developing High Performance SQL Server Databases, writes “My sysadmin said that cursors are evil, and I should never use them in my stored procedures. Is this true?”
Cursors are just another tool in the T-SQL toolbox, and like any tool they can be used properly or used poorly. Unfortunately, many SQL developers have used cursors poorly, leading to a bad reputation for cursors and a large group of folks sharing the opinion of your sysadmin.
Poor use of cursors falls into two broad categories. The first is badly written cursors. Sadly, bad programming can be found in virtually any environment and only proper training can solve this problem. The second category of poor cursors consists of developers who use cursors when a better solution is available.
As we shall soon see in code examples, cursors are data structures that permit the T-SQL developer to execute a query and then step through the row results one row at a time. It’s fair to say that if an SQL set-oriented solution exists, it will always be preferable to a solution based on cursors. For example, creating a cursor and then stepping through it to update the price in each row by 7% would be hopelessly inefficient. Use an UPDATE statement. Sadly, some programmers use cursors because the step-by-step logic of a cursor seems more natural to them than the declarative UPDATE statement. That’s no excuse.
Consider a formidable calculation, however. Perhaps a tax calculation must be performed on every row. It is likely that there are many different conditions that affect the calculation, potentially involving lots of conditional logic in the code. It may simply not be possible to perform the calculation armed only with declarative SQL, and a row-oriented cursor solution may be the only practical approach.
There are actually two distinct declarations for cursors in T-SQL. One follows the ANSI standard, and one is specific for Transact-SQL. In general, I will tend to prefer standard techniques over platform-specific methods, but here I make an exception. The T-SQL-specific syntax enables more options for cursors, and more options means the characteristics of the cursor can be more specifically tailored to our needs.
We’ll start by taking a brief look at the ANSI cursor declaration; it’s simpler but still illustrates some fundamental principles.
DECLARE Test_ANSI INSENSITIVE — NOT INSENSITIVE, SCROLL
FOR SELECT CustomerID, OrderID, OrderDate, ShipCountry FROM Orders
We immediately recognize this as an ANSI cursor, since cursor type options appear before the keyword “CURSOR”.
An INSENSITIVE cursor is a snapshot copy of the rowdata created when the SELECT statement is executed. It is called insensitive because it is not affected by any row changes that occur after its creation. In contrast, a NOT INSENSITIVE cursor is a set of keys created when the cursor is created. When a row is fetched using a NOT INSENSITIVE cursor, SQL Server uses the key to obtain the actual row data. As a result, if the row data is changed after the cursor is created, the NOT INSENSTIVE cursor will obtain the new version of the row data. If a new row is added, however there is no key in the cursor that points to it and the new row will be invisible as far as the cursor is concerned.
In my opinion, the primary value of the NOT INSENSITIVE cursor arises when large rowsets are required. A set of keys takes up fewer system resources than a set of rowdata.
By default, a cursor is forward-only, meaning you can only start at the beginning and keep reading the next row till you get to the end. If need be, the SCROLL option allow you to fetch the rows in any sequence you like, and return to previously fetched rows. For virtually all applications, a forward-only cursor is sufficient.
It is possible to declare a cursor for update, but we will not consider that option here.
Here is the T-SQL version of the same cursor declaration.
DECLARE Test_TSQL CURSOR — note that keyword CURSOR comes before options
GLOBAL — or local; A GLOBAL cursor is defined for the connection, not the batch, as we will illustrate
— FORWARD_ONLY or SCROLL
STATIC is the equivalent of INSENSITIVE
other options are KEYSET, DYNAMIC, and FAST_FORWARD
FOR SELECT CustomerID, OrderID, OrderDate, ShipCountry FROM Orders
We can immediately recognize the T-SQL syntax because the keyword CURSOR comes immediately after the cursor name, and the options follow.
STATIC is the equivalent of INSENSITIVE in the ASNI syntax. A static copy of the rowdata is constructed when the cursor is opened.
KEYSET is the T-SQL equivalent of NOT INSENSITIVE. A set of keys is constructed when the cursor is opened and the rowdata is retrieved at the time of the cursor FETCH operation.
A DYNAMIC cursor resembles a keyset, but the set of keys is not fixed. As you scroll, new keys are generated to reflect new rows added to the table after the cursor was opened. A dynamic keyset has the advantage of always reflecting the current state of rowdata in the underlying tables, dynamic cursors are also valuable when dealing with a large number of rows, as the entire set of keys does not need to be maintained.
FAST_FORWARD is a cursor optimized for forward-only performance. FAST_FORWARD should not be confused with FORWARD_ONLY.. FORWARD_ONLY is the default if you do not declare SCROLL, and simply means that you will only move to the next row when accessing the cursor. If you want FAST_FORWARD you must say so; the default cursor type for FORWARD_ONLY is DYNAMIC.
ANSI cursors are global, which means that, once declared, the cursor remains defined on the connection for as long as the connection remains open. In T-SQL cursors, we can specify whether we want our cursor to be global or local. A local cursor is available only within the T-SQL batch in which it was defined. In T-SQL as in all development environments, it is good practice to clean up as quickly as possible after the work is done. If there is no good reason for a cursor to be declared GLOBAL, LOCAL should be the preferred scope.
Cursors have, and will continue to have, an important role in SQL development. The Transact-SQL extended syntax provides more cursor types than the ANSI syntax, permitting the developer to choose a type most appropriate for the rowset and the task at hand.
In the next installment, we’ll see how to fetch and use cursor row data. The SQL code examples can be downloaded here.
For more, have a look at Learning Tree’s entire curriculum of 30 hands-on SQL Server courses. This includes 15 1-day sessions such as our new course on Writing Complex PL/SQL with Cursors and Collections. All these courses can be taken online from home or office.