SQL READS

What is the SQL READS SQL DATA clause?

Routine characteristic declaring that a stored procedure or function may read but cannot modify database data.

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 READS: MySQL, MariaDB, IBM Db2, Amazon Redshift, SAP HANA, H2. Not supported in PostgreSQL, SQL Server, Oracle, SQLite.

SQL READS Full Explanation

The READS SQL DATA clause (often written together as READS SQL DATA or simply READS) is a routine characteristic defined in the SQL standard and implemented by several databases. When attached to CREATE PROCEDURE or CREATE FUNCTION, it signals that the routine is permitted to execute statements that read data (SELECT, read-only cursors) but is prohibited from executing data-modifying statements such as INSERT, UPDATE, DELETE, MERGE, TRUNCATE, or DDL. Marking a routine with READS SQL DATA lets the optimizer, security layer, and callers know that the routine is side-effect-free regarding modifications. Many engines treat READS SQL DATA routines as safe for use inside larger read-only transactions, in deterministic expressions, or in replication/parallel-execution contexts where write access is restricted. If the routine attempts to run a disallowed statement, the database raises an error at compile time (static checking) or run time (dynamic checking), depending on the engine. Other routine characteristics in the same family include NO SQL (no SQL statements at all), CONTAINS SQL (any SQL but no guarantee of read/write behavior), and MODIFIES SQL DATA (explicitly allows data changes). The SQL standard introduced these characteristics in SQL:1999, and implementations differ slightly in enforcement and naming. Always test your specific database behavior.

SQL READS Syntax

CREATE FUNCTION function_name(parameters)
RETURNS data_type
READS SQL DATA
BEGIN
    -- read-only logic here
    SELECT ... ;
END;

CREATE PROCEDURE procedure_name(parameters)
READS SQL DATA
BEGIN
    SELECT ... ;
END;

SQL READS Parameters

Example Queries Using SQL READS

-- Example 1: Create a read-only function
CREATE FUNCTION total_customers()
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM customers;
    RETURN cnt;
END;

-- Example 2: Attempting a disallowed write inside READS routine (will error)
CREATE PROCEDURE bad_proc()
READS SQL DATA
BEGIN
    UPDATE customers SET vip = TRUE; -- ERROR in compliant engines
END;

Expected Output Using SQL READS

Example 1: The function compiles successfully and can be called from SELECT statements
Example 2: The database rejects the CREATE statement (or raises a runtime error) because UPDATE is not allowed when READS SQL DATA is declared

Use Cases with SQL READS

Declare deterministic or read-only helper functions used in SELECT queries.
Ensure stored routines called within read-only transactions cannot accidentally modify data.
Improve security by limiting routine capabilities for less-trusted developers.
Allow replication systems to classify routines as safe for secondary replicas that are open for reads only.

Common Mistakes with SQL READS

Mixing READS SQL DATA with data-modifying statements, leading to compilation errors.
Assuming READS SQL DATA is the default (some engines default to MODIFIES SQL DATA/CONTAINS SQL if not specified).
Confusing READS SQL DATA with NO SQL (the latter forbids any SQL, including SELECT).
Believing all databases support the clause; many engines (e.g., SQL Server, Oracle) ignore or reject it.

First Introduced In

SQL:1999 standard (feature T331, Routine Characteristics)

Frequently Asked Questions

What is the difference between READS SQL DATA and NO SQL?

READS SQL DATA allows SELECT and other read-only operations but forbids data changes. NO SQL forbids all SQL statements, including SELECT.

Can I omit READS SQL DATA and still be read-only?

Yes, but the database may treat the routine as potentially modifying data, which can restrict where it is callable. Declaring it explicitly clarifies intent and can improve optimization.

How do I change a routine from MODIFIES SQL DATA to READS SQL DATA?

Alter or recreate the routine and replace the MODIFIES SQL DATA clause with READS SQL DATA. Ensure the body no longer contains write operations.

Which databases enforce READS SQL DATA strictly?

MySQL, MariaDB, and IBM Db2 perform compile-time checks. Some others accept the syntax but enforce it only at runtime or ignore it entirely.