Understanding T-SQL Cursors

T-SQL Cursors are database objects that allow you to process row by row, much like a traditional programming language loop. While they offer flexibility, it's important to note that they are generally less performant than set-based operations. Use cursors judiciously when set-based alternatives are not feasible or are significantly more complex.

What is a Cursor?

A cursor defines a result set that can be traversed row by row. For each row in the result set, you can perform actions. This is in contrast to standard SQL statements, which operate on the entire set of rows that meet the criteria.

When to Use Cursors

  • When row-by-row processing is absolutely necessary and cannot be achieved with set-based logic.
  • Complex procedural logic needs to be applied to each individual row.
  • Migrating logic from older, row-processing systems.

When to Avoid Cursors

  • When a set-based SQL statement can achieve the same result more efficiently.
  • For simple data retrieval and modification.
  • In high-volume, performance-critical operations where set-based approaches excel.

Cursor Syntax and Lifecycle

Working with T-SQL cursors involves several key steps:

1. DECLARE Cursor

This step defines the cursor and its associated SELECT statement. You specify the cursor name and the query that will populate its result set.


DECLARE MyCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'Sales';

2. OPEN Cursor

After declaration, the cursor must be opened. This executes the associated SELECT statement and populates the cursor's temporary result set.


OPEN MyCursor;

3. FETCH Next Row

This is the core of cursor processing. The FETCH statement retrieves the next row from the cursor's result set and assigns its column values to variables. It also updates the cursor's status.


DECLARE @EmpID INT, @FName VARCHAR(50), @LName VARCHAR(50);

FETCH NEXT FROM MyCursor INTO @EmpID, @FName, @LName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the row using @EmpID, @FName, @LName
    PRINT 'Processing: ' + @FName + ' ' + @LName;

    FETCH NEXT FROM MyCursor INTO @EmpID, @FName, @LName;
END

@@FETCH_STATUS is a system function that indicates the status of the last FETCH operation:

  • 0: FETCH succeeded.
  • -1: FETCH failed or row is beyond the result set.
  • -2: Row is missing.

4. CLOSE Cursor

Once you have finished processing all rows, you should close the cursor. This releases the current result set but does not deallocate the cursor's definition.


CLOSE MyCursor;

5. DEALLOCATE Cursor

Finally, deallocate the cursor to free up all resources associated with it. This removes the cursor definition.


DEALLOCATE MyCursor;

Cursor Types and Options

T-SQL offers various options when declaring cursors, impacting their behavior and performance.

Scroll Options

  • FORWARD_ONLY (Default): Cursors can only be fetched from the first row to the last.
  • SCROLL: Allows fetching in any direction (FIRST, LAST, NEXT, PRIOR, ABSOLUTE, RELATIVE).

Concurrency Options

These options determine how the cursor handles data modifications by other users while the cursor is open.

  • READ_ONLY: The cursor is read-only. No updates can be made through it.
  • FOR UPDATE [OF column_list]: Allows updating the cursor rows. If column_list is specified, only those columns can be updated.
  • OPTIMISTIC: Allows updates but checks for data changes by other users. If changes are detected, the update fails.
  • KEYSET (Deprecated): Records the keys of the rows when the cursor is opened. Subsequent changes to the underlying data by other users might not be reflected.
  • STATIC: Creates a temporary copy of the data. No changes made by others will be reflected. This can be more performant but might lead to stale data.

Example with Options


DECLARE MyScrollableCursor CURSOR SCROLL FOR
UPDATE Employees SET Salary = Salary * 1.05
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName
WHERE Department = 'IT';

OPEN MyScrollableCursor;

-- Fetch and update rows...

CLOSE MyScrollableCursor;
DEALLOCATE MyScrollableCursor;