Using Sql Server Cursors

Using SQL Server Cursors By Alexander Chigrik

General Concepts Declaring a Cursor SQL-92 Syntax Transact-SQL Extended Syntax Opening a Cursor Fetching a Cursor Closing a Cursor Deallocating a Cursor Cursor Optimization Tips Literature

General concepts In this article, I want to tell you how to create and use server side cursors and how you can optimize a cursor performance. Cursor is a database object used by applications to manipulate data in a set on a rowby-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable. The server side cursors were first added in the SQL Server 6.0 release and are now supported in all editions of SQL Server 7.0 and SQL Server 2000. Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.

Declaring a Cursor Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

SQL-92 Syntax This is SQL-92 Syntax: DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

where cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. INSENSITIVE - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor. SCROLL - Specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported. select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords. READ ONLY - Specifies that cursor cannot be updated. UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility INSENSITIVE SCROLL READ ONLY INSENSITIVE Yes Yes SCROLL Yes Yes READ ONLY Yes Yes UPDATE No Yes No

Transact-SQL Extended Syntax This is Transact-SQL Extended Syntax: DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING]

UPDATE No Yes No

FOR select_statement [FOR UPDATE [OF column_name [,...n]]]

where cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. LOCAL - Specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates. GLOBAL - Specifies that cursor is global to the connection. The GLOBAL cursor will be implicitly deallocated at disconnect. FORWARD_ONLY - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. STATIC - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor. KEYSET - Specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. DYNAMIC - Specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor. FAST_FORWARD - Specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server. READ ONLY - Specifies that cursor cannot be updated. SCROLL_LOCKS - Specifies that cursor will lock the rows as they are read into the cursor to ensure that positioned updates or deletes made through the cursor will be succeed. OPTIMISTIC - Specifies that cursor does not lock rows as they are read into the cursor. So, the positioned updates or deletes made through the cursor will not succeed if the row has been updated outside the cursor since this row was read into the cursor. TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.

select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords. UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility LOCAL GLOBAL LOCAL GLOBAL

No No

FORWARD FAST READ SCROLL STATIC KEYSET DYNAMIC OPTIM ONLY FORWARD ONLY LOCKS Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

Yes

Yes

Yes

No

No

No

Yes

No

Yes

No

No

Yes

Yes

Yes

No

Yes

Yes

Yes

Yes

No

No

No

No

FORWARD_ONLY Yes

Yes

STATIC

Yes

Yes

Yes

KEYSET

Yes

Yes

Yes

No

DYNAMIC

Yes

Yes

Yes

No

No

FAST_FORWARD Yes

Yes

No

No

No

No

READ_ONLY

Yes

Yes

Yes

Yes

Yes

Yes

Yes

SCROLL_LOCKS Yes

Yes

Yes

No

Yes

Yes

No

No

OPTIMISTIC

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

TYPE_WARNING Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

UPDATE

Yes

Yes

No

Yes

Yes

No

No

Yes

Yes

Yes

No

Opening a Cursor Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax: OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

where GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened. cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. cursor_variable_name - The name of a cursor variable that references a cursor. After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.

Fetching a Cursor Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax: FETCH [

[

NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}

] FROM

] { { [GLOBAL] cursor_name } | @cursor_variable_name} [INTO @variable_name[,...n] ]

where NEXT - The default cursor fetch option. FETCH NEXT returns the next row after the current row. PRIOR - Returns the prior row before the current row. FIRST - Returns the first row in the cursor. LAST - Returns the last row in the cursor. ABSOLUTE {n \| @nvar} - Returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set. RELATIVE {n \| @nvar} - Returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row. GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched. cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. cursor_variable_name - The name of a cursor variable that references a cursor. INTO @variable_name[,...n] - Allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.

Closing a Cursor When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open. To close a cursor, you can use the following syntax: CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

where GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed. cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. cursor_variable_name - The name of a cursor variable that references a cursor. Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.

Deallocating a Cursor When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor. To deallocate a cursor, you can use the following syntax: DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}

where GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated. cursor_name - The name of the server side cursor, must contain from 1 to 128 characters. cursor_variable_name - The name of a cursor variable that references a cursor. Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.

Cursor Optimization Tips

•

Try to avoid using SQL Server cursors whenever possible. Using SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables if you need to perform row-by-row operations.

•

Do not forget to close SQL Server cursor when its result set is not needed. To close SQL Server cursor you can use the CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

•

Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed. To deallocate SQL Server cursor, you can use the DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

•

Try to reduce the number of records to process in the cursor. To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

•

Try to reduce the number of columns to process in the cursor. Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. This can increase cursor performance and reduce SQL Server overhead.

•

Use READ ONLY cursors, whenever possible, instead of updatable cursors. Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

•

Try avoid using insensitive, static and keyset cursors, whenever possible. These types of cursor produce the largest amount of overhead on SQL Server as they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

•

Use FAST_FORWARD cursors, whenever possible. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server as they are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

•

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option. If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified, the other cannot be specified.

Performance Tuning SQL Server Cursors If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Here are some alternatives to using a cursor: • • • • • •

Use WHILE LOOPS Use temp tables Use derived tables Use correlated sub-queries Use the CASE statement Perform multiple queries

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor. [6.5, 7.0, 2000] Updated 7-8-2004 ***** If you do find you must use a cursor, try to reduce the number of records to process. One way to do this is to move the records that need to be processed into a temp table first, then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subset of records to be inserted into the temp table are substantially less than those in the original table. The lower the number of records to process, the faster the cursor will finish. [6.5, 7.0, 2000, 2005] Updated 6-6-2005 ***** If the number of rows you need to return from a query is small, and you need to perform row-by-row operations on them, don't use a server-side cursor. Instead, consider returning the entire rowset to the client and have the client perform the necessary action on each row, then return any updated rows to the server. [6.5, 7.0, 2000, 2005] Updated 6-6-2005 ***** If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors, in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset. [6.5, 7.0, 2000, 2005] Updated 6-6-2005 ***** Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues. [6.5, 7.0, 2000] Updated 1-18-2004 ***** If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors use the tempdb for temporary storage of cursor data. The faster your disk array, the faster your cursor will be. [6.5, 7.0, 2000] Updated 1-18-2004 ***** Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency. [6.5, 7.0, 2000] Updated 118-2004 ***** When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released. [6.5, 7.0, 2000] Updated 1-182004 ***** If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources. [6.5, 7.0, 2000] Updated 1-18-2004 ***** If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment. [6.5, 7.0, 2000] Updated 1-18-2004 ***** When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The

smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit. [6.5, 7.0, 2000] Updated 1-18-2004 ***** If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible. [6.5, 7.0, 2000] Updated 1-31-2004 ***** If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance. [6.5, 7.0, 2000] Updated 1-31-2004 ***** In earlier versions of SQL Server, Transact-SQL cursors were only global to the connection. But in SQL Server 7 and 2000 there are two options to define the scope of a cursor. LOCAL and GLOBAL keywords in the DECLARE CURSOR statement are used to specify the scope of a cursor. A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. GLOBAL cursors must be explicitly deallocated or they will be available until the connection gets closed. For optimum performance, you should always explicitly deallocate a cursor when you are done using it. LOCAL cursors are implicitly deallocated when the stored procedure or the trigger or the batch in which they were created terminates. We can use LOCAL cursors for more security and better scope of the cursor in our application, which also helps to reduce resources on the server, and boosting performance. [7.0, 2000] Contributed by Nataraj Prakash. ***** Consider using asynchronous cursors if you expect your result set to be very large. This allows you to continue processing while the cursor is still being populated. While it may not actually speed up your application, it should give the appearance to your end users that something is happening sooner that if they have to wait until the entire cursor is populated. [6.5, 7.0, 2000] Updated 1-31-2004 ***** If you have to use a cursor, break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit immediately. [6.5, 7.0, 2000] Updated 1-31-2004 ***** If you are using the same cursor more than once in a batch of work, (or within more than one stored procedure), then define the cursor as a global cursor by using the GLOBAL keyword. By not closing or deallocating the cursor until the whole process is finished, a fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use. [6.5, 7.0, 2000] Updated 1-31-2004 *****

Sometimes, it is handy to be able to perform some calculation on one or more columns of a record, and then take the result of that calculation and then add it to similar calculations performed on other related records to find a grand total. For example, let's say you want to find the total dollar cost of an invoice. An invoice will generally involve a header record and one or more detail records. Each detail record will represent a line item on the invoice. In order to calculate the total dollar cost of an invoice, based on two or more line items, you would need to multiply the quantity of each item sold times the price of each item. Then, you would need to add the total price of each line item together in order to get the total dollar cost of the entire invoice. To keep this example simple, let's ignore things like discounts, taxes, shipping, etc. One way to accomplish this task would be to use a cursor, like we see below (we are using the Northwind database for this example code): DECLARE @LineTotal money DECLARE @InvoiceTotal money SET @LineTotal = 0 SET @InvoiceTotal = 0

--Declare variables

DECLARE Line_Item_Cursor CURSOR FOR

--Declare the cursor

SELECT UnitPrice*Quantity FROM [order details] WHERE orderid = 10248

--Set variables to 0

--Multiply unit price times quantity ordered --We are only concerned with invoice 10248

OPEN Line_Item_Cursor FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal WHILE @@FETCH_STATUS = 0 BEGIN SET @InvoiceTotal = @InvoiceTotal + @LineTotal FETCH NEXT FROM Line_Item_Cursor INTO @LineTotal END CLOSE Line_Item_Cursor DEALLOCATE Line_Item_Cursor SELECT @InvoiceTotal InvoiceTotal

--Open the cursor --Fetch next record

--Summarize line items

--Close cursor --Deallocate cursor --Display total value of invoice

The result for invoice number 10248 is $440.00. What the cursor does is to select all of the line items for invoice number 10248, then multiply the quantity ordered times the price to get a line item total, and then it takes each of the line item totals for each record and then adds them all up in order to calculate the total dollar amount for the invoice. This all works well, but the code is long and hard to read, and performance is not great because a cursor is used. Ideally, for best performance, we need to find another way to accomplish the same goal as above, but without using a cursor. Instead of using a cursor, let's rewrite the above code using set-based Transact-SQL instead of a cursor. Here's what the code looks like: DECLARE @InvoiceTotal money SELECT @InvoiceTotal = sum(UnitPrice*Quantity) FROM [order details] WHERE orderid = 10248 SELECT @InvoiceTotal InvoiceTotal

The result for invoice number 10248 is $440.00. Right away, it is obvious that this is a lot less code and that is it more readable. What may not be obvious, is that it uses less server resources and performs faster. In our example-with few rows--the time difference is very small, but if many rows are involved, the time difference between the techniques can be substantial. The secret here is to use the Transact-SQL "sum" function to summarize the line item totals for you, instead of relying on a cursor. You can use this same technique to help reduce your dependency on using resource-hogging cursors in much of your Transact-SQL code. [6.5, 7.0, 2000] Updated 8-4-2004