SQL SENSITIVE

What is the SQL SENSITIVE keyword?

Marks a cursor as sensitive so it can see row changes committed 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 SENSITIVE:

SQL SENSITIVE Full Explanation

SENSITIVE is an optional cursor attribute defined in the SQL standard and implemented in a few enterprise databases. When a cursor is declared as SENSITIVE, the database engine tries to ensure that each FETCH reflects committed inserts, updates, or deletes made by other transactions after the cursor was opened. This contrasts with INSENSITIVE (a static snapshot) and ASENSITIVE (leave the choice to the optimizer). Because the engine must continually reconcile the result set with the underlying tables, SENSITIVE cursors can consume more resources and may behave differently depending on the transaction isolation level. Implementations treat the keyword as a request, not an absolute guarantee: some optimizers silently downgrade the cursor to ASENSITIVE or INSENSITIVE if they cannot provide sensitivity efficiently.

SQL SENSITIVE Syntax

DECLARE cursor_name SENSITIVE [SCROLL] CURSOR FOR
    select_statement;

SQL SENSITIVE Parameters

Example Queries Using SQL SENSITIVE

-- Example 1: Sensitive scroll cursor in a stored procedure
DECLARE employee_cur SENSITIVE SCROLL CURSOR FOR
    SELECT id, name, salary
    FROM hr.employees
    WHERE department_id = :dept_id
    ORDER BY id;

OPEN employee_cur;
FETCH NEXT FROM employee_cur INTO v_id, v_name, v_salary;
-- If another session updates a salary, the next fetch reflects the change.

-- Example 2: Basic sensitive cursor
BEGIN
    DECLARE order_cur SENSITIVE CURSOR FOR
        SELECT order_id, status FROM sales.orders;
    OPEN order_cur;
    -- Application loop fetching rows
END;

Expected Output Using SQL SENSITIVE

The cursor opens successfully
Each FETCH returns the current committed data, so later FETCH calls may show rows changed by other sessions after the cursor was opened

Use Cases with SQL SENSITIVE

Real-time dashboards or monitoring tools that must see the latest committed data while scrolling through a result set.
OLTP applications that iterate over a small set of keys and require up-to-date values without reissuing the query.
Situations where business rules demand immediate visibility into concurrent transactions, but a full requery would be too heavy.

Common Mistakes with SQL SENSITIVE

Assuming every database supports SENSITIVE – many ignore or reject the keyword.
Expecting a hard guarantee; the optimizer may quietly fall back to an insensitive plan.
Forgetting that sensitivity is still bounded by the current isolation level (e.g., REPEATABLE READ may block updates or hide them).
Using SENSITIVE cursors for large result sets, leading to performance degradation.

First Introduced In

SQL:1999 standard; first vendor adoption in IBM Db2 7.1

Frequently Asked Questions

What performance impact does SENSITIVE have?

A SENSITIVE cursor requires the database to track row modifications, which can add CPU, memory, and I/O overhead, especially for large result sets.

Can I combine SENSITIVE with UPDATE cursors?

Yes. Declaring a SENSITIVE cursor with the FOR UPDATE clause lets you both see external changes and issue positioned updates, provided your engine supports both features.

How is SENSITIVE different from ASENSITIVE?

SENSITIVE explicitly requests live visibility of row changes. ASENSITIVE leaves the choice to the optimizer; it may behave as either SENSITIVE or INSENSITIVE depending on cost estimates.

Does SENSITIVE override transaction isolation levels?

No. Isolation levels still apply. Under SERIALIZABLE you may not see concurrent changes even with SENSITIVE, while under READ COMMITTED you typically will.