i

Description

Le mécanisme d'une horloge

Source

NPS photo · page d’origine

Licence

PD Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Déterminisme et objets déterministes

Ce que le moteur accorde aux objets déterministes, comment en écrire, et comment isoler une procédure des autres transactions avec un paramètre table.

En bref

• Deux sens : résultat reproductible / propriété d'un objet.
• Droits : indexer (colonne calculée, vue indexée), plier les constantes, inliner une fonction.
• Recette : SCHEMABINDING, aucun appel non déterministe, le contexte en paramètre.
• Type table : fige l'ensemble traité, isole des autres transactions ; ne rend pas la procédure déterministe, ne renseigne pas l'optimiseur.

Sur cette page

1. 1. Deux sens du mot « déterministe »
2. 2. Ce que dit la documentation
3. 3. Pourquoi les objets déterministes sont précieux
   1. 3.1 Précalculer à l'écriture plutôt que recalculer à chaque lecture
   2. 3.2 Laisser l'optimiseur raisonner
   3. 3.3 Reproduire, tester, recharger
   4. 3.4 Rendre le code généré reproductible
4. 4. Rendre une fonction déterministe
5. 5. Colonnes calculées et vues indexées : ce que le moteur accepte, ce qu'il refuse
6. 6. Pièges
7. 7. Types table et paramètres table : figer le contexte d'exécution
   1. Ce que la documentation dit des paramètres table
8. 8. En résumé
9. Sources et vérification

Le mot « déterministe » revient souvent en T-SQL, et il recouvre des choses voisines. Cette page distingue les deux sens, montre les droits concrets que SQL Server accorde aux objets déterministes, et explique comment figer le contexte d'exécution d'une procédure avec un paramètre table.

1. Deux sens du mot « déterministe »#

• Le résultat d'une requête est reproductible : mêmes données, même résultat, quel que soit le plan (voir Principes, §7). C'est une qualité du traitement.
• Un objet ou une expression est déterministe : pour les mêmes valeurs d'entrée et le même état de la base, il rend toujours la même valeur. C'est une propriété que le moteur reconnaît, vérifie, et dont il tire des droits (indexer, précalculer, plier, inliner).

2. Ce que dit la documentation#

Une fonction déterministe rend toujours le même résultat pour un jeu donné de valeurs d'entrée et le même état de la base ; une fonction non déterministe peut rendre un résultat différent à chaque appel (par exemple GETDATE()). Le déterminisme d'une fonction intégrée ne se choisit pas : il est fixé par l'implémentation. Quelques repères, d'après la documentation de Microsoft :

3. Pourquoi les objets déterministes sont précieux#

3.1 Précalculer à l'écriture plutôt que recalculer à chaque lecture#

Le moteur n'accepte d'indexer le résultat d'une expression que s'il peut compter dessus : colonne calculée persistée ou indexée, vue indexée. C'est la forme la plus puissante d'ensembliste : un calcul est déplacé du moment de la lecture, souvent répété, au moment de l'écriture, une seule fois, et le moteur maintient le résultat.

3.2 Laisser l'optimiseur raisonner#

• Le repliement de constantes (évaluation à la compilation) ne s'applique pas aux « fonctions non déterministes » ni aux « fonctions utilisateur T-SQL », qui figurent parmi les expressions non repliables du guide d'architecture de traitement des requêtes.
• L'inlining des fonctions scalaires (SQL Server 2019) exclut les fonctions qui appellent une fonction intrinsèque dépendante du temps (GETDATE()) ou à effet de bord (NEWSEQUENTIALID()). Écrire des fonctions déterministes les rend admissibles à l'inlining.

3.3 Reproduire, tester, recharger#

Un traitement déterministe se compare à sa version précédente (EXCEPT dans les deux sens), se rejoue sans effet de bord (idempotence) et se teste avec des jeux de données figés. Un traitement qui lit l'heure, un identifiant aléatoire ou un état global, non.

3.4 Rendre le code généré reproductible#

Le même principe s'applique au générateur de code : à référentiel identique, il doit produire le même texte, octet pour octet (voir la page sur la génération de code).

4. Rendre une fonction déterministe#

1. La lier au schéma (WITH SCHEMABINDING) : la documentation l'exige pour les fonctions déterministes.
2. N'appeler que des fonctions déterministes et n'accéder à aucune ressource externe ; une fonction qui appelle une procédure étendue est non déterministe.
3. Transformer le contexte en paramètre : la date de référence, la langue, le pays deviennent des arguments (« fonction pure »).
4. Vérifier avec OBJECTPROPERTY(id, 'IsDeterministic') pour un objet et COLUMNPROPERTY(…, 'IsDeterministic') pour une colonne de vue.

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)          -- pas de SCHEMABINDING
RETURNS int
AS BEGIN RETURN @x * 2; END;
GO
CREATE FUNCTION dbo.f_Maintenant ()                 -- appelle 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;

Faire de la date de référence une entrée donne une fonction rejouable, testable et indexable :

-- Non déterministe : le résultat dépend de l'heure d'exécution
CREATE FUNCTION dbo.f_AgeEnJours_Mauvaise (@DateNaissance date) RETURNS int
AS BEGIN RETURN DATEDIFF(DAY, @DateNaissance, GETDATE()); END;
GO
-- Déterministe : la date de référence devient une entrée
CREATE FUNCTION dbo.f_AgeEnJours (@DateNaissance date, @DateReference date) RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN DATEDIFF(DAY, @DateNaissance, @DateReference); END;
GO

5. Colonnes calculées et vues indexées : ce que le moteur accepte, ce qu'il refuse#

CREATE TABLE dbo.T
(
    Id         int      NOT NULL PRIMARY KEY,
    Val        int      NOT NULL,
    DateSaisie datetime NOT NULL DEFAULT (GETDATE())
);
GO
-- 1) Expression déterministe : persistée et indexée
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) Fonction non liée au schéma : refusée
ALTER TABLE dbo.T ADD Double_NSB AS dbo.f_Double_NSB(Val) PERSISTED;
GO
-- 3) Fonction non déterministe : l'index est refusé
ALTER TABLE dbo.T ADD Maint AS dbo.f_Maintenant();
GO
CREATE INDEX IX_T_Maint ON dbo.T (Maint);
GO
-- 4) Même cas sans fonction utilisateur : GETDATE() dans l'expression
ALTER TABLE dbo.T ADD AgeJours AS DATEDIFF(DAY, DateSaisie, GETDATE());
GO
CREATE INDEX IX_T_Age ON dbo.T (AgeJours);
GO

Vue indexée (agrégat précalculé) et refus en cas de 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
-- Vue indexée : agrégat précalculé, maintenu par le moteur à chaque écriture
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
-- Avec GETDATE() dans la définition : l'index est refusé
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

Résultat de la requête sur la vue indexée : 2026-01-01 · 100,00 · 1 ; 2026-01-02 · 75,50 · 2 ; 2026-01-05 · 10,00 · 1. La dernière instruction échoue avec Msg 1949 : « The function 'getdate' yields nondeterministic results. »

6. Pièges#

• Options SET imposées aux vues indexées : ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER à ON ; NUMERIC_ROUNDABORT à OFF ; ARITHABORT à ON. Le non-respect peut empêcher la création, l'écriture ou l'utilisation de la vue.
• Coût en écriture : chaque modification d'une table référencée par des vues indexées met ces vues à jour ; la documentation prévient que les performances des écritures peuvent se dégrader nettement.
• Réels et dates : une expression contenant des float est déterministe mais pas « précise » (elle ne peut pas servir de clé) ; les conversions implicites de chaînes en datetime sont non déterministes : convertir explicitement avec un style déterministe.
• Fonctions scalaires : une requête qui en appelle une ne s'exécute pas en parallèle (hors inlining). Déterminisme et performances sont deux sujets distincts.
• Utilisation automatique d'une vue indexée : selon l'édition de SQL Server, il faut l'indice NOEXPAND ; Azure SQL la substitue automatiquement.

7. Types table et paramètres table : figer le contexte d'exécution#

Un type table (CREATE TYPE … AS TABLE) permet de passer un jeu de lignes en paramètre à une procédure ou à une fonction. La procédure reçoit ainsi l'ensemble à traiter tel que l'appelant l'a figé, au lieu de le recalculer elle-même à partir de tables que d'autres transactions modifient.

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);   -- clé unique et ordre déclarés dans le type
GO
-- A) La procédure relit la table de base : l'ensemble traité peut changer en cours de route
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) La procédure reçoit l'ensemble figé par l'appelant
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
-- Appel de B : l'appelant fige l'ensemble, une fois
DECLARE @ids dbo.t_Ids;
INSERT INTO @ids (Id) SELECT Id FROM dbo.Fx WHERE Flag = 1;
EXEC dbo.p_Recoit_Tvp @ids;

Pendant la pause, une seconde session modifie la table de base :

-- Session 2, lancée environ 2 secondes après le début de l'appel (la procédure attend 4 secondes)
UPDATE dbo.Fx SET Flag = 1 WHERE Id IN (3, 4);

En A, la procédure voit l'ensemble changer sous ses pieds (niveau d'isolation par défaut, READ COMMITTED). En B, l'ensemble traité est stable du début à la fin de l'exécution, quoi que fassent les autres sessions.

Ce que la documentation dit des paramètres table#

• Avantages : pas de verrous pour la constitution initiale des données côté client ; moins d'allers-retours ; typage fort ; le client peut déclarer l'ordre et les clés uniques ; mise en cache comme une table temporaire.
• Limites : le paramètre est en READONLY (pas de UPDATE, DELETE ni INSERT dans la routine) ; aucune statistique n'est tenue sur ses colonnes ; il ne peut pas être la cible d'un SELECT INTO ou d'un INSERT EXEC.
• Un type table ne se modifie pas : il se recrée, après avoir retiré les routines qui l'utilisent. Traitez-le comme un contrat d'interface versionné.

8. En résumé#

Sources et vérification#

• Microsoft Learn — Deterministic and nondeterministic functions — définition, classement des fonctions intégrées, SCHEMABINDING, OBJECTPROPERTY
• Microsoft Learn — Create indexed views — définition déterministe, options SET, NOEXPAND, coût en écriture
• Microsoft Learn — Use table-valued parameters — avantages et limites des paramètres table
• Microsoft Learn — Query processing architecture guide — repliement de constantes et expressions non repliables
• Microsoft Learn — Scalar UDF Inlining — fonctions exclues de l'inlining