SQL SCROLL

What is SQL SCROLL?

Adds scrollability to a cursor so rows can be fetched in any direction, not just forward.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SCROLL: PostgreSQL, SQL Server (STATIC or SCROLL), IBM Db2, MariaDB (from 10.5), Oracle (within PL/SQL REF CURSOR as scrollable), not supported in MySQL or SQLite

SQL SCROLL Full Explanation

SCROLL is a cursor attribute defined in the SQL standard and implemented by several databases, notably PostgreSQL. When you declare a cursor with the SCROLL keyword, the database engine builds internal structures that allow the cursor to move both forward and backward through the result set. This enables FETCH statements such as PRIOR, ABSOLUTE n, or RELATIVE n. Without SCROLL, a cursor is typically forward-only, meaning you can FETCH NEXT repeatedly but cannot revisit earlier rows. Scrollable cursors are valuable for pagination, bi-directional navigation, and algorithms that must look ahead or behind in a result set. They do, however, consume more memory and may prevent certain optimizations because the engine must be able to reposition the cursor at will. Some databases allow SCROLL only on read-only or insensitive cursors, while others materialize the full result set in temp storage. Always benchmark if performance matters.

SQL SCROLL Syntax

DECLARE cursor_name SCROLL CURSOR FOR
    select_statement;

SQL SCROLL Parameters

cursor_name (identifier) - The name assigned to the cursor
select_statement (SQL) - Any valid SELECT that produces the cursor's result set

Example Queries Using SQL SCROLL

-- Open a scrollable cursor
BEGIN;
DECLARE user_cur SCROLL CURSOR FOR
    SELECT id, email FROM users ORDER BY id;

-- Jump to the 10th row
FETCH ABSOLUTE 10 FROM user_cur;

-- Move back two rows
FETCH RELATIVE -2 FROM user_cur;

-- Rewind to the first row
FETCH FIRST FROM user_cur;

CLOSE user_cur;
COMMIT;

Expected Output Using SQL SCROLL

Each FETCH returns the requested row(s) from the cursor in the specified position
The cursor can move forward or backward without error until it is closed

Use Cases with SQL SCROLL

Implementing bidirectional pagination in stored procedures
Algorithms that require look-ahead or look-behind logic
Interactive applications where users scroll up and down a result grid
Generating reports that revisit earlier rows for running totals

Common Mistakes with SQL SCROLL

Omitting SCROLL and later attempting FETCH PRIOR, causing an error
Assuming performance is identical to forward-only cursors
Using SCROLL on databases or drivers that do not support it
Forgetting to CLOSE the cursor, leading to memory leaks

First Introduced In

SQL-92 (as part of the ISO/IEC SQL standard)

Frequently Asked Questions

What is the difference between SCROLL and NO SCROLL?

SCROLL allows a cursor to move backward, jump to absolute positions, or fetch relative rows. NO SCROLL (or omitting SCROLL) restricts the cursor to forward-only FETCH NEXT operations.

Does SCROLL work with UPDATE cursors?

In most databases SCROLL is allowed only on read-only or INSENSITIVE cursors. Attempting to update through a SCROLL cursor may raise an error or silently degrade to forward-only.

How do I check if my database supports SCROLL?

Consult your database documentation. PostgreSQL, SQL Server, and Db2 support it natively. MySQL and SQLite do not. You can also attempt DECLARE my_cur SCROLL CURSOR FOR SELECT 1; and see if it compiles.

Are there performance considerations?

Yes. Scrollable cursors often require the database to buffer the full result set or maintain extra metadata. Use SCROLL only when bidirectional navigation is necessary.