SQL INSENSITIVE

What does the SQL INSENSITIVE keyword do?

INSENSITIVE defines a scrollable cursor whose result set does not reflect changes made to the underlying tables after the cursor is opened.

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 INSENSITIVE: ISO/IEC Standard SQL, IBM Db2, SAP HANA. Not supported in PostgreSQL, MySQL, SQL Server, Oracle, or SQLite.

SQL INSENSITIVE Full Explanation

INSENSITIVE is an optional cursor attribute in the ISO SQL standard. When a cursor is declared INSENSITIVE, the database engine materializes or snapshots the query result at the moment the cursor is opened. Subsequent inserts, updates, or deletes committed by other transactions on the underlying tables are not visible through FETCH operations issued on that cursor. Updates performed through the cursor itself remain visible, allowing positioned UPDATE or DELETE statements to succeed on the buffered rows. Because the engine may store the snapshot in temporary space, INSENSITIVE cursors often consume more memory or disk than their sensitive counterparts. Some vendors ignore the keyword or silently convert it to an internal cursor type that matches their implementation limits, so always verify vendor documentation.

SQL INSENSITIVE Syntax

DECLARE cursor_name INSENSITIVE [SCROLL] CURSOR FOR
    select_statement;

SQL INSENSITIVE Parameters

- cursor_name (identifier) - Name assigned to the cursor.
- select_statement (SQL query) - Defines the result set over which the cursor will iterate.
- SCROLL (optional) - keyword - Allows fetching rows in any direction.

Example Queries Using SQL INSENSITIVE

-- Create an insensitive scroll cursor
DECLARE emp_cur INSENSITIVE SCROLL CURSOR FOR
    SELECT employee_id, last_name, salary FROM hr.employees;

OPEN emp_cur;

-- Concurrent session raises an employee salary here, but our cursor will not see it.

FETCH NEXT FROM emp_cur;
FETCH PRIOR FROM emp_cur;

CLOSE emp_cur;
DEALLOCATE CURSOR emp_cur;

Expected Output Using SQL INSENSITIVE

The cursor opens with a snapshot of the employees table at OPEN time
FETCH calls return rows in that fixed order and with their original column values, ignoring later commits from other sessions

Use Cases with SQL INSENSITIVE

Generate stable, page-by-page reports even while other users modify data.
Protect long-running application logic from mid-transaction data drift.
Implement repeatable reads without raising transaction isolation level globally.

Common Mistakes with SQL INSENSITIVE

Assuming every database honors INSENSITIVE; several silently ignore it.
Believing the cursor is fully isolated: updates made through the cursor remain visible inside it.
Forgetting that buffering large result sets can exhaust temp space.

First Introduced In

SQL-92 (ISO/IEC 9075:1992)

Frequently Asked Questions

What happens if the database ignores INSENSITIVE?

If the vendor ignores the keyword, the cursor behaves as the default cursor type for that dialect, typically one that can see committed changes. Always test behavior in your target DBMS.

Does INSENSITIVE guarantee repeatable reads?

It guarantees repeatable reads only through that cursor. Other queries executed in the same transaction can still see concurrent changes unless the isolation level also blocks them.

Is an INSENSITIVE cursor updatable?

Yes. Rows fetched through the cursor can still be updated or deleted using positioned UPDATE and DELETE statements. Those changes become visible within the cursor.

Can INSENSITIVE be combined with WITH HOLD?

Yes in databases that support both features. WITH HOLD keeps the cursor open after COMMIT, while INSENSITIVE keeps its result set stable.