SQL Server Cursors

Cursors :
These let you move through rows one at a time and perform processing on each row
Cursors Keywords:

• The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor.
• The OPEN statement executes the SELECT statement and populates the result set.
• The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables.
• The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.
• cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
  
  Types Of Cursors:
• 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 - 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

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

Use Case Scenario's:

• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  Cursor based logic may not scale to meet the processing needs.
• With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor based approach may meet the need.
• Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
• If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
• Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult

Cursor Options and Related Stored Procedures:

• sp_cursor_list : Returns a list of cursors currently visible on the connection and their attributes.
• sp_describe_cursor : Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.
• sp_describe_cursor_columns : Describes the attributes of the columns in the cursor result set.sp_describe_cursor_tables Describes the base tables accessed by the cursor.
• Options
• FETCH FIRST: Fetches the first row in the cursor.
• FETCH NEXT: Fetches the row after the previously fetched row.
• FETCH PRIOR: Fetches the row before the previously fetched row.
• FETCH LAST: Fetches the last row in the cursor
• FETCH ABSOLUTE n: If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
• FETCH RELATIVE n: If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again.

Example 1:

Declare Flight_cursor CURSOR SCROLL

FOR SELECT * from flight Order By Aircraft_code

Open Flight_Cursor

Fetch First From Flight_Cursor

While @@Fetch_Status =0

BEGIN

Fetch NExt from Flight_Cursor

End

Example 2:

Declare @@counter int

set @@counter=0

Declare @@ProductID int

Declare @@ProductName varchar(30)

Declare @@Qty int
Declare special cursorlocal

Scroll Keyset Optimistic

ForSelect * from Products

Open special /* Opening the cursor */

fetch Absolute 1 from special into @@ProductID,@@ProductName,@@Qty
while @@fetch_Status<>-1

begin

fetch next from specialinto @@ProductID,@@ProductName,@@QtyPrint @@ProductiDPrint @@ProductNameprint @@Qty
/*set @@ProductName=(Select Prod_Name from special)*/

Update Products set Prod_Name= @@counter
set @@counter=@@counter+1

end

close special

Deallocate special

Cursor Locks:

READ ONLY: This prevents any updates on the table.

SCROLL LOCK: This allows you to make changes to the table.

OPTIMISTIC: This checks if any change in the table record since the row fetched before updating. If there is no change, the cursor can update