Cursor Concept - Cursors in SQL | OneNoughtOne

Database Management SystemsCursors

Cursors in SQL

LevelAdvanced

Duration90 mins

TopicCursors

1 / 5

Cursor Concept

Beyond Set-Based Processing

SQL is fundamentally a set-based language. Every SELECT statement returns a result set—a collection of rows that match your query criteria. The database processes these sets as atomic units: entire tables are joined, entire result sets are filtered, entire columns are aggregated. This set-based paradigm is powerful, elegant, and highly optimized by modern database engines.

But what happens when you need to process data one row at a time? What if your business logic requires examining each row individually, making decisions based on the current row's values, or performing operations that can't be expressed as set transformations?

Consider these scenarios:

Applying complex, conditional business rules that depend on accumulated state
Updating rows where the new value depends on calculations from previously processed rows
Interfacing with external systems that accept single records
Building audit trails that track each individual change
Performing procedural operations that simply cannot be expressed in declarative SQL

This is where cursors enter the picture. A cursor is a database object that enables row-at-a-time processing within SQL, bridging the gap between set-based query results and procedural logic that operates on individual records.

What You Will Learn

By the end of this page, you will understand the fundamental concept of cursors: what they are, why they exist, how they differ from standard set-based processing, their internal architecture, and the critical mental model for thinking about cursor-based data access. This conceptual foundation is essential before learning cursor syntax and operations.

What Is a Cursor?

A cursor is a database mechanism that enables row-by-row traversal of a query result set. The name "cursor" comes from the concept of a pointer that indicates a current position within a sequence of records—just as a cursor on your screen indicates where the next character will appear.

Formal Definition:

A cursor is a database object that provides a way to:

Execute a query and create a result set in memory
Maintain a pointer to a specific row within that result set
Fetch rows one at a time (or in blocks)
Optionally update or delete the currently pointed-to row
Navigate through the result set in various directions (depending on cursor type)

The cursor acts as an iterator—a concept familiar from programming languages—that sequentially accesses elements of a collection without exposing the underlying data structure.

Converting Mermaid diagram...

Key Characteristics of Cursors:

1. Stateful Navigation: Unlike a SELECT that returns all rows at once, a cursor maintains state—it remembers which row is "current" and allows movement from that position.

2. Encapsulated Result Set: The cursor encapsulates the query result, providing controlled access rather than delivering the entire set to the client at once.

3. Iterative Processing: Cursors enable loops in SQL: fetch a row, process it, fetch the next, repeat until done.

4. Positioned Operations: Many cursor implementations allow UPDATE or DELETE "WHERE CURRENT OF cursor_name," modifying the exact row the cursor points to.

5. Resource Management: Cursors consume server resources (memory, locks) that must be explicitly managed through open/close lifecycle operations.

The Iterator Pattern

If you've programmed in languages like Java, Python, or C#, you're familiar with iterators: objects that traverse collections one element at a time. A cursor is the SQL equivalent—it's how you iterate over a result set when set-based operations aren't sufficient for your needs.

Set-Based vs. Row-Based Processing

To truly understand cursors, we must first deeply appreciate the paradigm they transcend: set-based processing. This contrast illuminates when cursors are appropriate—and more importantly, when they're not.

Set-Based Processing (The SQL Way):

SQL queries operate on entire sets of data simultaneously. When you write:

UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

The database doesn't loop through engineering employees one by one. It identifies the entire set of matching rows, applies the transformation to all of them in one logical operation, and uses highly optimized internal algorithms (batch processing, parallel execution, vectorization) to complete the work efficiently.

Row-Based Processing (The Procedural Way):

Cursor-based processing is fundamentally different. The equivalent operation with a cursor would involve:

Open a cursor on engineering employees
Fetch the first employee
Calculate 1.10 × current salary
Update that specific row
Fetch the next employee
Repeat until no more rows
Close the cursor

This procedural approach gives you explicit control over each row but sacrifices the optimizations the database can apply to set operations.

Set-Based Processing

•Declarative: Describe WHAT you want, not HOW
•Optimized: Database engine chooses best execution strategy
•Parallel: Operations can be parallelized across CPU cores
•Batch Efficient: Reduces round-trip overhead
•Lock Efficient: Locks acquired and released in optimal patterns
•Scalable: Performance degrades gracefully with data size
•Preferred: Default approach for most SQL operations

Row-Based (Cursor) Processing

•Procedural: Specify exact HOW step by step
•Manual Control: You determine the execution path
•Sequential: Each row processed before the next
•High Overhead: Each FETCH is a separate operation
•Lock Duration: May hold locks for entire processing time
•Linear Scaling: Performance directly proportional to row count
•Special Cases: Used when set operations are insufficient

The Performance Reality:

Consider processing 100,000 rows. With set-based operations:

Database identifies all matching rows using indexes
Optimizer chooses parallel execution paths
Data is processed in cache-efficient batches
Total time: milliseconds to seconds

With cursor-based processing:

Each row requires a FETCH operation
100,000 fetch operations with individual round-trips
No parallelization possible
Context switching overhead for each row
Total time: seconds to minutes (or worse)

This performance difference isn't marginal—it can be orders of magnitude. A 100× slowdown is common when replacing a set operation with cursor iteration.

Why Do Cursors Exist Then?

Given the performance implications, why would anyone use cursors? Because some problems genuinely can't be solved with set operations:

Running totals with complex reset logic
Data validation with inter-row dependencies
External system integration row-by-row
Complex procedural logic within stored procedures
Legacy system interfaces expecting record iteration

The key is recognizing when a cursor is truly necessary versus when you're simply more comfortable with procedural thinking.

The Cardinal Rule

Before reaching for a cursor, always ask: 'Can this be done with a single SQL statement?' Set-based solutions should be your default. Use cursors only when procedural logic is genuinely required—not because you're more familiar with loops than with SQL thinking.

Cursor Architecture Deep Dive

Understanding how cursors work internally helps you use them effectively and avoid common pitfalls. Let's examine the architecture of cursor implementation within a database management system.

Cursor Components:

A cursor implementation typically involves several internal structures:

Internal Components of a Database Cursor
Component	Purpose	Characteristics
Query Plan	Execution strategy for the cursor's SELECT	Compiled and cached; reused for subsequent opens
Result Set Buffer	Memory area holding fetched rows	Size varies by cursor type; may be partial or complete
Position Pointer	Indicates current row within result set	Updated with each FETCH; tracks navigation state
Context Block	Metadata about cursor state	Open/closed status, fetch count, column metadata
Lock Resources	Locks held by the cursor	Depends on isolation level and cursor type
Cursor Handle	Identifier for application access	Named (SQL) or numeric (database driver)

Converting Mermaid diagram...

The Cursor Lifecycle:

Every cursor follows a defined lifecycle with distinct phases:

1. Declaration Phase: The cursor is defined, associating it with a SELECT statement. At this point, no query execution occurs—you're simply defining what the cursor will do when opened.

2. Open Phase: The cursor is opened, which executes the underlying query. The result set is materialized (either fully or partially, depending on cursor type), and the position pointer is initialized.

3. Fetch Phase: Rows are retrieved one at a time (or in batches). Each FETCH moves the position pointer and returns row data to the caller. Optional operations (UPDATE, DELETE WHERE CURRENT) may occur.

4. Close Phase: The cursor is closed, releasing server-side resources. The result set buffer is freed, locks are released, but the cursor definition remains for potential reopening.

5. Deallocation Phase (Optional): The cursor is completely removed from memory, including its definition. Required in some database systems; automatic in others.

cursor_lifecycle.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Complete cursor lifecycle demonstration
-- (Generic SQL syntax - specific syntax varies by DBMS)
 
-- 1. DECLARATION PHASE
-- Define the cursor and its associated query
DECLARE order_cursor CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date;
 
-- At this point, NO query has executed
-- The cursor is just a definition waiting to be used
 
-- 2. OPEN PHASE
-- Execute the query and create result set
OPEN order_cursor;
-- Now the SELECT runs, result set is created
-- Position pointer is at "before first row"
 
-- 3. FETCH PHASE (repeated as needed)
DECLARE @order_id INT, @customer_id INT, @amount DECIMAL(10,2);
 
FETCH NEXT FROM order_cursor 
INTO @order_id, @customer_id, @amount;
-- Position now at Row 1
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process current row
    PRINT 'Processing Order: ' + CAST(@order_id AS VARCHAR);
    
    -- Optionally update current row
    UPDATE orders 
    SET status = 'processing'
    WHERE CURRENT OF order_cursor;
    
    -- Move to next row
    FETCH NEXT FROM order_cursor 
    INTO @order_id, @customer_id, @amount;
END;
 
-- 4. CLOSE PHASE
-- Release result set buffer and locks
CLOSE order_cursor;
-- Cursor can be reopened if needed
 
-- 5. DEALLOCATION PHASE
-- Remove cursor definition entirely
DEALLOCATE order_cursor;
-- Cursor no longer exists

Resource Management Is Critical

Failure to properly close and deallocate cursors is a common source of memory leaks and lock contention in database applications. Always ensure cursor cleanup occurs, even when exceptions interrupt normal processing. Use TRY/CATCH blocks or equivalent error handling to guarantee cleanup.

Result Set Materialization Strategies

When a cursor is opened, the DBMS must materialize the result set—but how and when this happens varies significantly based on cursor type and database implementation. Understanding materialization is crucial for predicting cursor behavior and resource consumption.

Materialization Strategies:

Cursor Result Set Materialization Approaches
Strategy	Description	Memory Usage	Consistency	Use Case
Full Materialization	Entire result set copied to temporary storage at OPEN	High (proportional to result size)	Snapshot at open time	Static cursors; small result sets
Partial Materialization	Rows fetched in blocks as needed	Moderate (buffer size)	May see concurrent changes	Keyset cursors; pagination
Direct Navigation	No copy; cursor navigates base tables directly	Low (cursor state only)	Sees all concurrent changes	Dynamic cursors; real-time data
Hybrid	Key values materialized; data fetched on demand	Moderate (keys only)	Stable membership, fresh values	Keyset-driven cursors

Full Materialization (Static/Insensitive Cursor):

With full materialization, opening the cursor causes the entire result set to be copied into temporary storage (typically tempdb in SQL Server, work tables in Oracle). The cursor then operates on this copy, completely isolated from changes to the base tables.

Advantages:

Complete isolation from concurrent modifications
Consistent iteration—no phantom rows or disappearing rows
Can scroll backward without re-executing queries

Disadvantages:

Memory consumption proportional to result set size
Opening a cursor on millions of rows consumes substantial resources
Data staleness—you're working with a snapshot that becomes outdated

Converting Mermaid diagram...

Partial Materialization (Keyset Cursor):

Keyset cursors materialize only the keys (primary key or unique identifier values) of matching rows. When you FETCH, the cursor uses the stored key to retrieve current column values from the base table.

Result Set Membership:

Membership is fixed at OPEN time (like static)
Only rows with keys in the keyset are visible
Rows added after OPEN are not seen (no phantoms)
Rows deleted after OPEN cause FETCH to return 'row missing' status

Data Values:

Values are fetched fresh from base tables
UPDATE by other users is visible
You see current data, not snapshot data

Direct Navigation (Dynamic Cursor):

Dynamic cursors don't materialize any data. Each FETCH navigates the base tables directly, meaning you see:

Rows added by other users (phantoms)
Rows deleted by other users (disappear)
All value changes in real-time

This provides maximum currency but:

No backward scrolling (can't seek to previous in an ever-changing set)
Unpredictable iteration—result set can change mid-traversal
Higher I/O cost per fetch

Choosing Materialization Strategy

Choose based on requirements: Need consistent snapshot? Use static. Need fresh values but stable membership? Use keyset. Need real-time view? Use dynamic (but handle the complexity). The default varies by database—understand what you're getting.

Cursors and Transaction Context

Cursors don't operate in isolation—they exist within a transaction context that profoundly affects their behavior, visibility, and performance. Understanding this relationship is essential for correct cursor usage.

Cursor Scope Within Transactions:

By default, cursors are bound to the current transaction. When a transaction commits or rolls back, cursors are typically closed automatically. This behavior varies:

SQL Server: Cursors declared without LOCAL or GLOBAL scope are transaction-bound
Oracle: Cursors survive COMMIT by default unless explicitly closed
PostgreSQL: All cursors exist within their declaring transaction

Implications:

cursor_transaction_examples.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Cursor behavior with transactions
 
-- Example 1: Cursor closed on COMMIT
BEGIN TRANSACTION;
 
DECLARE item_cursor CURSOR FOR
    SELECT item_id, quantity FROM inventory WHERE quantity < 10;
    
OPEN item_cursor;
 
FETCH NEXT FROM item_cursor INTO @id, @qty;
-- Process row...
 
COMMIT TRANSACTION;  -- Cursor is automatically closed!
 
FETCH NEXT FROM item_cursor INTO @id, @qty;  
-- ERROR: Cursor is not open
 
-- Example 2: Cursor that survives COMMIT (if supported)
-- SQL Server: Use CLOSE_ON_COMMIT = OFF with cursor options
DECLARE item_cursor CURSOR 
    GLOBAL  -- or use DECLARE ... WITH HOLD in PostgreSQL
FOR 
    SELECT item_id, quantity FROM inventory WHERE quantity < 10;
 
-- Example 3: Transaction isolation affects cursor visibility
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- With READ COMMITTED:
-- Static cursor: Sees snapshot at OPEN time
-- Dynamic cursor: May see different data on each FETCH
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- With SERIALIZABLE:
-- Range locks prevent phantoms even with dynamic cursors
-- But: Higher lock contention, potential deadlocks

Holdable Cursors:

Some databases support holdable cursors (also called WITH HOLD cursors) that remain open across transaction boundaries. This is useful when:

You need to iterate through data while committing changes incrementally
Long-running processes shouldn't hold a single transaction open
You want to release locks periodically but maintain cursor position

However, holdable cursors introduce complexity:

The result set must be preserved (typically materialized)
Positioned UPDATE/DELETE may not work after COMMIT
Resource consumption continues across transactions

Cursor Behavior Across Databases
Feature	SQL Server	Oracle	PostgreSQL	MySQL
Default Scope	Transaction	Session	Transaction	Session
Holdable Cursors	Via cursor variable	Native (default)	WITH HOLD syntax	Limited support
Survives COMMIT	No (by default)	Yes	Only with HOLD	Connection-bound
Implicit Close on ROLLBACK	Yes	Yes	Yes	Yes

Locking Behavior:

Cursors can hold locks on data for the duration of their operation, depending on:

1. Cursor Type:

Static cursors: Snapshot isolation, minimal ongoing locks
Dynamic cursors: May acquire and hold locks during navigation
FOR UPDATE cursors: Explicitly acquire update locks

2. Isolation Level:

READ UNCOMMITTED: Minimal locking, dirty reads possible
READ COMMITTED: Shared locks released after each fetch
REPEATABLE READ: Shared locks held until cursor closes
SERIALIZABLE: Range locks prevent phantoms

3. Fetch Duration: Slow processing between FETCHes can hold locks longer, increasing contention with other transactions.

Lock Duration with Cursors

A cursor that fetches rows slowly while performing complex processing between fetches can become a locking nightmare. Other transactions wait for locks the cursor holds. Design cursor operations to process rows quickly, or use appropriate isolation levels to minimize lock duration.

The Cursor Mental Model

To work effectively with cursors, you need a clear mental model of what's happening at each stage. Let's build that model using everyday analogies and precise technical understanding.

Analogy: The Library Book Retrieval System

Imagine a large library with millions of books. You want to review all books on database design:

Without a cursor (set-based): You request "all database design books." The librarian retrieves every matching book, stacks them on a cart, and delivers the entire collection. You have immediate access to all books but need space for the whole collection.

With a cursor: You ask the librarian to create a list of matching books (the result set). Then you say "bring me the first one." After reviewing it, you say "bring me the next one." The librarian maintains your position in the list (the cursor), fetching books one at a time. You only handle one book at a time.

Cursor Mental Model Components

•The Query (SELECT): The specification of what data you want—like the library catalog search criteria. Defines the potential result set.
•The Result Set: The collection of rows matching your query—like the list of matching books. May or may not be fully materialized.
•The Cursor Position: Your current location within the result set—like a bookmark. Can be 'before first', 'on row N', or 'after last'.
•FETCH Operation: The act of retrieving data at the current position and optionally moving—like requesting the next book in sequence.
•Current Row: The row at the cursor's current position—the book currently in your hands. Available for examination or modification.
•Cursor State: Whether the cursor is declared but not open, open and positioned, or closed—like whether your library session is active.

The Position Pointer Visualization:

Visualize the cursor position as an arrow pointing between rows:

 Position: Before First
 ┌─────────────────────────────────┐
 │  →  [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │  →  Row 1: Order #2001 ← Current│
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After three more FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │  →  Row 4: Order #2004 ← Current│
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After one more FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │  →  [Gap after last row]       │
 └─────────────────────────────────┘
 Status: No more data (@@FETCH_STATUS ≠ 0)

The Position Pointer Is Key

The cursor position pointer can be at three conceptual locations: before the first row (initial state after OPEN), on a specific row (after successful FETCH), or after the last row (when result set is exhausted). Understanding this helps you reason about FETCH behavior and loop termination conditions.

Historical Context and Evolution

Cursors have been part of relational databases since the early days, and understanding their historical context explains why they work the way they do—and why they're often overused or misunderstood.

Origins in Embedded SQL:

In the early days of relational databases (1970s-1980s), SQL was primarily used embedded within host programming languages like COBOL, C, and FORTRAN. These procedural languages processed data record-by-record—they had no native concept of "set operations."

Cursors were invented to bridge this gap:

The host program declares a cursor for a SQL query
The program opens the cursor, executing the query
The program loops, fetching one row at a time into host variables
Each row is processed using the host language's procedural constructs
The cursor is closed when processing completes

This pattern was essential because COBOL couldn't natively handle a set of 10,000 rows—it needed them delivered one at a time.

Evolution of Cursor Usage Over Time
Era	Primary Language	Cursor Role	Set Processing
1970s-1980s	COBOL, FORTRAN, C	Essential bridge to host	Not available in host
1990s	C, C++, Visual Basic	Common pattern for DB access	Limited; ORM emerging
2000s	Java, C#, Python	Less common; collections exist	Good; ORM widespread
2010s-Present	Modern languages + ORM	Specialized use cases	Excellent; set-based preferred

The Legacy Problem:

Many developers learned database programming in the procedural era. They internalized cursor-based patterns as "how you work with databases." This mindset persists even when:

Modern SQL supports complex set operations
Window functions handle many cursor use cases
CTEs and derived tables enable sophisticated transformations
ORMs and batch APIs process sets efficiently

The result: cursors are often used where set-based SQL would be faster, cleaner, and more maintainable.

When Cursors Were Revolutionary:

In their time, cursors solved real problems:

Provided transactional integrity for record-at-a-time processing
Enabled complex business logic that couldn't be expressed in early SQL
Allowed scrollable result sets before modern APIs existed
Offered positioned updates when alternatives didn't exist

Modern Alternatives to Many Cursor Uses:

Modern Alternatives to Cursors

•Window Functions: Running totals, rankings, row comparisons—previously required cursors, now elegant set operations
•Common Table Expressions (CTEs): Complex multi-step transformations without procedural iteration
•MERGE Statement: Conditional INSERT/UPDATE/DELETE in a single set-based operation
•APPLY Operator: Row-by-row function application within set-based queries (SQL Server, PostgreSQL)
•Batch APIs: Application-level efficient batch processing without database-side iteration
•Table-Valued Parameters: Pass entire result sets to procedures without row-by-row handling

Know the History, Choose the Future

Understanding why cursors exist helps you recognize when they're genuinely needed versus when they're a legacy pattern. The best database developers know cursors thoroughly—and use them rarely, only when truly necessary.

Summary: Understanding Cursors

We've established the conceptual foundation for understanding cursors in SQL. Before diving into syntax and operations, let's consolidate the essential concepts:

Key Takeaways

•Cursors enable row-by-row processing: They provide an iterator pattern for SQL result sets, allowing procedural logic to process one row at a time.
•Set-based processing is preferred: Cursors are almost always slower than equivalent set-based operations. Use them only when procedural logic is genuinely required.
•Cursors have a defined lifecycle: DECLARE → OPEN → FETCH (loop) → CLOSE → DEALLOCATE. Each phase has specific resource implications.
•Materialization strategies vary: Static cursors snapshot data; dynamic cursors see real-time changes; keyset cursors are hybrid. Choose based on consistency vs. concurrency needs.
•Transaction context matters: Cursors interact with transactions and locking. Understanding this prevents resource contention and unexpected behavior.
•The position pointer mental model: Visualize the cursor as a pointer that can be 'before first', 'on a row', or 'after last'. This clarifies FETCH behavior.
•Historical context explains usage patterns: Cursors solved embedded SQL challenges but are often overused today due to legacy mindsets.

What's Next:

Now that you understand what cursors are and why they exist, we'll move to the practical syntax for declaring cursors. The next page covers DECLARE CURSOR in depth—the various options for defining cursor behavior, scrollability, sensitivity, and update capability.

Page Complete

You now have a solid conceptual foundation for understanding database cursors. You understand what they are, when they're appropriate, how they differ from set-based processing, and the internal architecture that makes them work. Next, we'll learn how to declare cursors with precise control over their behavior.

1 / 5

Loading learning content...

Database Management SystemsCursors

Cursors in SQL

LevelAdvanced

Duration90 mins

TopicCursors

1 / 5

Cursor Concept

Beyond Set-Based Processing

Consider these scenarios:

Applying complex, conditional business rules that depend on accumulated state
Updating rows where the new value depends on calculations from previously processed rows
Interfacing with external systems that accept single records
Building audit trails that track each individual change
Performing procedural operations that simply cannot be expressed in declarative SQL

What You Will Learn

What Is a Cursor?

Formal Definition:

A cursor is a database object that provides a way to:

Execute a query and create a result set in memory
Maintain a pointer to a specific row within that result set
Fetch rows one at a time (or in blocks)
Optionally update or delete the currently pointed-to row
Navigate through the result set in various directions (depending on cursor type)

The cursor acts as an iterator—a concept familiar from programming languages—that sequentially accesses elements of a collection without exposing the underlying data structure.

Converting Mermaid diagram...

Key Characteristics of Cursors:

1. Stateful Navigation: Unlike a SELECT that returns all rows at once, a cursor maintains state—it remembers which row is "current" and allows movement from that position.

2. Encapsulated Result Set: The cursor encapsulates the query result, providing controlled access rather than delivering the entire set to the client at once.

3. Iterative Processing: Cursors enable loops in SQL: fetch a row, process it, fetch the next, repeat until done.

4. Positioned Operations: Many cursor implementations allow UPDATE or DELETE "WHERE CURRENT OF cursor_name," modifying the exact row the cursor points to.

5. Resource Management: Cursors consume server resources (memory, locks) that must be explicitly managed through open/close lifecycle operations.

The Iterator Pattern

Set-Based vs. Row-Based Processing

Set-Based Processing (The SQL Way):

SQL queries operate on entire sets of data simultaneously. When you write:

UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

Row-Based Processing (The Procedural Way):

Cursor-based processing is fundamentally different. The equivalent operation with a cursor would involve:

Open a cursor on engineering employees
Fetch the first employee
Calculate 1.10 × current salary
Update that specific row
Fetch the next employee
Repeat until no more rows
Close the cursor

This procedural approach gives you explicit control over each row but sacrifices the optimizations the database can apply to set operations.

Set-Based Processing

•Declarative: Describe WHAT you want, not HOW
•Optimized: Database engine chooses best execution strategy
•Parallel: Operations can be parallelized across CPU cores
•Batch Efficient: Reduces round-trip overhead
•Lock Efficient: Locks acquired and released in optimal patterns
•Scalable: Performance degrades gracefully with data size
•Preferred: Default approach for most SQL operations

Row-Based (Cursor) Processing

•Procedural: Specify exact HOW step by step
•Manual Control: You determine the execution path
•Sequential: Each row processed before the next
•High Overhead: Each FETCH is a separate operation
•Lock Duration: May hold locks for entire processing time
•Linear Scaling: Performance directly proportional to row count
•Special Cases: Used when set operations are insufficient

The Performance Reality:

Consider processing 100,000 rows. With set-based operations:

Database identifies all matching rows using indexes
Optimizer chooses parallel execution paths
Data is processed in cache-efficient batches
Total time: milliseconds to seconds

With cursor-based processing:

Each row requires a FETCH operation
100,000 fetch operations with individual round-trips
No parallelization possible
Context switching overhead for each row
Total time: seconds to minutes (or worse)

This performance difference isn't marginal—it can be orders of magnitude. A 100× slowdown is common when replacing a set operation with cursor iteration.

Why Do Cursors Exist Then?

Given the performance implications, why would anyone use cursors? Because some problems genuinely can't be solved with set operations:

Running totals with complex reset logic
Data validation with inter-row dependencies
External system integration row-by-row
Complex procedural logic within stored procedures
Legacy system interfaces expecting record iteration

The key is recognizing when a cursor is truly necessary versus when you're simply more comfortable with procedural thinking.

The Cardinal Rule

Cursor Architecture Deep Dive

Understanding how cursors work internally helps you use them effectively and avoid common pitfalls. Let's examine the architecture of cursor implementation within a database management system.

Cursor Components:

A cursor implementation typically involves several internal structures:

Internal Components of a Database Cursor
Component	Purpose	Characteristics
Query Plan	Execution strategy for the cursor's SELECT	Compiled and cached; reused for subsequent opens
Result Set Buffer	Memory area holding fetched rows	Size varies by cursor type; may be partial or complete
Position Pointer	Indicates current row within result set	Updated with each FETCH; tracks navigation state
Context Block	Metadata about cursor state	Open/closed status, fetch count, column metadata
Lock Resources	Locks held by the cursor	Depends on isolation level and cursor type
Cursor Handle	Identifier for application access	Named (SQL) or numeric (database driver)

Converting Mermaid diagram...

The Cursor Lifecycle:

Every cursor follows a defined lifecycle with distinct phases:

1. Declaration Phase: The cursor is defined, associating it with a SELECT statement. At this point, no query execution occurs—you're simply defining what the cursor will do when opened.

4. Close Phase: The cursor is closed, releasing server-side resources. The result set buffer is freed, locks are released, but the cursor definition remains for potential reopening.

5. Deallocation Phase (Optional): The cursor is completely removed from memory, including its definition. Required in some database systems; automatic in others.

cursor_lifecycle.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Complete cursor lifecycle demonstration
-- (Generic SQL syntax - specific syntax varies by DBMS)
 
-- 1. DECLARATION PHASE
-- Define the cursor and its associated query
DECLARE order_cursor CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date;
 
-- At this point, NO query has executed
-- The cursor is just a definition waiting to be used
 
-- 2. OPEN PHASE
-- Execute the query and create result set
OPEN order_cursor;
-- Now the SELECT runs, result set is created
-- Position pointer is at "before first row"
 
-- 3. FETCH PHASE (repeated as needed)
DECLARE @order_id INT, @customer_id INT, @amount DECIMAL(10,2);
 
FETCH NEXT FROM order_cursor 
INTO @order_id, @customer_id, @amount;
-- Position now at Row 1
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process current row
    PRINT 'Processing Order: ' + CAST(@order_id AS VARCHAR);
    
    -- Optionally update current row
    UPDATE orders 
    SET status = 'processing'
    WHERE CURRENT OF order_cursor;
    
    -- Move to next row
    FETCH NEXT FROM order_cursor 
    INTO @order_id, @customer_id, @amount;
END;
 
-- 4. CLOSE PHASE
-- Release result set buffer and locks
CLOSE order_cursor;
-- Cursor can be reopened if needed
 
-- 5. DEALLOCATION PHASE
-- Remove cursor definition entirely
DEALLOCATE order_cursor;
-- Cursor no longer exists

Resource Management Is Critical

Result Set Materialization Strategies

Materialization Strategies:

Cursor Result Set Materialization Approaches
Strategy	Description	Memory Usage	Consistency	Use Case
Full Materialization	Entire result set copied to temporary storage at OPEN	High (proportional to result size)	Snapshot at open time	Static cursors; small result sets
Partial Materialization	Rows fetched in blocks as needed	Moderate (buffer size)	May see concurrent changes	Keyset cursors; pagination
Direct Navigation	No copy; cursor navigates base tables directly	Low (cursor state only)	Sees all concurrent changes	Dynamic cursors; real-time data
Hybrid	Key values materialized; data fetched on demand	Moderate (keys only)	Stable membership, fresh values	Keyset-driven cursors

Full Materialization (Static/Insensitive Cursor):

Advantages:

Complete isolation from concurrent modifications
Consistent iteration—no phantom rows or disappearing rows
Can scroll backward without re-executing queries

Disadvantages:

Memory consumption proportional to result set size
Opening a cursor on millions of rows consumes substantial resources
Data staleness—you're working with a snapshot that becomes outdated

Converting Mermaid diagram...

Partial Materialization (Keyset Cursor):

Result Set Membership:

Membership is fixed at OPEN time (like static)
Only rows with keys in the keyset are visible
Rows added after OPEN are not seen (no phantoms)
Rows deleted after OPEN cause FETCH to return 'row missing' status

Data Values:

Values are fetched fresh from base tables
UPDATE by other users is visible
You see current data, not snapshot data

Direct Navigation (Dynamic Cursor):

Dynamic cursors don't materialize any data. Each FETCH navigates the base tables directly, meaning you see:

Rows added by other users (phantoms)
Rows deleted by other users (disappear)
All value changes in real-time

This provides maximum currency but:

No backward scrolling (can't seek to previous in an ever-changing set)
Unpredictable iteration—result set can change mid-traversal
Higher I/O cost per fetch

Choosing Materialization Strategy

Cursors and Transaction Context

Cursor Scope Within Transactions:

By default, cursors are bound to the current transaction. When a transaction commits or rolls back, cursors are typically closed automatically. This behavior varies:

SQL Server: Cursors declared without LOCAL or GLOBAL scope are transaction-bound
Oracle: Cursors survive COMMIT by default unless explicitly closed
PostgreSQL: All cursors exist within their declaring transaction

Implications:

cursor_transaction_examples.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Cursor behavior with transactions
 
-- Example 1: Cursor closed on COMMIT
BEGIN TRANSACTION;
 
DECLARE item_cursor CURSOR FOR
    SELECT item_id, quantity FROM inventory WHERE quantity < 10;
    
OPEN item_cursor;
 
FETCH NEXT FROM item_cursor INTO @id, @qty;
-- Process row...
 
COMMIT TRANSACTION;  -- Cursor is automatically closed!
 
FETCH NEXT FROM item_cursor INTO @id, @qty;  
-- ERROR: Cursor is not open
 
-- Example 2: Cursor that survives COMMIT (if supported)
-- SQL Server: Use CLOSE_ON_COMMIT = OFF with cursor options
DECLARE item_cursor CURSOR 
    GLOBAL  -- or use DECLARE ... WITH HOLD in PostgreSQL
FOR 
    SELECT item_id, quantity FROM inventory WHERE quantity < 10;
 
-- Example 3: Transaction isolation affects cursor visibility
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- With READ COMMITTED:
-- Static cursor: Sees snapshot at OPEN time
-- Dynamic cursor: May see different data on each FETCH
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- With SERIALIZABLE:
-- Range locks prevent phantoms even with dynamic cursors
-- But: Higher lock contention, potential deadlocks

Holdable Cursors:

Some databases support holdable cursors (also called WITH HOLD cursors) that remain open across transaction boundaries. This is useful when:

You need to iterate through data while committing changes incrementally
Long-running processes shouldn't hold a single transaction open
You want to release locks periodically but maintain cursor position

However, holdable cursors introduce complexity:

The result set must be preserved (typically materialized)
Positioned UPDATE/DELETE may not work after COMMIT
Resource consumption continues across transactions

Cursor Behavior Across Databases
Feature	SQL Server	Oracle	PostgreSQL	MySQL
Default Scope	Transaction	Session	Transaction	Session
Holdable Cursors	Via cursor variable	Native (default)	WITH HOLD syntax	Limited support
Survives COMMIT	No (by default)	Yes	Only with HOLD	Connection-bound
Implicit Close on ROLLBACK	Yes	Yes	Yes	Yes

Locking Behavior:

Cursors can hold locks on data for the duration of their operation, depending on:

1. Cursor Type:

Static cursors: Snapshot isolation, minimal ongoing locks
Dynamic cursors: May acquire and hold locks during navigation
FOR UPDATE cursors: Explicitly acquire update locks

2. Isolation Level:

READ UNCOMMITTED: Minimal locking, dirty reads possible
READ COMMITTED: Shared locks released after each fetch
REPEATABLE READ: Shared locks held until cursor closes
SERIALIZABLE: Range locks prevent phantoms

3. Fetch Duration: Slow processing between FETCHes can hold locks longer, increasing contention with other transactions.

Lock Duration with Cursors

The Cursor Mental Model

To work effectively with cursors, you need a clear mental model of what's happening at each stage. Let's build that model using everyday analogies and precise technical understanding.

Analogy: The Library Book Retrieval System

Imagine a large library with millions of books. You want to review all books on database design:

Cursor Mental Model Components

•The Query (SELECT): The specification of what data you want—like the library catalog search criteria. Defines the potential result set.
•The Result Set: The collection of rows matching your query—like the list of matching books. May or may not be fully materialized.
•The Cursor Position: Your current location within the result set—like a bookmark. Can be 'before first', 'on row N', or 'after last'.
•FETCH Operation: The act of retrieving data at the current position and optionally moving—like requesting the next book in sequence.
•Current Row: The row at the cursor's current position—the book currently in your hands. Available for examination or modification.
•Cursor State: Whether the cursor is declared but not open, open and positioned, or closed—like whether your library session is active.

The Position Pointer Visualization:

Visualize the cursor position as an arrow pointing between rows:

 Position: Before First
 ┌─────────────────────────────────┐
 │  →  [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │  →  Row 1: Order #2001 ← Current│
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After three more FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │  →  Row 4: Order #2004 ← Current│
 │     [Gap after last row]       │
 └─────────────────────────────────┘

 After one more FETCH NEXT:
 ┌─────────────────────────────────┐
 │     [Gap before first row]     │
 │     Row 1: Order #2001         │
 │     Row 2: Order #2002         │
 │     Row 3: Order #2003         │
 │     Row 4: Order #2004         │
 │  →  [Gap after last row]       │
 └─────────────────────────────────┘
 Status: No more data (@@FETCH_STATUS ≠ 0)

The Position Pointer Is Key

Historical Context and Evolution

Origins in Embedded SQL:

Cursors were invented to bridge this gap:

The host program declares a cursor for a SQL query
The program opens the cursor, executing the query
The program loops, fetching one row at a time into host variables
Each row is processed using the host language's procedural constructs
The cursor is closed when processing completes

This pattern was essential because COBOL couldn't natively handle a set of 10,000 rows—it needed them delivered one at a time.

Evolution of Cursor Usage Over Time
Era	Primary Language	Cursor Role	Set Processing
1970s-1980s	COBOL, FORTRAN, C	Essential bridge to host	Not available in host
1990s	C, C++, Visual Basic	Common pattern for DB access	Limited; ORM emerging
2000s	Java, C#, Python	Less common; collections exist	Good; ORM widespread
2010s-Present	Modern languages + ORM	Specialized use cases	Excellent; set-based preferred

The Legacy Problem:

Many developers learned database programming in the procedural era. They internalized cursor-based patterns as "how you work with databases." This mindset persists even when:

Modern SQL supports complex set operations
Window functions handle many cursor use cases
CTEs and derived tables enable sophisticated transformations
ORMs and batch APIs process sets efficiently

The result: cursors are often used where set-based SQL would be faster, cleaner, and more maintainable.

When Cursors Were Revolutionary:

In their time, cursors solved real problems:

Provided transactional integrity for record-at-a-time processing
Enabled complex business logic that couldn't be expressed in early SQL
Allowed scrollable result sets before modern APIs existed
Offered positioned updates when alternatives didn't exist

Modern Alternatives to Many Cursor Uses:

Modern Alternatives to Cursors

•Window Functions: Running totals, rankings, row comparisons—previously required cursors, now elegant set operations
•Common Table Expressions (CTEs): Complex multi-step transformations without procedural iteration
•MERGE Statement: Conditional INSERT/UPDATE/DELETE in a single set-based operation
•APPLY Operator: Row-by-row function application within set-based queries (SQL Server, PostgreSQL)
•Batch APIs: Application-level efficient batch processing without database-side iteration
•Table-Valued Parameters: Pass entire result sets to procedures without row-by-row handling

Know the History, Choose the Future

Summary: Understanding Cursors

We've established the conceptual foundation for understanding cursors in SQL. Before diving into syntax and operations, let's consolidate the essential concepts:

Key Takeaways

•Cursors enable row-by-row processing: They provide an iterator pattern for SQL result sets, allowing procedural logic to process one row at a time.
•Set-based processing is preferred: Cursors are almost always slower than equivalent set-based operations. Use them only when procedural logic is genuinely required.
•Cursors have a defined lifecycle: DECLARE → OPEN → FETCH (loop) → CLOSE → DEALLOCATE. Each phase has specific resource implications.
•Materialization strategies vary: Static cursors snapshot data; dynamic cursors see real-time changes; keyset cursors are hybrid. Choose based on consistency vs. concurrency needs.
•Transaction context matters: Cursors interact with transactions and locking. Understanding this prevents resource contention and unexpected behavior.
•The position pointer mental model: Visualize the cursor as a pointer that can be 'before first', 'on a row', or 'after last'. This clarifies FETCH behavior.
•Historical context explains usage patterns: Cursors solved embedded SQL challenges but are often overused today due to legacy mindsets.

What's Next:

Page Complete

1 / 5