SQL DETERMINISTIC

What is the SQL DETERMINISTIC keyword?

Marks a stored function or procedure as always returning the same result for the same input values.

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 DETERMINISTIC: MySQL 5.0+, MariaDB, Oracle, IBM DB2, Firebird. Not supported in PostgreSQL, SQL Server, SQLite.

SQL DETERMINISTIC Full Explanation

DETERMINISTIC is an optional attribute that can be added to a CREATE FUNCTION or CREATE PROCEDURE statement to assert that the routine is free of side effects and depends only on its input parameters. When a routine is declared DETERMINISTIC, the optimizer and replication engines can safely cache results, reorder calls, or skip logging the full statement text in favor of a checksum, which improves performance and binary-log efficiency. Incorrectly marking a routine as DETERMINISTIC when it reads tables, uses random numbers, or references dynamic context (such as NOW()) may lead to wrong query results, stale caches, or data drift between primary and replica servers.

SQL DETERMINISTIC Syntax

CREATE FUNCTION function_name (param datatype[, ...])
RETURNS return_datatype
DETERMINISTIC
[other_clauses]
BEGIN
   -- routine body
END;

SQL DETERMINISTIC Parameters

Example Queries Using SQL DETERMINISTIC

-- 1. Simple deterministic function
CREATE FUNCTION double_val(x INT)
RETURNS INT
DETERMINISTIC
RETURN x * 2;

-- 2. Using the function
SELECT double_val(10);  -- returns 20

Expected Output Using SQL DETERMINISTIC

The CREATE FUNCTION statement registers the routine with the DETERMINISTIC flag
Subsequent calls like SELECT double_val(10) return 20
The database may cache the result or skip re-evaluation when possible

Use Cases with SQL DETERMINISTIC

Declare pure mathematical functions so the optimizer can inline or cache them.
Ensure safe statement-based replication by marking routines that do not read data and have no side effects.
Improve performance for frequently called helper functions in SELECT clauses.

Common Mistakes with SQL DETERMINISTIC

Marking a routine DETERMINISTIC when it queries tables, uses RAND(), UUID(), NOW(), or user variables.
Forgetting to specify DETERMINISTIC when binary logging is in STATEMENT mode, causing errors like "This function is not deterministic and might not replicate correctly".
Assuming DETERMINISTIC implies the result is cached automatically in every engine; caching behavior is implementation-specific.

First Introduced In

SQL:1999 standard; MySQL 5.0 (2005)

Frequently Asked Questions

What does DETERMINISTIC add to a CREATE FUNCTION statement?

It asserts that the function will always return the same output for the same inputs, allowing the planner to cache or inline results and making statement-based replication safe.

How do I know if my function is truly deterministic?

Ensure the body does not read or modify tables, rely on session variables, call non-deterministic functions (RAND, NOW, UUID), or depend on environment state.

What happens if I wrongly label a function DETERMINISTIC?

You risk stale caches, incorrect query results, or data divergence between primary and replica because the engine assumes repeatability that does not exist.

Does DETERMINISTIC guarantee result caching?

No. It only permits the optimizer to cache results; whether it actually does so depends on the specific database engine and query plan.