Cursors (Transact-SQL)

A Transact-SQL cursor is a control mechanism that enables operations on individual rows of a result set, row by row.

Introduction

While set-based operations are generally preferred in SQL for performance, cursors are sometimes necessary when row-by-row processing is the most straightforward or only feasible approach. Cursors allow you to iterate through a result set, fetch one row at a time, and perform actions on that specific row. This can be useful for complex reporting, data manipulation where each row has unique dependencies, or integration with applications that require sequential data processing.

How Cursors Work

When you declare a cursor, SQL Server creates a temporary result set based on your query. You can then open the cursor, which populates this temporary result set. After opening, you can fetch rows one by one into variables or temporary tables. As you fetch, the cursor's internal pointer moves to the next row. You can also move the pointer backward, to the first or last row, or to a specific row.

Cursor Types

SQL Server supports several types of cursors, each with different characteristics:

• Transact-SQL Cursors: These are the standard cursors implemented in T-SQL. They are managed entirely within SQL Server.
• API Cursors: These are typically used by client applications (like ADO.NET, ODBC) and are often implemented using server-side cursors managed by the database.

Within Transact-SQL cursors, further distinctions are made based on their characteristics:

• FORWARD_ONLY: The cursor can only move forward. This is the default and offers the best performance.
• SCROLL: The cursor can move forward and backward. This is more resource-intensive.
• STATIC: Creates a temporary copy of the data. Changes to the underlying table are not reflected in the cursor. Rows are guaranteed to be stable.
• DYNAMIC: Reflects all changes made to the database. Rows can be added, deleted, or modified between fetches. This is the most performant of the scrollable cursors but can be unpredictable.
• KEYSET: The cursor identifies the rows and their order when the cursor is opened. Other users' changes to data in the rows are visible, but additions or deletions by other users are not.
• INSENSITIVE: A copy of the data is made when the cursor is opened. Changes to the underlying table are not reflected. This is similar to STATIC but with more guarantees about unaffected data.

Cursor Operations

The primary operations for working with cursors are:

• DECLARE CURSOR: Defines the cursor's properties and the SQL query it will use.
• OPEN: Populates the cursor's result set.
• FETCH: Retrieves a single row from the cursor's result set into variables.
• CLOSE: Closes the cursor, releasing its resources.
• DEALLOCATE: Removes the cursor definition.

Syntax

The basic syntax for declaring and using a cursor:

DECLARE cursor_name CURSOR
    [ [ INSENSITIVE ] [ SCROLL ] | [ FORWARD_ONLY ] ]
    [ STATIC ] [ KEYSET ] [ DYNAMIC ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
[ ; ]

OPEN cursor_name
[ ; ]

FETCH
    [ NEXT | PRIOR | FIRST | LAST | {ABSOLUTE {n | @n} | RELATIVE {n | @n} } ]
    [ FROM ] cursor_name
    [ INTO @variable_name [ , ...n ] ]
[ ; ]

-- Example of processing a row
-- IF @@FETCH_STATUS = 0
-- BEGIN
--     -- Process the fetched row using @variable_name(s)
--     -- ...
-- END

CLOSE cursor_name
[ ; ]

DEALLOCATE cursor_name
[ ; ]
            

Cursor Attributes Explanation:

• FORWARD_ONLY: Fetches rows only in the forward direction.
• STATIC: Creates a temporary copy.
• KEYSET: Uses keys to identify rows.
• DYNAMIC: Reflects all database changes.
• READ_ONLY: Prevents updates through the cursor.
• SCROLL_LOCKS: Locks rows to prevent others from modifying them.
• OPTIMISTIC: Assumes rows will not be changed and checks for conflicts before updates.

Example

This example demonstrates how to declare a cursor to iterate through employees and print their names:

DECLARE @EmployeeName NVARCHAR(100);
DECLARE @Department NVARCHAR(50);

-- Declare a FORWARD_ONLY, READ_ONLY cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT FirstName + ' ' + LastName, Department
FROM Employees
WHERE IsActive = 1;

OPEN EmployeeCursor;

-- Fetch the first row
FETCH NEXT FROM EmployeeCursor INTO @EmployeeName, @Department;

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @EmployeeName + ' | Department: ' + @Department;

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeName, @Department;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
            

Considerations

While powerful, cursors can have performance implications:

• Performance: Cursors process data row by row, which is significantly slower than set-based operations that process data in batches. Use cursors only when a set-based approach is not practical or significantly more complex.
• Resource Usage: Cursors can consume memory and lock resources, especially for large result sets or when using scrollable or locked cursors.
• Complexity: Managing cursor logic, especially with error handling and different fetch types, can add complexity to your code.

Always strive to rewrite cursor logic into set-based operations where possible. Consider using techniques like temporary tables, table variables, or window functions to achieve set-based processing.