T-SQL Cursors

Cursors enable row-by-row processing of a result set. While set-based operations are generally preferred in SQL Server for performance, cursors provide a mechanism to handle situations where row-by-row logic is unavoidable or more appropriate. This section covers the syntax, functionality, and best practices associated with T-SQL cursors.

What are Cursors?

A cursor is a database object that facilitates traversal of the rows in a result set. Unlike standard SQL queries that operate on the entire set of returned rows simultaneously, a cursor allows you to fetch and process one row at a time. This can be useful for complex iterative logic, updating individual rows based on specific conditions, or when migrating application logic that relies on recordsets.

Declaring and Using Cursors

The process of using a cursor involves several key Transact-SQL statements:

DECLARE CURSOR: Defines the cursor, specifying its name, scope, concurrency options, and the SELECT statement that populates it.
OPEN: Executes the SELECT statement associated with the cursor and populates the cursor with the result set.
FETCH NEXT FROM: Retrieves the next row from the cursor into variables.
WHILE @@FETCH_STATUS = 0: A common loop construct to iterate through the fetched rows.
CLOSE: Closes the cursor, releasing any associated resources.
DEALLOCATE: Removes the cursor definition from memory.

Cursor Syntax Example

DECLARE @EmployeeID INT;
DECLARE @FirstName VARCHAR(50);

-- Declare a cursor for selecting employee names
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName
FROM Employees
WHERE Department = 'Sales';

-- Open the cursor
OPEN EmployeeCursor;

-- Fetch the first row
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName;

-- Loop through the cursor while rows are being fetched successfully
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the current row (e.g., print the name)
    PRINT 'Processing Employee ID: ' + CAST(@EmployeeID AS VARCHAR) + ', Name: ' + @FirstName;

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName;
END

-- Close the cursor
CLOSE EmployeeCursor;

-- Deallocate the cursor
DEALLOCATE EmployeeCursor;

Cursor Types and Options

When declaring a cursor, you can specify various options that affect its behavior, including:

Scroll options:FORWARD_ONLY (default), SCROLL (allows FIRST, LAST, PRIOR, NEXT, ABSOLUTE, RELATIVE).
Concurrency options: READ_ONLY, OPTIMISTIC, KEYSET, STATIC. Each option determines how the cursor handles data modifications made by other users while the cursor is active.
Scroll sensitivity: INSENSITIVE, SENSITIVE, STATIC. This dictates whether changes made to the underlying data after the cursor is opened will be reflected when fetching rows.

Performance Considerations

Recommendation: Avoid using cursors whenever possible. Set-based operations are significantly more efficient and scalable in SQL Server. Cursors can consume substantial server resources and lead to performance bottlenecks, especially with large result sets. Only use them when there is no practical alternative.

If a cursor is necessary, consider these performance tips:

Use FORWARD_ONLY and READ_ONLY options when possible.
Fetch rows in manageable batches rather than one at a time if the processing logic allows.
Ensure the SELECT statement defining the cursor is optimized with appropriate indexes.
Declare variables to hold fetched data to minimize context switching.
Always CLOSE and DEALLOCATE cursors when they are no longer needed.

Alternatives to Cursors

Before resorting to cursors, explore set-based alternatives such as:

WHILE loops with TOP and DELETE/UPDATE: For iterating and processing a subset of rows.
Recursive Common Table Expressions (CTEs): For hierarchical data or iterative calculations.
APPLY operators (CROSS APPLY, OUTER APPLY): For applying a table-valued function to each row of a table.
Window Functions: For performing calculations across a set of table rows related to the current row.

Microsoft SQL Server Documentation