SQL CURSORS: The History and Motivation, Understanding How They Work

Introduction

Ah, SQL cursors. The lovechild of procedural programming and databases that nobody asked for, but here we are.

At some point, every developer working with databases runs into them.

Maybe you’ve read about them in dark corners of database documentation, or perhaps you’ve found yourself debugging someone else’s cursor-laden stored procedure while questioning your career choices.

A (Brief and Slightly Dramatic) History of SQL Cursors

Once upon a time, databases were simple. You wrote SQL queries, they executed, and life was good. But then, people got greedy. They wanted row-by-row processing, despite relational databases being designed for set-based operations.

Enter SQL cursors: a feature introduced to help process rows individually, much like iterating over a list in a programming language.

At first, cursors seemed like a great idea—structured iteration within SQL. But soon, developers realized something horrifying: cursors can be slow. Like, molasses in winter slow. And thus, the great debate began: To cursor, or not to cursor?

Understanding How SQL Cursors Work

What is a Cursor?

A cursor is a database object that allows you to iterate over query results one row at a time. Think of it like an old-school record player—it moves from row to row, one at a time.

Steps to Use a Cursor

Declare the cursor (define what data it will iterate over).
Open the cursor (execute the query and store the result set).
Fetch rows from the cursor (one row at a time).
Process the row (do something useful).
Close the cursor (release resources).
Deallocate the cursor (get rid of it, like an ex you don’t want to see again).

Code Examples

Let’s see how cursors work in MSSQL, MySQL, and PostgreSQL.

Cursor in MSSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
DECLARE @customerName VARCHAR(100);
DECLARE customer_cursor CURSOR FOR
SELECT name FROM Customers;

OPEN customer_cursor;
FETCH NEXT FROM customer_cursor INTO @customerName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing customer: ' + @customerName;
    FETCH NEXT FROM customer_cursor INTO @customerName;
END;

CLOSE customer_cursor;
DEALLOCATE customer_cursor;

Cursor in MySQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER $$

CREATE PROCEDURE process_customers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE customerName VARCHAR(100);
    DECLARE customer_cursor CURSOR FOR SELECT name FROM Customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN customer_cursor;

    read_loop: LOOP
        FETCH customer_cursor INTO customerName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT CONCAT('Processing customer: ', customerName);
    END LOOP;

    CLOSE customer_cursor;
END $$

DELIMITER ;

Cursor in PostgreSQL

1
2
3
4
5
6
7
8
DO $$ 
DECLARE 
    customer_rec RECORD;
BEGIN
    FOR customer_rec IN SELECT name FROM Customers LOOP
        RAISE NOTICE 'Processing customer: %', customer_rec.name;
    END LOOP;
END $$;

When to Use Cursors (And When to Run Away)

Use Cursors When:

✅ You need row-by-row processing that can’t be done with a set-based approach.
✅ You’re dealing with complex business logic that must process each row separately.
✅ You enjoy suffering (just kidding… mostly).

Avoid Cursors When:

🚫 You can achieve the same result with set-based operations (which are faster).
🚫 You’re working with large datasets (cursors can be horribly slow).
🚫 You value your database performance.

Pro Tip: If you find yourself writing a cursor, stop and ask: Can I do this with a JOIN, a CTE, or a window function? If yes, do that instead.

Conclusion

SQL cursors are both a gift and a curse. They provide a way to process rows sequentially when needed, but they can also be performance killers if misused. Understanding their role and limitations is key to using them effectively.

If you’re a finite state machine nerd, you’ll appreciate the similarity in how cursors move between rows like states in an FSM. But if you’re a database performance enthusiast, you’ll probably want to avoid them as much as possible.

Key Ideas

SQL cursors allow row-by-row processing but are often slow.
They have clear similarities to finite state machines.
MSSQL, MySQL, and PostgreSQL all support cursors, but syntax varies.
Cursors should be avoided when set-based operations can be used instead.
Always ask: Can I do this without a cursor? If yes, then don’t use one.

SQL CURSORS In a Nutshell

Code Examples in MSSQL, MySql and Postgres