i

Description

The mechanism of a clock

Source

NPS photo · original page

Licence

PD Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Determinism and deterministic objects

What the engine grants to deterministic objects, how to write them, and how to isolate a procedure from other transactions with a table parameter.

In brief

• Two meanings: reproducible result / property of an object.
• Rights: index (computed column, indexed view), fold constants, inline a function.
• Recipe: SCHEMABINDING, no non-deterministic call, context as a parameter.
• Table type: freezes the set being processed, isolates from other transactions; does not make the procedure deterministic, does not inform the optimizer.

On this page

1. 1. Two meanings of the word “deterministic”
2. 2. What the documentation says
3. 3. Why deterministic objects are valuable
   1. 3.1 Precompute at write time rather than recompute at every read
   2. 3.2 Let the optimizer reason
   3. 3.3 Reproduce, test, reload
   4. 3.4 Make generated code reproducible
4. 4. Making a function deterministic
5. 5. Computed columns and indexed views: what the engine accepts, what it refuses
6. 6. Pitfalls
7. 7. Table types and table parameters: freezing the execution context
   1. What the documentation says about table parameters
8. 8. Summary
9. Sources and verification

The word “deterministic” comes up often in T-SQL, and it covers related but different things. This page distinguishes the two meanings, shows the concrete rights SQL Server grants to deterministic objects, and explains how to freeze the execution context of a procedure with a table parameter.

1. Two meanings of the word “deterministic”#

• The result of a query is reproducible: same data, same result, whatever the plan (see Principles, §7). This is a quality of the process.
• An object or expression is deterministic: for the same input values and the same database state, it always returns the same value. This is a property that the engine recognizes, verifies, and from which it derives rights (index, precompute, fold, inline).

2. What the documentation says#

A deterministic function always returns the same result for a given set of input values and the same database state; a non-deterministic function can return a different result on each call (for example GETDATE()). The determinism of a built-in function is not a choice: it is fixed by the implementation. A few reference points, based on Microsoft's documentation:

3. Why deterministic objects are valuable#

3.1 Precompute at write time rather than recompute at every read#

The engine agrees to index the result of an expression only if it can count on it: persisted or indexed computed column, indexed view. This is the most powerful form of set-based thinking: a calculation is moved from read time, often repeated, to write time, once, and the engine maintains the result.

3.2 Let the optimizer reason#

• Constant folding (evaluation at compile time) does not apply to “non-deterministic functions” or to “T-SQL user-defined functions”, which are among the non-foldable expressions in the query processing architecture guide.
• Scalar function inlining (SQL Server 2019) excludes functions that call a time-dependent intrinsic function (GETDATE()) or one with side effects (NEWSEQUENTIALID()). Writing deterministic functions makes them eligible for inlining.

3.3 Reproduce, test, reload#

A deterministic process can be compared with its previous version (EXCEPT in both directions), replayed with no side effects (idempotence) and tested with frozen data sets. A process that reads the time, a random identifier or a global state cannot.

3.4 Make generated code reproducible#

The same principle applies to the code generator: with identical reference data, it must produce the same text, byte for byte (see the page on code generation).

4. Making a function deterministic#

1. Bind it to the schema (WITH SCHEMABINDING): the documentation requires this for deterministic functions.
2. Call only deterministic functions and access no external resource; a function that calls an extended procedure is non-deterministic.
3. Turn the context into a parameter: the reference date, the language, the country become arguments (a “pure function”).
4. Verify with OBJECTPROPERTY(id, 'IsDeterministic') for an object and COLUMNPROPERTY(…, 'IsDeterministic') for a view column.

CREATE FUNCTION dbo.f_Double_SB (@x int)
RETURNS int WITH SCHEMABINDING
AS BEGIN RETURN @x * 2; END;
GO
CREATE FUNCTION dbo.f_Double_NSB (@x int)          -- no SCHEMABINDING
RETURNS int
AS BEGIN RETURN @x * 2; END;
GO
CREATE FUNCTION dbo.f_Maintenant ()                 -- calls GETDATE()
RETURNS datetime WITH SCHEMABINDING
AS BEGIN RETURN GETDATE(); END;
GO
SELECT name,
       OBJECTPROPERTY(object_id, 'IsDeterministic') AS est_deterministe,
       OBJECTPROPERTY(object_id, 'IsSchemaBound')   AS schema_lie
FROM   sys.objects
WHERE  name IN (N'f_Double_SB', N'f_Double_NSB', N'f_Maintenant')
ORDER BY name;

Making the reference date an input gives a function that is replayable, testable and indexable:

-- Non-deterministic: the result depends on the execution time
CREATE FUNCTION dbo.f_AgeEnJours_Mauvaise (@DateNaissance date) RETURNS int
AS BEGIN RETURN DATEDIFF(DAY, @DateNaissance, GETDATE()); END;
GO
-- Deterministic: the reference date becomes an input
CREATE FUNCTION dbo.f_AgeEnJours (@DateNaissance date, @DateReference date) RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN DATEDIFF(DAY, @DateNaissance, @DateReference); END;
GO

5. Computed columns and indexed views: what the engine accepts, what it refuses#

CREATE TABLE dbo.T
(
    Id         int      NOT NULL PRIMARY KEY,
    Val        int      NOT NULL,
    DateSaisie datetime NOT NULL DEFAULT (GETDATE())
);
GO
-- 1) Deterministic expression: persisted and indexed
ALTER TABLE dbo.T ADD Double_SB AS dbo.f_Double_SB(Val) PERSISTED;
GO
CREATE INDEX IX_T_Double ON dbo.T (Double_SB);
GO
-- 2) Function not bound to the schema: refused
ALTER TABLE dbo.T ADD Double_NSB AS dbo.f_Double_NSB(Val) PERSISTED;
GO
-- 3) Non-deterministic function: the index is refused
ALTER TABLE dbo.T ADD Maint AS dbo.f_Maintenant();
GO
CREATE INDEX IX_T_Maint ON dbo.T (Maint);
GO
-- 4) Same case without a user-defined function: GETDATE() in the expression
ALTER TABLE dbo.T ADD AgeJours AS DATEDIFF(DAY, DateSaisie, GETDATE());
GO
CREATE INDEX IX_T_Age ON dbo.T (AgeJours);
GO

Indexed view (precomputed aggregate) and refusal in the case of GETDATE():

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE dbo.Ventes (VenteId int NOT NULL PRIMARY KEY, DateVente date NOT NULL, Montant decimal(18,2) NOT NULL);
INSERT INTO dbo.Ventes VALUES (1,'2026-01-01',100), (2,'2026-01-02',50), (3,'2026-01-02',25.5), (4,'2026-01-05',10);
GO
-- Indexed view: precomputed aggregate, maintained by the engine on every write
CREATE VIEW dbo.v_VentesParJour WITH SCHEMABINDING AS
SELECT DateVente, SUM(Montant) AS Total, COUNT_BIG(*) AS Nb
FROM   dbo.Ventes
GROUP BY DateVente;
GO
CREATE UNIQUE CLUSTERED INDEX UX_v_VentesParJour ON dbo.v_VentesParJour (DateVente);
GO
SELECT DateVente, Total, Nb FROM dbo.v_VentesParJour WITH (NOEXPAND) ORDER BY DateVente;
GO
-- With GETDATE() in the definition: the index is refused
CREATE VIEW dbo.v_Mauvaise WITH SCHEMABINDING AS
SELECT DateVente, SUM(Montant) AS Total, COUNT_BIG(*) AS Nb
FROM   dbo.Ventes WHERE DateVente < GETDATE() GROUP BY DateVente;
GO
CREATE UNIQUE CLUSTERED INDEX UX_v_Mauvaise ON dbo.v_Mauvaise (DateVente);   -- Msg 1949
GO

Result of the query on the indexed view: 2026-01-01 · 100.00 · 1; 2026-01-02 · 75.50 · 2; 2026-01-05 · 10.00 · 1. The last statement fails with Msg 1949: “The function 'getdate' yields nondeterministic results.”

6. Pitfalls#

• Required SET options for indexed views: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER set to ON; NUMERIC_ROUNDABORT set to OFF; ARITHABORT set to ON. Failing to comply can prevent the view from being created, written to or used.
• Write cost: every modification of a table referenced by indexed views updates those views; the documentation warns that write performance can degrade significantly.
• Floats and dates: an expression containing float values is deterministic but not “precise” (it cannot be used as a key); implicit conversions from strings to datetime are non-deterministic: convert explicitly with a deterministic style.
• Scalar functions: a query that calls one does not run in parallel (outside of inlining). Determinism and performance are two separate topics.
• Automatic use of an indexed view: depending on the SQL Server edition, the NOEXPAND hint is needed; Azure SQL substitutes it automatically.

7. Table types and table parameters: freezing the execution context#

A table type (CREATE TYPE … AS TABLE) lets you pass a set of rows as a parameter to a procedure or function. The procedure thus receives the set to process as the caller froze it, instead of recomputing it itself from tables that other transactions modify.

CREATE TABLE dbo.Fx (Id int NOT NULL PRIMARY KEY, Flag bit NOT NULL, Montant decimal(18,2) NOT NULL);
INSERT INTO dbo.Fx VALUES (1,1,10), (2,1,20), (3,0,30), (4,0,40), (5,0,50);

CREATE TYPE dbo.t_Ids AS TABLE (Id int NOT NULL PRIMARY KEY);   -- unique key and order declared in the type
GO
-- A) The procedure re-reads the base table: the set being processed can change along the way
CREATE PROCEDURE dbo.p_Lit_Base AS
BEGIN
    SELECT N'avant' AS moment, COUNT(*) AS nb_ids, SUM(Montant) AS total FROM dbo.Fx WHERE Flag = 1;
    WAITFOR DELAY '00:00:04';
    SELECT N'après' AS moment, COUNT(*) AS nb_ids, SUM(Montant) AS total FROM dbo.Fx WHERE Flag = 1;
END;
GO
-- B) The procedure receives the set frozen by the caller
CREATE PROCEDURE dbo.p_Recoit_Tvp @ids dbo.t_Ids READONLY AS
BEGIN
    SELECT N'avant' AS moment, COUNT(*) AS nb_ids, SUM(f.Montant) AS total FROM dbo.Fx AS f JOIN @ids AS i ON i.Id = f.Id;
    WAITFOR DELAY '00:00:04';
    SELECT N'après' AS moment, COUNT(*) AS nb_ids, SUM(f.Montant) AS total FROM dbo.Fx AS f JOIN @ids AS i ON i.Id = f.Id;
END;
GO
-- Call to B: the caller freezes the set, once
DECLARE @ids dbo.t_Ids;
INSERT INTO @ids (Id) SELECT Id FROM dbo.Fx WHERE Flag = 1;
EXEC dbo.p_Recoit_Tvp @ids;

During the pause, a second session modifies the base table:

-- Session 2, started about 2 seconds after the call begins (the procedure waits 4 seconds)
UPDATE dbo.Fx SET Flag = 1 WHERE Id IN (3, 4);

In A, the procedure sees the set change under its feet (default isolation level, READ COMMITTED). In B, the set being processed is stable from start to finish of the execution, whatever the other sessions do.

What the documentation says about table parameters#

• Advantages: no locks for the initial population of the data on the client side; fewer round trips; strong typing; the client can declare the order and unique keys; cached like a temporary table.
• Limits: the parameter is READONLY (no UPDATE, DELETE or INSERT in the routine); no statistics are maintained on its columns; it cannot be the target of a SELECT INTO or an INSERT EXEC.
• A table type cannot be altered: it is recreated, after dropping the routines that use it. Treat it as a versioned interface contract.

8. Summary#

Sources and verification#

• Microsoft Learn — Deterministic and nondeterministic functions — definition, classification of built-in functions, SCHEMABINDING, OBJECTPROPERTY
• Microsoft Learn — Create indexed views — deterministic definition, SET options, NOEXPAND, write cost
• Microsoft Learn — Use table-valued parameters — advantages and limits of table parameters
• Microsoft Learn — Query processing architecture guide — constant folding and non-foldable expressions
• Microsoft Learn — Scalar UDF Inlining — functions excluded from inlining