Deterministic vs Non-Deterministic Functions in SQL
Introduction
SQL functions can be classified by whether they return the same result when they are evaluated again with the same inputs. A deterministic function produces the same output for the same explicit inputs under the same relevant database conditions. A non-deterministic function can produce a different output because it depends on time, random state, database state, session settings, external resources, or another changing input.
-- Normally deterministic
SELECT UPPER('sql') AS normalized_text;
-- Non-deterministic because time advances
SELECT CURRENT_TIMESTAMP AS captured_at;
The distinction matters far beyond terminology. Database engines use determinism rules when deciding whether an expression can appear in an index, persisted computed column, generated column, partition key, materialized result, or optimizer rewrite. Determinism also affects caching, replication, auditing, retry safety, and automated testing.
A useful starting model is simple: same explicit input plus the same relevant environment should produce the same output. The difficult part is identifying every relevant input, including the hidden ones.
What Is a Deterministic Function?
A deterministic function always returns the same result for the same arguments when data types, collation, configuration, and other declared dependencies remain unchanged. UPPER, LOWER, ABS, ROUND, FLOOR, CEILING, and POWER are typical examples when they operate on fixed values with fixed semantics.
SELECT ABS(-100) AS absolute_value,
POWER(2, 3) AS power_value,
LOWER('DATABASE') AS normalized_text;
-- Results: 100, 8, database
Repeated evaluation does not consume time, randomness, or changing table data. The output follows directly from the supplied values. Formally, if a function F receives input X and returns Y, then repeated evaluations of F(X) continue to return Y under the same relevant conditions.
Deterministic Functions with Table Columns
A function remains deterministic when its argument comes from a column, provided the row value and environment are unchanged. If an employee's salary is 65342.456, ROUND(salary, 2) returns the same rounded amount each time that row is evaluated.
SELECT employee_id,
UPPER(employee_name) AS display_name,
ROUND(salary, 2) AS rounded_salary
FROM employee;
The result can change after the underlying row changes, but that does not make ROUND non-deterministic. The explicit input is no longer the same. Determinism compares identical inputs under identical relevant conditions, not different versions of a row.
What Is a Non-Deterministic Function?
A non-deterministic function may return different values even when its visible arguments are unchanged. It usually reads a hidden input such as the current clock, a random generator state, a sequence, session identity, system configuration, or changing database content.
SELECT CURRENT_TIMESTAMP AS now_value;
SELECT CURRENT_TIMESTAMP AS later_value;
Even though both calls have no visible arguments, time is an implicit input. The values may differ because the clock advanced. Non-deterministic does not mean incorrect or unreliable; it means the output is not determined solely by the explicit arguments.
Deterministic vs Non-Deterministic Comparison
| Characteristic | Deterministic | Non-deterministic |
|---|---|---|
| Repeated identical input | Same result under fixed conditions | Result may change |
| Typical dependencies | Explicit arguments | Time, random state, sequence, session, or external state |
| Examples | ABS, UPPER, LOWER, POWER | Current time, random value, generated UUID |
| Index eligibility | Often eligible when product rules are met | Usually restricted |
| Testing style | Assert an exact expected value | Assert properties, ranges, or controlled dependencies |
The database's official classification takes priority over a general example. A function may be deterministic only for certain arguments, data types, styles, collations, or configuration settings.
Common Deterministic Function Categories
Text conversion, arithmetic, fixed numeric rounding, substring extraction, and explicit type conversion are often deterministic. Examples include UPPER, LOWER, ABS, FLOOR, CEILING, POWER, SUBSTRING, and CAST when their behavior is fully defined by their inputs.
SELECT SUBSTRING('DATABASE', 1, 4) AS prefix,
CAST('100' AS INTEGER) AS numeric_value,
CEILING(10.2) AS upper_integer;
However, a conversion that depends on session date format, locale, collation, or time zone may not be deterministic in the way required for an index. Prefer explicit, unambiguous input and documented conversion styles.
Current Date and Time Functions
Functions that return the current date or time depend on the clock and are therefore not deterministic across separate evaluations. CURRENT_DATE, CURRENT_TIMESTAMP, NOW, GETDATE, SYSDATE, and comparable functions belong to this category, although their exact statement-level behavior differs.
INSERT INTO audit_event (event_type, captured_at)
VALUES ('LOGIN_SUCCESS', CURRENT_TIMESTAMP);
This is an appropriate use of a changing time value. Problems arise when code assumes that the value is fixed forever, uses it in a restricted persisted expression, or expects separate statements to share exactly the same timestamp.
Statement-Stable Is Not the Same as Deterministic
Some databases evaluate a current-time function once per statement or once per transaction, so every row in that scope receives the same timestamp. That behavior is useful, but the function remains time-dependent across statements or transactions.
Three concepts should be separated: deterministic across identical inputs, stable within one statement or transaction, and repeatable in a particular test run. A function can be statement-stable without being deterministic, and a deterministic function can still produce different results when configuration changes.
Random Functions
RANDOM, RAND, DBMS_RANDOM, and similar functions generate values from changing internal state. Repeated calls can return different results even with no visible arguments.
SELECT RANDOM() AS sample_value;
Random functions are useful for sampling, test-data generation, simulations, and randomized ordering. They should not be treated as cryptographically secure unless the documentation explicitly guarantees that property. Ordering a large table by a random function can also be expensive because the engine may generate a value for every row and sort the entire result.
Seeded Random Functions
A seed can make a pseudo-random sequence reproducible, but that does not automatically make every SQL expression deterministic. The result can depend on generator state, evaluation order, parallelism, connection state, or how many earlier values were consumed.
Use a seeded generator for controlled tests only after verifying the product's exact semantics. If a stable per-row pseudo-random ordering is required, a deterministic hash of a stable key and fixed seed may be more predictable than a stateful random function.
UUID and Unique Identifier Generation
UUID or GUID functions produce new identifiers, often using random bits, time, host information, counters, or a combination. The output changes across calls, so generation is non-deterministic even when collision probability is extremely low.
INSERT INTO job_request (request_id, status)
VALUES (GENERATE_UUID(), 'QUEUED');
Random does not guarantee unique, and unique does not mean sequential. Choose identifier strategies based on collision guarantees, index locality, privacy, distributed generation, storage size, and exposure requirements. Enforce uniqueness with a constraint even when the generator is designed to avoid collisions.
Sequence and Identity Values
A sequence returns a value based on mutable database state. Calling NEXT VALUE changes that state, so the result is non-deterministic from the perspective of repeated evaluation. Identity assignment has similar stateful behavior.
Sequences are designed for key allocation, not for gapless business numbering. Rollbacks, caching, failover, and concurrent sessions can create gaps or reorder visible values. A requirement for gapless regulated numbers needs a separate transactional design.
Functions That Depend on Database State
A function can accept the same argument and still change because it reads a table. A user-defined function that looks up a tax rate, exchange rate, feature flag, or customer tier depends on current data.
SELECT calculate_tax(:amount, :region_code) AS tax_amount;
If calculate_tax queries a tax-rate table, the same amount and region can return a different result after the table is updated. The table is a hidden dependency. For historical calculations, pass an effective date or version explicitly and store the rate used.
Functions That Depend on Session State
Session user, current schema, locale, date format, time zone, collation, and configuration settings can influence output. A function may appear stable in one connection and produce a different result in another.
SELECT CURRENT_USER,
CURRENT_SCHEMA,
CURRENT_TIMESTAMP;
Session-dependent output is often legitimate for auditing or access control, but it should not be mislabeled as a pure calculation. Connection pools make this especially important because session settings can persist between borrowers unless they are reset explicitly.
Hidden Inputs
Arguments written inside parentheses are not always the complete input set. Time, random state, table contents, environment variables, server configuration, locale, collation, current user, transaction snapshot, sequence state, and external services can all affect a function.
A strong review question is: if the visible arguments stay unchanged, what else can alter this result? That question exposes hidden dependencies and makes determinism a design property rather than a label memorized from a function list.
Determinism and NULL
NULL does not make a function non-deterministic. If a deterministic function consistently returns NULL for a given NULL input, it remains deterministic. What matters is repeatability under the same rules.
SELECT ABS(CAST(NULL AS INTEGER)) AS result_value;
NULL behavior still needs explicit tests because functions differ in propagation, fallback, concatenation, and three-valued logic. A change from NULL to a default value is a semantic decision, not merely a technical convenience.
Determinism and Errors
A function can be deterministic even when a particular input consistently raises an error. Division by zero, invalid conversion, overflow, and out-of-range date calculations can fail repeatably. Determinism describes output dependency; it does not guarantee success, safety, or correctness.
Environment-Dependent Determinism
String case conversion can depend on collation. Text-to-date conversion can depend on language or date format. Numeric formatting can depend on locale. Time conversion can depend on time-zone data that is updated by the operating system or database.
-- Ambiguous and environment-sensitive
SELECT CAST('01/02/2026' AS DATE);
-- Prefer an unambiguous ISO-style value
SELECT CAST('2026-01-02' AS DATE);
For indexed or persisted calculations, use explicit collations, unambiguous formats, fixed time-zone rules, and documented conversion options where the database supports them.
Why Determinism Matters for Indexes
An index stores values that must remain valid until the indexed row changes. If an indexed expression depends on the clock or random state, its stored value could become stale without any row update. For this reason, databases generally require indexed expressions to use deterministic functions.
-- Product-specific expression-index concept
CREATE INDEX ix_customer_normalized_email
ON customer (LOWER(email));
Even deterministic expressions must meet product requirements related to precision, ownership, schema binding, data types, and session settings. Check the target database documentation rather than assuming that every deterministic expression is indexable.
Generated and Computed Columns
Generated or computed columns derive a value from other columns. A virtual value can sometimes be calculated at read time, while a persisted or stored value is materialized. Non-deterministic expressions are often prohibited because the persisted value would not automatically change when an invisible dependency changes.
-- Conceptual syntax; product details vary
ALTER TABLE employee
ADD normalized_name AS LOWER(TRIM(employee_name));
Use generated columns for stable row-derived values, not for current age, current date status, or random labels. Store the underlying birth date or status timestamps and calculate time-relative values at query time.
Materialized Views and Cached Results
Materialized results store output for later reuse. A function based on time, session identity, or external state raises the question of when the stored result should be refreshed. Deterministic expressions are easier to invalidate because changes follow explicit data dependencies.
If a business report intentionally captures a time-dependent value, model it as a snapshot with a documented capture timestamp and refresh policy. Do not present a cached value as current merely because its query originally used a current-time function.
Query Optimization and Constant Folding
An optimizer can sometimes evaluate a deterministic expression with constant arguments once and replace it with the result. This is called constant folding.
SELECT POWER(2, 3) AS calculated_value;
-- The optimizer may treat the expression as the constant 8.
The engine must be more cautious with non-deterministic expressions because replacing or moving an evaluation could change observable behavior. Function volatility can affect predicate reordering, common-expression elimination, parallel execution, and plan caching.
Evaluation Frequency Is Database-Specific
A non-deterministic expression might be evaluated once per statement, once per row, once per worker, once per function call, or according to another documented rule. SQL text alone may not prove the number of evaluations.
Do not depend on accidental evaluation frequency. If one shared value is required, capture it explicitly in a variable, parameter, common table expression with verified semantics, or application-generated value. If every row needs a separate value, verify that the product guarantees row-level generation.
User-Defined Function Determinism
A user-defined function can be deterministic when its output depends only on its arguments and stable semantics. A discount calculation based solely on amount and a passed rate is a good candidate.
SELECT calculate_discount(order_total, discount_rate)
FROM orders;
A function that reads configuration tables, calls the clock, generates random data, uses sequences, or accesses an external service is not purely determined by its visible arguments. Some databases allow or require a developer declaration of volatility or determinism. That declaration is a promise to the optimizer, not a way to make changing logic stable.
Incorrect Determinism Declarations
Marking a volatile user-defined function as deterministic can produce incorrect indexed values, stale cached results, unsafe optimizer rewrites, or replication inconsistencies. The database may trust the declaration because it cannot inspect every external dependency.
Review function bodies, called functions, table access, session settings, and external calls before declaring volatility. Reassess the declaration whenever implementation changes.
PostgreSQL Volatility Categories
PostgreSQL uses IMMUTABLE, STABLE, and VOLATILE categories. IMMUTABLE means the result depends only on arguments. STABLE permits results that remain fixed within a statement while potentially changing across statements. VOLATILE permits changes at any time and is the default.
This three-level model explains why statement stability is not full determinism. An incorrect IMMUTABLE declaration can let the planner fold a value too early or reuse it when its hidden dependencies changed.
SQL Server Considerations
SQL Server documents deterministic and nondeterministic built-in functions and applies requirements to indexed views and indexes on computed columns. Determinism can depend on syntax, conversion style, precision, schema binding, ownership, and required SET options.
GETDATE and NEWID are common nondeterministic examples. Deterministic alone may not be sufficient for indexing; an expression may also need to be precise and satisfy data-type restrictions.
MySQL Considerations
MySQL uses deterministic characteristics for stored functions and considers data access and replication behavior. Declaring a stored function DETERMINISTIC tells the server that identical inputs produce identical outputs, but MySQL does not automatically prove the claim.
Functions involving current time, randomness, UUID generation, or table reads need careful review. Binary logging and replication settings can make inaccurate declarations operationally important.
Oracle Considerations
Oracle supports a DETERMINISTIC declaration for functions used in contexts such as function-based indexes. The developer is responsible for ensuring repeatability. Reading changing tables, package state, system time, or session context violates that promise even if the function sometimes returns the same value.
Performance Considerations
Deterministic functions can enable constant folding, expression indexes, generated values, and result reuse, but determinism does not guarantee low cost. A deterministic user-defined function can still perform expensive computation for every row.
Non-deterministic functions can restrict optimization and add CPU, sorting, serialization, or synchronization work. Random ordering across millions of rows and row-by-row clock calls are common examples. Measure actual plans, rows, CPU, reads, elapsed time, and concurrency behavior.
Avoid Unnecessary Non-Deterministic Work
If every inserted row in one batch needs the same audit time, generate the timestamp once and pass it consistently. If each row truly needs its own capture time, document that requirement. Avoid invoking random, UUID, or current-time functions repeatedly merely because the syntax is convenient.
-- Application or procedure captures one batch timestamp
INSERT INTO audit_event (entity_id, captured_at)
SELECT entity_id, :batch_timestamp
FROM changed_entity;
Testing Deterministic Functions
Deterministic-function tests should assert exact output for representative and boundary inputs. Include NULL, empty text, Unicode, negative values, maximum precision, invalid input, collations, and supported database versions. Re-run identical cases to confirm stable behavior.
Tests should also vary the environment intentionally. Change session time zone, language, collation, date format, or configuration to determine whether a supposedly pure function has hidden dependencies.
Testing Non-Deterministic Functions
Do not normally assert one exact changing value. For a timestamp, verify the accepted time window, time zone, and ordering. For random output, verify range, type, distribution properties, and failure behavior. For generated identifiers, verify format and enforce uniqueness at the database boundary.
-- Conceptual timestamp assertion
:before_execution <= captured_at
AND captured_at <= :after_execution
A test that expects two random calls to differ can fail legitimately because random values may collide. Test documented properties rather than assumptions.
Reproducible Testing
The strongest design separates non-deterministic value generation from deterministic business logic. Pass a clock, generated ID, random seed, or effective date into the unit under test. Production supplies the real changing value; tests supply a fixed one.
This pattern makes failures reproducible and clarifies hidden dependencies. It also helps replay events, reconcile calculations, and perform historical audits.
Real-World Audit Example
An audit event should capture who performed an action and when. CURRENT_USER and CURRENT_TIMESTAMP are intentionally environment-dependent. Store their results at write time so the historical event does not change when queried later.
INSERT INTO audit_event
(entity_id, action_name, actor_name, captured_at)
VALUES (:entity_id, :action_name, CURRENT_USER, CURRENT_TIMESTAMP);
The stored audit row is a snapshot. Recalculating the current user or time during report generation would describe the report session, not the original action.
Real-World Business Calculation
A pricing function based only on quantity, unit price, discount rate, and explicit tax rate can be deterministic. A pricing function that silently reads today's exchange rate or a mutable promotion table is not determined solely by the order values.
For reliable invoicing, pass or store the applicable rate and policy version. This lets the system reproduce the original total after rates change and makes disputed calculations auditable.
Concurrency and Transaction Snapshots
Concurrent transactions make hidden database-state dependencies easier to see. A function that reads a lookup table may return one value in one transaction and another value in a later transaction after a committed update. Within one transaction, snapshot or locking rules may make the lookup appear stable, but that observation does not make the function immutable.
Isolation level determines which committed versions a statement can see. Read committed, repeatable read, snapshot, and serializable isolation can therefore affect results from data-reading functions. If a calculation must be historically reproducible, store the relevant input value or reference a versioned effective-dated row rather than relying on whatever data is visible later.
Retries and Idempotent Workflows
Applications and job systems often retry a statement after a timeout or transient failure. A retry that generates a new current timestamp, random value, sequence number, or UUID can produce a different request even when the business operation is logically the same. That difference can create duplicate records or make reconciliation difficult.
Generate an idempotency key at the workflow boundary and reuse it for every retry. Capture time-dependent business inputs once when the operation begins. The database should enforce an appropriate uniqueness rule so a repeated request returns or updates the original result instead of performing the operation twice.
Replication and Distributed Execution
Non-deterministic expressions are important in replicated and distributed systems. If each replica independently re-executes a statement containing time, random, host, or session functions, the resulting rows can diverge. Modern databases provide replication rules to manage this risk, but custom functions and inaccurate declarations can still cause inconsistencies.
Statement-based, row-based, and logical replication have different implications. A row-based stream normally transfers the materialized result, while statement replay may evaluate an expression again. Review the database's replication guarantees before using volatile functions in writes, generated values, or conflict-resolution logic.
Defaults, Constraints, and Policies
A non-deterministic default such as a current timestamp can be appropriate because it is evaluated when a row is inserted and the resulting value is stored. By contrast, a check constraint that changes truth merely because time passes is difficult to enforce: a previously valid row could become invalid without an update.
CREATE TABLE task (
task_id INTEGER PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
due_at TIMESTAMP,
CHECK (due_at IS NULL OR due_at > created_at)
);
The constraint compares stored row values and remains meaningful. Avoid a rule such as due_at > CURRENT_TIMESTAMP when the intent is permanent validity, because the clock will eventually make an unchanged row violate the rule.
Security and Authorization Functions
Row-level security and authorization logic may depend on the current user, role, tenant context, or session claims. These dependencies are intentionally session-sensitive. Treat them as security boundaries rather than ordinary deterministic calculations, and do not cache their results under keys that omit identity and tenant context.
A helper that answers whether the current user can view a row can change after a role update or session change. Test cross-tenant access, connection-pool reset behavior, role revocation, and administrative bypass paths. Security correctness is more important than forcing such logic into a deterministic classification.
Production Monitoring and Diagnostics
Monitor expensive user-defined functions, random sorts, volatile predicates, generated identifiers, and expression-index usage. Query statistics should capture execution count, rows, CPU, reads, elapsed time, spills, and plan changes. A function that is inexpensive for one row can dominate a query when called millions of times.
Log the effective time zone, policy version, rate identifier, seed, or other relevant hidden input when reproducibility matters. Diagnostics should explain not only what result was stored but also which version of the environment produced it. That evidence shortens incident analysis and supports audits.
Common Mistakes
- Assuming CURRENT_DATE is deterministic because it stays constant during one statement.
- Assuming a function with no arguments must be non-deterministic.
- Calling a function deterministic because two sample calls happened to match.
- Assuming random output is guaranteed unique.
- Assuming UUID generation makes a uniqueness constraint unnecessary.
- Ignoring collation, time zone, locale, and session settings.
- Declaring a table-reading function deterministic.
- Assuming every database classifies the same function identically.
- Depending on undocumented once-per-row or once-per-statement evaluation.
- Believing deterministic automatically means fast, safe, or precise.
Practical Decision Checklist
- List every explicit argument.
- Check whether current time or current date affects the result.
- Check random, sequence, UUID, and identity state.
- Identify table reads, external services, files, and configuration.
- Identify session user, schema, locale, collation, and time zone.
- Check whether identical errors and NULL behavior are defined.
- Read the target database's official classification.
- Review restrictions for indexes, generated columns, and materialized results.
- Verify evaluation frequency rather than assuming it.
- Design tests around exact outputs or documented properties as appropriate.
Interview Questions and Answers
What is a deterministic function? It returns the same result for the same inputs under the same relevant database conditions.
What is a non-deterministic function? Its result can change because it depends on time, random state, sequence state, session state, database data, or another hidden input.
Give deterministic examples. ABS, UPPER, LOWER, POWER, and many fixed numeric calculations are typical examples, subject to product semantics.
Give non-deterministic examples. Current-time, random-value, UUID-generation, sequence, and current-session functions are common categories.
Is CURRENT_TIMESTAMP deterministic? No across separate times, although a database may keep it stable within a statement or transaction.
Is ABS deterministic? Yes for a fixed supported numeric input and fixed semantics.
Does non-deterministic mean random? No. Current time, current user, sequences, and table-dependent lookups are non-deterministic without being random.
Can a user-defined function be deterministic? Yes, if its result depends only on its explicit inputs and stable declared semantics.
Can a function that reads a table be deterministic? Usually not in the strict sense because the table can change independently of the function arguments.
Why are non-deterministic functions restricted in indexes? Stored index values could become invalid when a hidden dependency changes without an indexed-row update.
Why does determinism matter to an optimizer? It affects whether expressions can be folded, reordered, cached, indexed, persisted, or reused safely.
Key Takeaway
A deterministic function produces the same result from the same inputs under fixed relevant conditions. A non-deterministic function depends on a changing or hidden input such as time, random state, sequence state, session context, mutable data, or external configuration.
DETERMINISTIC RESULT = EXPLICIT INPUTS
+ FIXED SEMANTICS
+ NO CHANGING HIDDEN STATE
NON-DETERMINISTIC RESULT = EXPLICIT INPUTS
+ TIME, RANDOMNESS, STATE,
SESSION, DATA, OR ENVIRONMENT
Use the database's documented classification, make hidden dependencies explicit, avoid unsafe determinism declarations, and test deterministic and non-deterministic behavior with different strategies.