Deterministic Functions — SQL Functions | OneNoughtOne

0/278

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

4 / 5

Deterministic Functions

The Promise of Predictability

In mathematics, a function has a simple, powerful property: given the same inputs, it always produces the same output. The function f(x) = x² will forever return 4 when given 2, 9 when given 3, and 16 when given 4. This property— determinism —is so fundamental to mathematics that it's part of the definition of what a function is. In database systems, deterministic functions carry this same guarantee. A deterministic function produces identical results every time it's called with identical arguments, regardless of when it's called, who calls it, or what state the database is in. This seemingly simple property unlocks profound optimizations and capabilities that non-deterministic functions cannot access. Understanding determinism is essential because it determines: Whether a function can be used in indexed views or persisted computed columns Whether the query optimizer can cache and reuse results Whether parallel execution can safely use the function Whether the function produces consistent results in distributed systems

What You Will Learn

By the end of this page, you will understand what makes a function deterministic or non-deterministic, learn how to verify and declare determinism across database platforms, explore the optimizer advantages deterministic functions enable, and develop strategies for designing functions with predictable behavior.

Defining Determinism

A function is deterministic if and only if it satisfies this invariant: For any combination of input parameter values, the function always returns the exact same result. This definition has subtle but important implications. "Always" means across time, across database sessions, across server restarts, and across different execution contexts. If any factor besides the input parameters can influence the output, the function is non-deterministic . Let's crystallize this with concrete examples:

Deterministic Functions

•Mathematical: ABS(x), POWER(x, y), SQRT(x)
•String: UPPER(s), SUBSTRING(s, i, n), LEN(s)
•Date math: DATEDIFF(d1, d2), DATEADD(part, n, d)
•Type conversion: CAST(x AS INT), CONVERT(type, x)
•Custom pure logic: CalculateCompoundInterest(p, r, t)

Non-Deterministic Functions

•Time-based: GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME()
•Random: RAND(), NEWID(), NEWSEQUENTIALID()
•Session-dependent: @@SPID, USER_NAME(), SYSTEM_USER
•Environment: HOST_NAME(), APP_NAME()
•Data-reading: Functions that SELECT from tables

The Hidden Non-Determinism Trap

A function that calls ANY non-deterministic function becomes non-deterministic itself, even if that call is buried deep in nested function calls. A function that uses GETDATE() to timestamp a log entry—even in an IF branch that's rarely executed—is non-deterministic for its entire existence.

Why Determinism Matters

Determinism isn't just a theoretical property—it directly controls what capabilities are available to your functions. Database systems leverage determinism guarantees to enable powerful features that would be unsafe or incorrect with non-deterministic functions.

Capabilities Enabled by Deterministic Functions
Capability	Why Determinism Required	Impact If Non-Deterministic
Indexed Views	Index data must match underlying data; if function result changes, index becomes invalid	Cannot create indexed view referencing the function
Persisted Computed Columns	Stored value must equal computed value on every read; non-determinism breaks this	Computed column must be virtual (computed on access)
Index on Computed Column	Index key must be stable; changing values corrupt B-tree structure	Cannot create index on the computed column
Result Caching	Optimizer can reuse result only if guaranteed unchanged for same inputs	Function re-evaluated on every reference, even in same query
Parallel Execution	Parallel workers must produce consistent results independently	May be restricted to serial execution
CHECK Constraints	Constraint evaluation must be consistent; non-determinism causes random failures	Some systems reject non-deterministic functions in constraints

determinism_impact.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Demonstration: Determinism enables advanced features
 
-- DETERMINISTIC function
CREATE FUNCTION dbo.NormalizeSSN(@SSN VARCHAR(20))
RETURNS CHAR(9)
WITH SCHEMABINDING  -- Schema binding helps establish determinism
AS
BEGIN
    RETURN REPLACE(REPLACE(@SSN, '-', ''), ' ', '');
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.NormalizeSSN'), 'IsDeterministic') AS IsDeterministic;
-- Returns 1
 
-- NOW we can create a PERSISTED computed column
ALTER TABLE Employees
ADD SSN_Normalized AS dbo.NormalizeSSN(SSN) PERSISTED;
 
-- AND we can INDEX it for fast lookups
CREATE INDEX IX_Employees_SSN_Normalized ON Employees(SSN_Normalized);
 
-- NON-DETERMINISTIC function
CREATE FUNCTION dbo.GetAgeDynamic(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.GetAgeDynamic'), 'IsDeterministic') AS IsDeterministic;
-- Returns 0
 
-- This FAILS: cannot persist non-deterministic computed column
-- ALTER TABLE Employees ADD Age AS dbo.GetAgeDynamic(BirthDate) PERSISTED;
-- Error: Computed column 'Age' cannot be persisted because the column is non-deterministic.
 
-- Virtual computed column is allowed (computed on each access)
ALTER TABLE Employees ADD Age AS dbo.GetAgeDynamic(BirthDate);

Verifying Function Determinism

Each database platform provides mechanisms to check whether a function is deterministic. Understanding how to query this information helps you validate function properties and troubleshoot when expected capabilities aren't available.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- SQL Server: Check determinism using OBJECTPROPERTY
 
-- Single function check
SELECT 
    OBJECT_NAME(object_id) AS FunctionName,
    OBJECTPROPERTY(object_id, 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(object_id, 'IsSchemaBound') AS IsSchemaBound,
    OBJECTPROPERTY(object_id, 'IsInlineFunction') AS IsInlined
FROM sys.objects
WHERE object_id = OBJECT_ID('dbo.MyFunction');
 
-- All user-defined functions with determinism status
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS FunctionName,
    type_desc AS FunctionType,
    OBJECTPROPERTY(object_id, 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(object_id, 'IsSchemaBound') AS IsSchemaBound
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')  -- Scalar, Inline TVF, Multi-statement TVF
ORDER BY SchemaName, FunctionName;
 
-- Check if function can be used in computed column
SELECT 
    OBJECTPROPERTY(OBJECT_ID('dbo.MyFunction'), 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(OBJECT_ID('dbo.MyFunction'), 'IsPrecise') AS IsPrecise;
-- Both must be 1 for persisted computed columns
 
-- Why is my function non-deterministic? Check dependencies
SELECT 
    OBJECT_NAME(referencing_id) AS ReferencingFunction,
    referenced_entity_name AS ReferencedObject,
    CASE 
        WHEN OBJECTPROPERTY(OBJECT_ID(referenced_entity_name), 'IsDeterministic') = 0 
        THEN 'Non-Deterministic Dependency'
        ELSE 'OK'
    END AS Status
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.MyFunction');

Designing for Determinism

Creating deterministic functions requires disciplined design that avoids all sources of non-determinism. The key principle is parameterize everything that varies —don't embed anything into the function that could change between calls.

determinism_design_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- Pattern 1: Replace time functions with parameters
 
-- NON-DETERMINISTIC: Uses GETDATE() internally
CREATE FUNCTION dbo.GetAge_Bad(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
 
-- DETERMINISTIC: Receives reference date as parameter
CREATE FUNCTION dbo.GetAge_Good(@BirthDate DATE, @AsOfDate DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, @AsOfDate)
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, @AsOfDate), @BirthDate) > @AsOfDate
                  THEN 1 ELSE 0 END;
END;
 
-- Pattern 2: Avoid reading from tables
 
-- NON-DETERMINISTIC: Reads from configuration table
CREATE FUNCTION dbo.GetTaxRate_Bad(@StateCode CHAR(2))
RETURNS DECIMAL(6,4)
AS
BEGIN
    DECLARE @Rate DECIMAL(6,4);
    SELECT @Rate = TaxRate FROM StateTaxRates WHERE StateCode = @StateCode;
    RETURN @Rate;
END;
 
-- DETERMINISTIC: Encode rates in function or receive as parameter
CREATE FUNCTION dbo.GetTaxRate_Good(@StateCode CHAR(2))
RETURNS DECIMAL(6,4)
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @StateCode
        WHEN 'CA' THEN 0.0725
        WHEN 'NY' THEN 0.0800
        WHEN 'TX' THEN 0.0625
        WHEN 'WA' THEN 0.0650
        ELSE 0.0500
    END;
END;
 
-- Or for dynamic rates: pass rate as parameter
CREATE FUNCTION dbo.CalculateTax(@Amount DECIMAL(18,2), @Rate DECIMAL(6,4))
RETURNS DECIMAL(18,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN ROUND(@Amount * @Rate, 2);
END;
 
-- Pattern 3: Pure mathematical/logical computation
CREATE FUNCTION dbo.Fibonacci(@N INT)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
    IF @N < 0 RETURN NULL;
    IF @N <= 1 RETURN @N;
    
    DECLARE @a BIGINT = 0, @b BIGINT = 1, @c BIGINT;
    DECLARE @i INT = 2;
    
    WHILE @i <= @N
    BEGIN
        SET @c = @a + @b;
        SET @a = @b;
        SET @b = @c;
        SET @i = @i + 1;
    END;
    
    RETURN @b;
END;

Determinism Design Checklist

•No time functions — Replace GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME() with parameters
•No random functions — Avoid RAND(), NEWID(), NEWSEQUENTIALID()
•No session functions — Don't use @@SPID, USER_NAME(), SESSION_USER
•No environment functions — Avoid HOST_NAME(), APP_NAME(), @@SERVERNAME
•No data access — Functions reading tables are non-deterministic (data can change)
•Only call deterministic functions — Verify all called functions are deterministic
•Use SCHEMABINDING — Required in SQL Server to enable determinism verification

SCHEMABINDING Deep Dive

In SQL Server, SCHEMABINDING is intimately connected with determinism because it prevents changes to underlying objects that could affect function behavior. A function without SCHEMABINDING cannot be reliably verified as deterministic because its dependencies might change. When you create a function WITH SCHEMABINDING: The function is bound to all referenced objects (tables, views, other functions) Those objects cannot be dropped or altered in ways that affect the function SQL Server can verify the function's determinism based on its complete dependency tree The function becomes eligible for use in indexed views and persisted computed columns

schemabinding_mechanics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Understanding SCHEMABINDING requirements and effects
 
-- Schema-bound function: must use two-part names (schema.object)
CREATE FUNCTION dbo.FormatCurrency(@Amount DECIMAL(18,2))
RETURNS VARCHAR(50)
WITH SCHEMABINDING  -- Enables determinism verification
AS
BEGIN
    -- Cannot reference dbo.SomeTable here - would make it non-deterministic
    -- Cannot use SELECT from any table
    
    IF @Amount IS NULL RETURN NULL;
    
    RETURN '$' + FORMAT(@Amount, 'N2');
END;
GO
 
-- This IS deterministic
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.FormatCurrency'), 'IsDeterministic');
-- Returns 1
 
-- What SCHEMABINDING prevents:
-- 1. Cannot DROP referenced objects
-- DROP TABLE dbo.SomeReferencedTable;  -- Error if function references it
 
-- 2. Cannot ALTER columns referenced by the function
-- ALTER TABLE dbo.Table ALTER COLUMN ReferencedColumn INT;  -- Error
 
-- 3. Forces explicit schema qualification
-- CREATE FUNCTION dbo.BadFunction(@X INT) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN X * 2; END;
-- Error: Must use two-part name for 'X' (thinks X is a column reference)
 
-- Example: Schema-bound function referencing another function
CREATE FUNCTION dbo.DoubleValue(@X INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN @X * 2;
END;
GO
 
CREATE FUNCTION dbo.QuadrupleValue(@X INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    -- References another schema-bound function
    RETURN dbo.DoubleValue(dbo.DoubleValue(@X));
END;
GO
 
-- Now you cannot drop DoubleValue:
-- DROP FUNCTION dbo.DoubleValue;  
-- Error: Cannot drop function because it is being referenced by 'QuadrupleValue'
 
-- Check schema dependencies
SELECT 
    referencing_id,
    OBJECT_NAME(referencing_id) AS referencing_name,
    referenced_id,
    OBJECT_NAME(referenced_id) AS referenced_name,
    is_schema_bound_reference
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('dbo.DoubleValue');

SCHEMABINDING Trade-offs

SCHEMABINDING adds deployment complexity: you must drop/recreate functions in reverse dependency order when making changes. However, this rigidity ensures determinism verification and protects against accidental breaks. For production systems with indexed views or persisted computed columns, SCHEMABINDING's guarantees are worth the deployment overhead.

PostgreSQL Volatility Categories

PostgreSQL takes a more nuanced approach with three volatility categories: IMMUTABLE, STABLE, and VOLATILE . These categories provide finer-grained control over optimization and capability eligibility than SQL Server's binary deterministic/non-deterministic classification.

PostgreSQL Volatility Categories
Category	Guarantee	Optimizer Benefits	Use Cases
IMMUTABLE	Same result forever for same inputs	Can evaluate at plan time, use in indexes, maximum caching	Pure math, string manipulation, encoding/decoding
STABLE	Same result within single query/transaction	Can cache results within a query, used in index scans	Functions reading config, current_user, transaction-constant data
VOLATILE	Result may change at any time	Re-evaluated on every row, no caching	RANDOM(), NOW(), functions with side effects

postgresql_volatility.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- IMMUTABLE: Truly deterministic, can be used in indexes
 
CREATE OR REPLACE FUNCTION slugify(text_input TEXT)
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE  -- Same input = same output, forever
PARALLEL SAFE
AS $$
    SELECT LOWER(
        REGEXP_REPLACE(
            REGEXP_REPLACE(text_input, '[^a-zA-Z0-9]+', '-', 'g'),
            '^-+|-+$', '', 'g'
        )
    );
$$;
 
-- Can create expression-based index!
CREATE INDEX idx_products_slug ON products (slugify(product_name));
 
-- STABLE: Same result within a transaction
 
CREATE OR REPLACE FUNCTION get_config_value(key TEXT)
RETURNS TEXT
LANGUAGE SQL
STABLE  -- Reads config, which doesn't change mid-transaction
AS $$
    SELECT value FROM app_config WHERE config_key = key;
$$;
 
-- Cannot use in index (data might change)
-- But optimizer can cache result within a query:
SELECT * FROM orders 
WHERE store_id = get_config_value('default_store')::INT;
-- Function called once, not for every row
 
-- VOLATILE: May change at any time
 
CREATE OR REPLACE FUNCTION generate_code()
RETURNS TEXT
LANGUAGE SQL
VOLATILE  -- Uses random, definitely not deterministic
AS $$
    SELECT 'CODE-' || LPAD(FLOOR(RANDOM() * 1000000)::TEXT, 6, '0');
$$;
 
-- Common mistake: Declaring RANDOM() function as STABLE
-- DON'T DO THIS - results in incorrect optimization!
CREATE OR REPLACE FUNCTION bad_random_function()
RETURNS DOUBLE PRECISION
LANGUAGE SQL
STABLE  -- WRONG! RANDOM() is VOLATILE
AS $$
    SELECT RANDOM();
$$;
 
-- The optimizer might evaluate this ONCE and reuse:
SELECT *, bad_random_function() FROM generate_series(1, 10);
-- All rows might get the SAME random value!
 
-- Built-in function volatility examples:
-- IMMUTABLE: abs(), length(), upper(), lower()
-- STABLE: current_user, current_database(), current_setting()
-- VOLATILE: random(), nextval(), timeofday(), clock_timestamp()

The Danger of Incorrect Volatility

PostgreSQL TRUSTS your volatility declaration. If you declare a VOLATILE function as IMMUTABLE, the optimizer may cache incorrect results, inline the function, or use it in constant folding—producing wrong query results. Always verify your volatility declaration matches actual function behavior.

Optimizer Advantages of Determinism

When the query optimizer knows a function is deterministic, it can apply several powerful optimizations that dramatically improve query performance. Understanding these optimizations helps you appreciate why designing for determinism matters.

Optimizer Optimizations for Deterministic Functions

•Constant Folding — If function inputs are literals or constants, the optimizer can evaluate the function once at compile time and substitute the result. WHERE column = CalculateTax(100.00, 0.08) becomes WHERE column = 8.00.
•Common Subexpression Elimination — When the same deterministic function call appears multiple times, the optimizer can evaluate it once and reuse the result. Essential for queries that reference the same function in SELECT and WHERE.
•Result Caching — Within a query, deterministic function results for identical inputs can be cached, avoiding repeated computation for the same parameter values.
•Predicate Pushdown — For inline functions, the optimizer can push predicates through function calls because it knows the function won't alter the predicate's meaning.
•Parallel Execution — Deterministic functions can safely execute on multiple parallel threads because they produce consistent results regardless of execution order.
•Join Optimization — For functions in join conditions, determinism ensures the join produces the same result regardless of which table is processed first.

optimizer_optimizations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Constant Folding demonstration
 
-- Deterministic function
CREATE FUNCTION dbo.CircleArea(@Radius FLOAT)
RETURNS FLOAT
WITH SCHEMABINDING
AS
BEGIN
    RETURN PI() * @Radius * @Radius;
END;
 
-- This query:
SELECT ProductId, Price
FROM Products
WHERE Price > dbo.CircleArea(10);
 
-- Optimizer MAY simplify to:
SELECT ProductId, Price
FROM Products
WHERE Price > 314.159265358979;  -- Computed at compile time
 
-- Common Subexpression Elimination
 
-- Query with repeated function call:
SELECT 
    ProductId,
    Price,
    dbo.CalculateTax(Price, 0.08) AS Tax,
    Price + dbo.CalculateTax(Price, 0.08) AS TotalWithTax
FROM Products
WHERE dbo.CalculateTax(Price, 0.08) > 10;
 
-- For each row, the function could be called 3 times
-- With deterministic + optimization: called ONCE per row
 
-- Parallel execution visibility
-- Check if function blocks parallelism
SELECT 
    p.query_plan,
    p.query_plan.query('//RelOp[@PhysicalOp="Parallelism"]') AS ParallelOps
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p
WHERE qs.sql_handle = /* your query's handle */;
 
-- Non-deterministic functions often show:
-- "NonParallelPlanReason = UDFInsideFilter"

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

4 / 5

Deterministic Functions

The Promise of Predictability

In mathematics, a function has a simple, powerful property: given the same inputs, it always produces the same output. The function f(x) = x² will forever return 4 when given 2, 9 when given 3, and 16 when given 4. This property— determinism —is so fundamental to mathematics that it's part of the definition of what a function is. In database systems, deterministic functions carry this same guarantee. A deterministic function produces identical results every time it's called with identical arguments, regardless of when it's called, who calls it, or what state the database is in. This seemingly simple property unlocks profound optimizations and capabilities that non-deterministic functions cannot access. Understanding determinism is essential because it determines: Whether a function can be used in indexed views or persisted computed columns Whether the query optimizer can cache and reuse results Whether parallel execution can safely use the function Whether the function produces consistent results in distributed systems

What You Will Learn

By the end of this page, you will understand what makes a function deterministic or non-deterministic, learn how to verify and declare determinism across database platforms, explore the optimizer advantages deterministic functions enable, and develop strategies for designing functions with predictable behavior.

Defining Determinism

A function is deterministic if and only if it satisfies this invariant: For any combination of input parameter values, the function always returns the exact same result. This definition has subtle but important implications. "Always" means across time, across database sessions, across server restarts, and across different execution contexts. If any factor besides the input parameters can influence the output, the function is non-deterministic . Let's crystallize this with concrete examples:

Deterministic Functions

•Mathematical: ABS(x), POWER(x, y), SQRT(x)
•String: UPPER(s), SUBSTRING(s, i, n), LEN(s)
•Date math: DATEDIFF(d1, d2), DATEADD(part, n, d)
•Type conversion: CAST(x AS INT), CONVERT(type, x)
•Custom pure logic: CalculateCompoundInterest(p, r, t)

Non-Deterministic Functions

•Time-based: GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME()
•Random: RAND(), NEWID(), NEWSEQUENTIALID()
•Session-dependent: @@SPID, USER_NAME(), SYSTEM_USER
•Environment: HOST_NAME(), APP_NAME()
•Data-reading: Functions that SELECT from tables

The Hidden Non-Determinism Trap

A function that calls ANY non-deterministic function becomes non-deterministic itself, even if that call is buried deep in nested function calls. A function that uses GETDATE() to timestamp a log entry—even in an IF branch that's rarely executed—is non-deterministic for its entire existence.

Why Determinism Matters

Determinism isn't just a theoretical property—it directly controls what capabilities are available to your functions. Database systems leverage determinism guarantees to enable powerful features that would be unsafe or incorrect with non-deterministic functions.

Capabilities Enabled by Deterministic Functions
Capability	Why Determinism Required	Impact If Non-Deterministic
Indexed Views	Index data must match underlying data; if function result changes, index becomes invalid	Cannot create indexed view referencing the function
Persisted Computed Columns	Stored value must equal computed value on every read; non-determinism breaks this	Computed column must be virtual (computed on access)
Index on Computed Column	Index key must be stable; changing values corrupt B-tree structure	Cannot create index on the computed column
Result Caching	Optimizer can reuse result only if guaranteed unchanged for same inputs	Function re-evaluated on every reference, even in same query
Parallel Execution	Parallel workers must produce consistent results independently	May be restricted to serial execution
CHECK Constraints	Constraint evaluation must be consistent; non-determinism causes random failures	Some systems reject non-deterministic functions in constraints

determinism_impact.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Demonstration: Determinism enables advanced features
 
-- DETERMINISTIC function
CREATE FUNCTION dbo.NormalizeSSN(@SSN VARCHAR(20))
RETURNS CHAR(9)
WITH SCHEMABINDING  -- Schema binding helps establish determinism
AS
BEGIN
    RETURN REPLACE(REPLACE(@SSN, '-', ''), ' ', '');
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.NormalizeSSN'), 'IsDeterministic') AS IsDeterministic;
-- Returns 1
 
-- NOW we can create a PERSISTED computed column
ALTER TABLE Employees
ADD SSN_Normalized AS dbo.NormalizeSSN(SSN) PERSISTED;
 
-- AND we can INDEX it for fast lookups
CREATE INDEX IX_Employees_SSN_Normalized ON Employees(SSN_Normalized);
 
-- NON-DETERMINISTIC function
CREATE FUNCTION dbo.GetAgeDynamic(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.GetAgeDynamic'), 'IsDeterministic') AS IsDeterministic;
-- Returns 0
 
-- This FAILS: cannot persist non-deterministic computed column
-- ALTER TABLE Employees ADD Age AS dbo.GetAgeDynamic(BirthDate) PERSISTED;
-- Error: Computed column 'Age' cannot be persisted because the column is non-deterministic.
 
-- Virtual computed column is allowed (computed on each access)
ALTER TABLE Employees ADD Age AS dbo.GetAgeDynamic(BirthDate);

Verifying Function Determinism

Each database platform provides mechanisms to check whether a function is deterministic. Understanding how to query this information helps you validate function properties and troubleshoot when expected capabilities aren't available.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- SQL Server: Check determinism using OBJECTPROPERTY
 
-- Single function check
SELECT 
    OBJECT_NAME(object_id) AS FunctionName,
    OBJECTPROPERTY(object_id, 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(object_id, 'IsSchemaBound') AS IsSchemaBound,
    OBJECTPROPERTY(object_id, 'IsInlineFunction') AS IsInlined
FROM sys.objects
WHERE object_id = OBJECT_ID('dbo.MyFunction');
 
-- All user-defined functions with determinism status
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS FunctionName,
    type_desc AS FunctionType,
    OBJECTPROPERTY(object_id, 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(object_id, 'IsSchemaBound') AS IsSchemaBound
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')  -- Scalar, Inline TVF, Multi-statement TVF
ORDER BY SchemaName, FunctionName;
 
-- Check if function can be used in computed column
SELECT 
    OBJECTPROPERTY(OBJECT_ID('dbo.MyFunction'), 'IsDeterministic') AS IsDeterministic,
    OBJECTPROPERTY(OBJECT_ID('dbo.MyFunction'), 'IsPrecise') AS IsPrecise;
-- Both must be 1 for persisted computed columns
 
-- Why is my function non-deterministic? Check dependencies
SELECT 
    OBJECT_NAME(referencing_id) AS ReferencingFunction,
    referenced_entity_name AS ReferencedObject,
    CASE 
        WHEN OBJECTPROPERTY(OBJECT_ID(referenced_entity_name), 'IsDeterministic') = 0 
        THEN 'Non-Deterministic Dependency'
        ELSE 'OK'
    END AS Status
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.MyFunction');

Designing for Determinism

Creating deterministic functions requires disciplined design that avoids all sources of non-determinism. The key principle is parameterize everything that varies —don't embed anything into the function that could change between calls.

determinism_design_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- Pattern 1: Replace time functions with parameters
 
-- NON-DETERMINISTIC: Uses GETDATE() internally
CREATE FUNCTION dbo.GetAge_Bad(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
 
-- DETERMINISTIC: Receives reference date as parameter
CREATE FUNCTION dbo.GetAge_Good(@BirthDate DATE, @AsOfDate DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, @AsOfDate)
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, @AsOfDate), @BirthDate) > @AsOfDate
                  THEN 1 ELSE 0 END;
END;
 
-- Pattern 2: Avoid reading from tables
 
-- NON-DETERMINISTIC: Reads from configuration table
CREATE FUNCTION dbo.GetTaxRate_Bad(@StateCode CHAR(2))
RETURNS DECIMAL(6,4)
AS
BEGIN
    DECLARE @Rate DECIMAL(6,4);
    SELECT @Rate = TaxRate FROM StateTaxRates WHERE StateCode = @StateCode;
    RETURN @Rate;
END;
 
-- DETERMINISTIC: Encode rates in function or receive as parameter
CREATE FUNCTION dbo.GetTaxRate_Good(@StateCode CHAR(2))
RETURNS DECIMAL(6,4)
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @StateCode
        WHEN 'CA' THEN 0.0725
        WHEN 'NY' THEN 0.0800
        WHEN 'TX' THEN 0.0625
        WHEN 'WA' THEN 0.0650
        ELSE 0.0500
    END;
END;
 
-- Or for dynamic rates: pass rate as parameter
CREATE FUNCTION dbo.CalculateTax(@Amount DECIMAL(18,2), @Rate DECIMAL(6,4))
RETURNS DECIMAL(18,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN ROUND(@Amount * @Rate, 2);
END;
 
-- Pattern 3: Pure mathematical/logical computation
CREATE FUNCTION dbo.Fibonacci(@N INT)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
    IF @N < 0 RETURN NULL;
    IF @N <= 1 RETURN @N;
    
    DECLARE @a BIGINT = 0, @b BIGINT = 1, @c BIGINT;
    DECLARE @i INT = 2;
    
    WHILE @i <= @N
    BEGIN
        SET @c = @a + @b;
        SET @a = @b;
        SET @b = @c;
        SET @i = @i + 1;
    END;
    
    RETURN @b;
END;

Determinism Design Checklist

•No time functions — Replace GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME() with parameters
•No random functions — Avoid RAND(), NEWID(), NEWSEQUENTIALID()
•No session functions — Don't use @@SPID, USER_NAME(), SESSION_USER
•No environment functions — Avoid HOST_NAME(), APP_NAME(), @@SERVERNAME
•No data access — Functions reading tables are non-deterministic (data can change)
•Only call deterministic functions — Verify all called functions are deterministic
•Use SCHEMABINDING — Required in SQL Server to enable determinism verification

SCHEMABINDING Deep Dive

In SQL Server, SCHEMABINDING is intimately connected with determinism because it prevents changes to underlying objects that could affect function behavior. A function without SCHEMABINDING cannot be reliably verified as deterministic because its dependencies might change. When you create a function WITH SCHEMABINDING: The function is bound to all referenced objects (tables, views, other functions) Those objects cannot be dropped or altered in ways that affect the function SQL Server can verify the function's determinism based on its complete dependency tree The function becomes eligible for use in indexed views and persisted computed columns

schemabinding_mechanics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Understanding SCHEMABINDING requirements and effects
 
-- Schema-bound function: must use two-part names (schema.object)
CREATE FUNCTION dbo.FormatCurrency(@Amount DECIMAL(18,2))
RETURNS VARCHAR(50)
WITH SCHEMABINDING  -- Enables determinism verification
AS
BEGIN
    -- Cannot reference dbo.SomeTable here - would make it non-deterministic
    -- Cannot use SELECT from any table
    
    IF @Amount IS NULL RETURN NULL;
    
    RETURN '$' + FORMAT(@Amount, 'N2');
END;
GO
 
-- This IS deterministic
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.FormatCurrency'), 'IsDeterministic');
-- Returns 1
 
-- What SCHEMABINDING prevents:
-- 1. Cannot DROP referenced objects
-- DROP TABLE dbo.SomeReferencedTable;  -- Error if function references it
 
-- 2. Cannot ALTER columns referenced by the function
-- ALTER TABLE dbo.Table ALTER COLUMN ReferencedColumn INT;  -- Error
 
-- 3. Forces explicit schema qualification
-- CREATE FUNCTION dbo.BadFunction(@X INT) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN X * 2; END;
-- Error: Must use two-part name for 'X' (thinks X is a column reference)
 
-- Example: Schema-bound function referencing another function
CREATE FUNCTION dbo.DoubleValue(@X INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN @X * 2;
END;
GO
 
CREATE FUNCTION dbo.QuadrupleValue(@X INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    -- References another schema-bound function
    RETURN dbo.DoubleValue(dbo.DoubleValue(@X));
END;
GO
 
-- Now you cannot drop DoubleValue:
-- DROP FUNCTION dbo.DoubleValue;  
-- Error: Cannot drop function because it is being referenced by 'QuadrupleValue'
 
-- Check schema dependencies
SELECT 
    referencing_id,
    OBJECT_NAME(referencing_id) AS referencing_name,
    referenced_id,
    OBJECT_NAME(referenced_id) AS referenced_name,
    is_schema_bound_reference
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('dbo.DoubleValue');

SCHEMABINDING Trade-offs

SCHEMABINDING adds deployment complexity: you must drop/recreate functions in reverse dependency order when making changes. However, this rigidity ensures determinism verification and protects against accidental breaks. For production systems with indexed views or persisted computed columns, SCHEMABINDING's guarantees are worth the deployment overhead.

PostgreSQL Volatility Categories

PostgreSQL takes a more nuanced approach with three volatility categories: IMMUTABLE, STABLE, and VOLATILE . These categories provide finer-grained control over optimization and capability eligibility than SQL Server's binary deterministic/non-deterministic classification.

PostgreSQL Volatility Categories
Category	Guarantee	Optimizer Benefits	Use Cases
IMMUTABLE	Same result forever for same inputs	Can evaluate at plan time, use in indexes, maximum caching	Pure math, string manipulation, encoding/decoding
STABLE	Same result within single query/transaction	Can cache results within a query, used in index scans	Functions reading config, current_user, transaction-constant data
VOLATILE	Result may change at any time	Re-evaluated on every row, no caching	RANDOM(), NOW(), functions with side effects

postgresql_volatility.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- IMMUTABLE: Truly deterministic, can be used in indexes
 
CREATE OR REPLACE FUNCTION slugify(text_input TEXT)
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE  -- Same input = same output, forever
PARALLEL SAFE
AS $$
    SELECT LOWER(
        REGEXP_REPLACE(
            REGEXP_REPLACE(text_input, '[^a-zA-Z0-9]+', '-', 'g'),
            '^-+|-+$', '', 'g'
        )
    );
$$;
 
-- Can create expression-based index!
CREATE INDEX idx_products_slug ON products (slugify(product_name));
 
-- STABLE: Same result within a transaction
 
CREATE OR REPLACE FUNCTION get_config_value(key TEXT)
RETURNS TEXT
LANGUAGE SQL
STABLE  -- Reads config, which doesn't change mid-transaction
AS $$
    SELECT value FROM app_config WHERE config_key = key;
$$;
 
-- Cannot use in index (data might change)
-- But optimizer can cache result within a query:
SELECT * FROM orders 
WHERE store_id = get_config_value('default_store')::INT;
-- Function called once, not for every row
 
-- VOLATILE: May change at any time
 
CREATE OR REPLACE FUNCTION generate_code()
RETURNS TEXT
LANGUAGE SQL
VOLATILE  -- Uses random, definitely not deterministic
AS $$
    SELECT 'CODE-' || LPAD(FLOOR(RANDOM() * 1000000)::TEXT, 6, '0');
$$;
 
-- Common mistake: Declaring RANDOM() function as STABLE
-- DON'T DO THIS - results in incorrect optimization!
CREATE OR REPLACE FUNCTION bad_random_function()
RETURNS DOUBLE PRECISION
LANGUAGE SQL
STABLE  -- WRONG! RANDOM() is VOLATILE
AS $$
    SELECT RANDOM();
$$;
 
-- The optimizer might evaluate this ONCE and reuse:
SELECT *, bad_random_function() FROM generate_series(1, 10);
-- All rows might get the SAME random value!
 
-- Built-in function volatility examples:
-- IMMUTABLE: abs(), length(), upper(), lower()
-- STABLE: current_user, current_database(), current_setting()
-- VOLATILE: random(), nextval(), timeofday(), clock_timestamp()

The Danger of Incorrect Volatility

PostgreSQL TRUSTS your volatility declaration. If you declare a VOLATILE function as IMMUTABLE, the optimizer may cache incorrect results, inline the function, or use it in constant folding—producing wrong query results. Always verify your volatility declaration matches actual function behavior.

Optimizer Advantages of Determinism

When the query optimizer knows a function is deterministic, it can apply several powerful optimizations that dramatically improve query performance. Understanding these optimizations helps you appreciate why designing for determinism matters.

Optimizer Optimizations for Deterministic Functions

•Constant Folding — If function inputs are literals or constants, the optimizer can evaluate the function once at compile time and substitute the result. WHERE column = CalculateTax(100.00, 0.08) becomes WHERE column = 8.00.
•Common Subexpression Elimination — When the same deterministic function call appears multiple times, the optimizer can evaluate it once and reuse the result. Essential for queries that reference the same function in SELECT and WHERE.
•Result Caching — Within a query, deterministic function results for identical inputs can be cached, avoiding repeated computation for the same parameter values.
•Predicate Pushdown — For inline functions, the optimizer can push predicates through function calls because it knows the function won't alter the predicate's meaning.
•Parallel Execution — Deterministic functions can safely execute on multiple parallel threads because they produce consistent results regardless of execution order.
•Join Optimization — For functions in join conditions, determinism ensures the join produces the same result regardless of which table is processed first.

optimizer_optimizations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Constant Folding demonstration
 
-- Deterministic function
CREATE FUNCTION dbo.CircleArea(@Radius FLOAT)
RETURNS FLOAT
WITH SCHEMABINDING
AS
BEGIN
    RETURN PI() * @Radius * @Radius;
END;
 
-- This query:
SELECT ProductId, Price
FROM Products
WHERE Price > dbo.CircleArea(10);
 
-- Optimizer MAY simplify to:
SELECT ProductId, Price
FROM Products
WHERE Price > 314.159265358979;  -- Computed at compile time
 
-- Common Subexpression Elimination
 
-- Query with repeated function call:
SELECT 
    ProductId,
    Price,
    dbo.CalculateTax(Price, 0.08) AS Tax,
    Price + dbo.CalculateTax(Price, 0.08) AS TotalWithTax
FROM Products
WHERE dbo.CalculateTax(Price, 0.08) > 10;
 
-- For each row, the function could be called 3 times
-- With deterministic + optimization: called ONCE per row
 
-- Parallel execution visibility
-- Check if function blocks parallelism
SELECT 
    p.query_plan,
    p.query_plan.query('//RelOp[@PhysicalOp="Parallelism"]') AS ParallelOps
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p
WHERE qs.sql_handle = /* your query's handle */;
 
-- Non-deterministic functions often show:
-- "NonParallelPlanReason = UDFInsideFilter"

Deterministic Functions

The Promise of Predictability

Defining Determinism

Why Determinism Matters

Verifying Function Determinism

Designing for Determinism

SCHEMABINDING Deep Dive

PostgreSQL Volatility Categories

Optimizer Advantages of Determinism

There’s more to understand.

Deterministic Functions

The Promise of Predictability

Defining Determinism

Why Determinism Matters

Verifying Function Determinism

Designing for Determinism

SCHEMABINDING Deep Dive

PostgreSQL Volatility Categories

Optimizer Advantages of Determinism

There’s more to understand.