What is a Cursor in SQL?

by

If you’ve been working with SQL for a while, you’ve probably heard someone mention cursors, usually followed by a warning to avoid them. Maybe you’ve used them yourself. But what exactly are cursors, and why do they get such a bad rap? Let’s take a look at what cursors are, how they work, and when (if ever) you should actually use them.

The Basic Concept

In SQL programming, a cursor is a database object that lets you retrieve and manipulate data one row at a time, rather than working with entire result sets all at once. You could think of it like a pointer or iterator that moves through the rows of a query result, allowing you to process each row individually.

In standard SQL operations, you’ll typically work with sets of data. When you run a SELECT statement, you get back a result set that you can filter, join, aggregate, or transform using set-based operations. Cursors let you step through that result set row by row, kind of like using a loop in a programming language.

How Cursors Work

When you create a cursor, you’re basically defining a SELECT statement and then opening it to start fetching rows. The cursor maintains a position within the result set, and you can fetch rows one at a time (or sometimes in small batches) while the cursor is open.

The typical lifecycle of a cursor involves several steps:

Declaring the Cursor

First, you declare the cursor and associate it with a SELECT statement. This defines what data the cursor will work with, but it doesn’t actually execute the query yet.

Here’s an example:

DECLARE my_cursor CURSOR FOR
SELECT employee_id, first_name, salary
FROM employees
WHERE department = 'Sales';

Opening the Cursor

Once declared, you need to open the cursor. This executes the SELECT statement and populates the result set that the cursor will iterate through:

OPEN my_cursor;

Fetching Rows

With the cursor open, you can start fetching rows. Each FETCH operation retrieves the next row from the result set and moves the cursor position forward. You typically do this in a loop until there are no more rows to fetch.

Example:

FETCH NEXT FROM my_cursor INTO @emp_id, @first_name, @salary;

Closing and Deallocating

When you’re done with the cursor, you need to close it to free up the resources it’s using. In some database systems, you also need to deallocate it to completely remove it from memory:

CLOSE my_cursor;
DEALLOCATE my_cursor;

Types of Cursors

Depending on your DBMS (like SQL Server, Oracle, or MySQL), there are a few types of cursors you can use, including:

  • Static Cursors: Static cursors create a snapshot of the data when the cursor is opened. Any changes made to the underlying data after the cursor opens won’t be reflected in the cursor’s result set. This makes them predictable but potentially outdated.
  • Dynamic Cursors: Dynamic cursors reflect changes made to the data while the cursor is open. If someone updates, inserts, or deletes rows that match your cursor’s query, you’ll see those changes as you fetch through the cursor. This keeps the data fresh but can lead to inconsistencies.
  • Forward-Only Cursors: These cursors can only move forward through the result set. You can’t go back to previous rows. They’re typically the fastest type of cursor because they don’t need to maintain the ability to scroll backward.
  • Scrollable Cursors: Scrollable cursors let you move both forward and backward through the result set, jump to specific positions, and fetch rows in various orders. They’re more flexible but come with additional overhead.
  • Keyset-Driven Cursors: Keyset-driven cursors use unique identifiers to track rows in the result set. They show updates to existing rows and detect deletions (which appear as “holes”), but don’t reflect newly inserted rows or changes to key columns. They offer better performance than dynamic cursors while still detecting some changes, making them a good middle ground between static and dynamic cursor types.

Example

Here’s an example of how you might use a cursor in SQL Server:

DECLARE @emp_id INT;
DECLARE @salary DECIMAL(10,2);
DECLARE @bonus DECIMAL(10,2);

DECLARE bonus_cursor CURSOR FOR
SELECT employee_id, salary
FROM employees
WHERE eligible_for_bonus = 1;

OPEN bonus_cursor;

FETCH NEXT FROM bonus_cursor INTO @emp_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Complex bonus calculation logic here
    IF @salary < 50000
        SET @bonus = @salary * 0.10;
    ELSE IF @salary < 100000
        SET @bonus = @salary * 0.08;
    ELSE
        SET @bonus = @salary * 0.05;
    
    -- Update the employee record
    UPDATE employees
    SET bonus_amount = @bonus
    WHERE employee_id = @emp_id;
    
    FETCH NEXT FROM bonus_cursor INTO @emp_id, @salary;
END;

CLOSE bonus_cursor;
DEALLOCATE bonus_cursor;

Here, we stepped through each employee that’s eligible for a bonus, and checked their salary in order to calculate their bonus. Their bonus amount depends on their salary. Those with a salary below $50K receive a bonus of 10%. Those between $50K and $100K receive 8%, and anyone over $100K receives 5%.

Why Cursors Get a Bad Reputation

If you’ve heard about cursors before, then you’ve probably also heard that you should “avoid them at all costs!”. Cursors have certainly developed a negative reputation over the years.

But there are some good reasons for this negative reputation.

  • Performance Issues: Cursors are inherently slow compared to set-based operations. When you use a cursor, you’re processing data one row at a time instead of leveraging the database engine’s ability to work with entire sets of data at once. This row-by-row processing creates significant overhead, especially with large result sets. Databases are optimized for set-based operations. They can perform complex operations on thousands or millions of rows incredibly efficiently using set-based logic. When you switch to a cursor, you’re essentially fighting against this optimization.
  • Resource Consumption: Cursors consume memory and hold locks on database resources while they’re open. If a cursor is left open for a long time, or if multiple cursors are used simultaneously, they can cause blocking and concurrency issues that affect other database operations.
  • Lock Escalation: Depending on how you configure them, cursors can hold locks on rows or pages as they iterate through the result set. This can prevent other users from accessing or modifying data, leading to contention and reduced system throughput.

When Cursors Might Be Appropriate

Despite all the warnings, there are some scenarios where cursors might actually be the right tool for the job. These situations are relatively rare, but they do exist.

  • Complex Procedural Logic: Sometimes you need to perform operations that truly require processing rows individually with complex conditional logic that can’t be expressed in standard SQL. If you’re dealing with business rules that involve multiple steps per row, external system calls, or decision trees that vary significantly per row, a cursor might be your only option.
  • Database Administration Tasks: DBAs sometimes use cursors for maintenance operations, like iterating through all tables in a database to update statistics or rebuild indexes. These operations are typically run during maintenance windows when performance isn’t critical.
  • Working with External Systems: If you need to call external APIs or processes for each row of data, a cursor might be necessary. For example, if you’re integrating with a third-party system that requires individual requests for each record, row-by-row processing might be unavoidable.
  • Small Result Sets: If you’re only working with a small number of rows, the performance difference between a cursor and a set-based approach might be negligible. In these cases, if a cursor makes the code more readable or maintainable, the performance hit might be acceptable.

Alternatives to Cursors

Before you reach for a cursor, it’s worth exploring alternatives that can achieve the same result with better performance.

Set-Based Operations

The first thing to try is always a set-based approach. Many operations that seem to require row-by-row processing can actually be accomplished with clever use of joins, subqueries, common table expressions (CTEs), or window functions.

For instance, that bonus calculation example could potentially be rewritten as:

UPDATE employees
SET bonus_amount = CASE
    WHEN salary < 50000 THEN salary * 0.10
    WHEN salary < 100000 THEN salary * 0.08
    ELSE salary * 0.05
END
WHERE eligible_for_bonus = 1;

This single UPDATE statement would execute orders of magnitude faster than the cursor version.

Temporary Tables

If you need to process data in stages, temporary tables can be a good middle ground. You can use set-based operations to populate a temp table, then perform additional set-based operations on that data without resorting to cursors.

Table Variables

Similar to temp tables, table variables can hold intermediate results and allow you to chain together set-based operations without the overhead of cursors.

WHILE Loops with TOP()

If you absolutely must process data in smaller chunks, you can use a WHILE loop with TOP() to grab batches of rows instead of processing them one at a time. This is still not as good as a pure set-based approach, but it’s usually faster than a cursor.

Best Practices If You Must Use Cursors

If you’ve exhausted all other options and determined that a cursor is truly necessary, here are some best practices to minimize the performance impact:

  • Use the most restrictive cursor type possible. If you only need to move forward through the data, use a FORWARD_ONLY cursor. Don’t use a scrollable cursor unless you actually need the scrolling functionality.
  • Choose READ_ONLY cursors when you’re not modifying the data through the cursor. This reduces locking and improves concurrency.
  • Keep cursors open for the shortest time possible. Open them, do your work, and close them immediately. Don’t leave cursors open while waiting for user input or performing other lengthy operations.
  • Use LOCAL cursors instead of GLOBAL cursors to limit their scope to the current stored procedure or batch. This helps prevent unintended interactions with other code.
  • Process data in batches when possible rather than one row at a time. Some scenarios allow you to fetch and process multiple rows in each iteration, which can improve performance.

The Bottom Line

Cursors are a legitimate SQL feature that serves a specific purpose, but they’re often misused by developers who are more comfortable with procedural programming than set-based thinking. The general advice to avoid cursors is quite valid, and it’s based on real performance concerns.

If you find yourself reaching for a cursor, take a step back and ask whether there’s a set-based way to accomplish the same goal. More often than not, there is. But if you’ve carefully considered the alternatives and a cursor truly is the best solution for your specific situation, use it confidently and follow best practices to minimize its impact.