Using SQL Server Cursors



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 row-by-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 UPDATE
    INSENSITIVE Yes Yes No
    SCROLL Yes Yes Yes
    READ ONLY Yes Yes No
    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]
    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 FORWARD
    ONLY
    STATIC KEYSET DYNAMIC FAST
    FORWARD
    READ
    ONLY
    SCROLL
    LOCKS
    OPTIMISTIC TYPE
    WARNING
    UPDATE
    LOCAL No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
    GLOBAL No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
    FORWARD_ONLY Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes
    STATIC Yes Yes Yes No No No Yes No Yes Yes No
    KEYSET Yes Yes Yes No No No Yes Yes Yes Yes Yes
    DYNAMIC Yes Yes Yes No No No Yes Yes Yes Yes Yes
    FAST_FORWARD Yes Yes No No No No Yes No No Yes No
    READ_ONLY Yes Yes Yes Yes Yes Yes Yes No No Yes No
    SCROLL_LOCKS Yes Yes Yes No Yes Yes No No No Yes Yes
    OPTIMISTIC Yes Yes Yes Yes Yes Yes No No No Yes Yes
    TYPE_WARNING Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
    UPDATE Yes Yes Yes No Yes Yes No No Yes Yes Yes


    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.

    Literature

    1. Chapter 15 – Creating and Using Cursors


    Special Edition Using SQL Server


    by Bob Branchek, Peter Hazlehurst, Stephen Wynkoop, Scott L. Warner

    2. Performance Tuning SQL Server Cursors

    3. MSDN Library – DECLARE CURSOR

    4. MSDN Library – OPEN

    5. MSDN Library – FETCH

    6. MSDN Library – @@FETCH_STATUS

    7. MSDN Library – CLOSE

    8. MSDN Library – DEALLOCATE


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles