T-SQL Cursors

T-SQL cursors provide a mechanism to traverse through the rows of a result set one by one. While set-based operations are generally preferred for performance in SQL Server, cursors can be useful for specific scenarios where row-by-row processing is necessary.

What are Cursors?

A cursor allows you to perform operations on each individual row of a query result. They work by defining a result set and then allowing you to move a "pointer" through this result set, fetching one row at a time. This is analogous to how cursors work in programming languages.

When to Use Cursors

While set-based operations are highly efficient in SQL Server, there are situations where cursors might be considered:

When complex logic needs to be applied to each row individually, which cannot be easily expressed in a set-based query.
For migrating application logic that was originally designed for row-by-row processing.
When the result set is very small, and the overhead of cursor creation is negligible.

Recommendation: Always try to use set-based operations whenever possible. Cursors can significantly impact performance and should be used cautiously.

Cursor Syntax and Workflow

The typical workflow for using a T-SQL cursor involves the following steps:

Declare the Cursor: Define the cursor and the `SELECT` statement that will populate it.
Open the Cursor: Execute the `SELECT` statement and populate the cursor with the result set.
Fetch Rows: Retrieve rows from the cursor one by one into variables.
Process Rows: Perform desired operations on the fetched row data.
Close the Cursor: Release the resources associated with the cursor.
Deallocate the Cursor: Remove the cursor definition from memory.

Declaring a Cursor

You declare a cursor using the DECLARE CURSOR statement.

DECLARE cursor_name CURSOR
    [ FOR SELECT_statement ]
    [ FOR LOCAL | GLOBAL ]
    [ FOR READ_ONLY | SCROLL_LOCK ]
    [ SCROLL | FORWARD_ONLY ]
    [ TYPE_WARNING ]
    [ SET ROW_COUNT = number ]
    [ KEYSET | DYNAMIC | FORWARD_ONLY | STATIC ]

Common options include:

FOR SELECT_statement: The query that defines the result set.
FORWARD_ONLY: The default. Rows can only be fetched in the forward direction.
STATIC: Creates a temporary copy of the data. Changes to the underlying tables are not reflected.
DYNAMIC: Reflects changes made to the underlying tables.
KEYSET: A hybrid approach.
READ_ONLY: Prevents updates through the cursor.

Opening and Fetching

The OPEN statement populates the cursor, and FETCH retrieves rows.

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;
-- Or FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE, FETCH RELATIVE

The @@FETCH_STATUS global variable indicates the status of the last fetch:

0: Fetch successful.
-1: Fetch went beyond the result set.
-2: Fetch went before the result set.

Closing and Deallocating

CLOSE releases the result set, and DEALLOCATE removes the cursor definition.

CLOSE cursor_name;
DEALLOCATE cursor_name;

Example: Iterating Through Employees

Let's say we have an Employees table with EmployeeID and FirstName. We want to print each employee's name.

-- Assume an Employees table exists with EmployeeID and FirstName columns

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

-- 1. Declare the cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName
FROM Employees
WHERE EmployeeID < 10; -- Example filter

-- 2. Open the cursor
OPEN EmployeeCursor;

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

-- 4. Loop while fetch is successful
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 5. Process the row (print the name)
    PRINT 'Employee: ' + @FirstName + ' (ID: ' + CAST(@EmployeeID AS VARCHAR(10)) + ')';

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

-- 7. Close the cursor
CLOSE EmployeeCursor;

-- 8. Deallocate the cursor
DEALLOCATION EmployeeCursor;
GO

Cursor Variables

Variables are essential for holding the data fetched from each row of the cursor. Ensure that the data types of the variables match the data types of the columns being fetched.

Performance Considerations

Using cursors can be significantly slower than set-based operations for several reasons:

Row-by-Row Processing: SQL Server is optimized for processing data in sets. Cursors force it to switch context for each row.
Locking: Cursors can hold locks on rows for extended periods, potentially blocking other operations.
Network Traffic: For remote operations, each fetched row can incur network round trips.

Warning: Excessive use of cursors can lead to poor database performance and scalability issues. Always profile your code and consider alternatives.

Alternatives to Cursors

Before resorting to cursors, explore these set-based alternatives:

WHILE loops with set-based operations: Update or process chunks of data at a time.
Common Table Expressions (CTEs) and Recursive CTEs: For hierarchical data or iterative processing.
Window Functions: Powerful for calculations across related rows.
APPLY operators (CROSS APPLY, OUTER APPLY): Can simulate row-by-row processing in a set-based manner for certain scenarios.

SQL Server Docs