i

Description

Un établi en bois avec un compas à pointes sèches, un carnet de cuir et un pied de lampe en tuyau.

Source

Jeff Sheldon (ugmonk) · page d’origine

Licence

CC0 Domaine public

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

Les moyens du T-SQL

Les constructions T-SQL qui permettent de rester ensembliste, avec leur version minimale.

En bref

• Lire : jointures, EXISTS, agrégats, fenêtres, CTE, APPLY.
• Écrire : INSERT…SELECT, UPDATE/DELETE…FROM, MERGE (prudence), OUTPUT.
• Générer : GENERATE_SERIES ou table de nombres.
• Piège : cadre de fenêtre par défaut en RANGE.

Sur cette page

1. Panorama des fonctionnalités et de leurs versions
2. Jointures, semi-jointures et anti-jointures
3. Agrégats et regroupements
4. Fonctions de fenêtre
5. Expressions de table (CTE)
6. APPLY
7. Opérateurs ensemblistes
8. Écritures ensemblistes
9. Types table et paramètres table
10. Séries et tables de nombres
11. Fonctions : en ligne ou scalaires ?
12. Index et statistiques
13. Observer
14. Traitement par lots
15. Sources et vérification

Panorama des fonctionnalités et de leurs versions#

Les versions indiquées sont celles de la première disponibilité dans SQL Server (et, sauf mention contraire, dans Azure SQL Database).

Jointures, semi-jointures et anti-jointures#

La jointure rapproche deux ensembles. Quand on veut seulement tester l'existence d'une correspondance, EXISTS exprime l'intention sans multiplier les lignes (semi-jointure) ; NOT EXISTS exprime l'absence (anti-jointure) et reste correct en présence de NULL, contrairement à NOT IN.

-- Semi-jointure : clients ayant au moins une commande
SELECT c.ClientId FROM dbo.Clients AS c
WHERE EXISTS (SELECT 1 FROM dbo.Commandes AS o WHERE o.ClientId = c.ClientId);

-- Anti-jointure : clients sans commande (sûre vis-à-vis des NULL)
SELECT c.ClientId FROM dbo.Clients AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Commandes AS o WHERE o.ClientId = c.ClientId);

Agrégats et regroupements#

GROUP BY réduit un ensemble à un ensemble de groupes. GROUPING SETS, ROLLUP et CUBE calculent plusieurs niveaux de regroupement en une seule passe.

SELECT ClientId, YEAR(DateCommande) AS Annee, SUM(Montant) AS Total
FROM   dbo.Commandes
GROUP BY ROLLUP (ClientId, YEAR(DateCommande));   -- sous-totaux par client et total général

Fonctions de fenêtre#

Une fonction de fenêtre calcule une valeur pour chaque ligne sans réduire l'ensemble : classement, valeur précédente ou suivante, cumuls, moyennes mobiles. Trois éléments la définissent : PARTITION BY (découpage), ORDER BY (ordre logique), et le cadre ROWS/RANGE.

SELECT ClientId, DateCommande, Montant,
       ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId) AS Rang,
       LAG(Montant)  OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId) AS MontantPrecedent,
       SUM(Montant)  OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)           AS Cumul
FROM   dbo.Commandes;

Expressions de table (CTE)#

Une CTE nomme un sous-ensemble pour la lisibilité. Elle n'est pas matérialisée par principe : c'est un habillage de la requête, que l'optimiseur peut réécrire. La CTE récursive sert aux hiérarchies et aux graphes ; elle est bornée par MAXRECURSION (100 par défaut).

-- CTE récursive : parcours d'une hiérarchie
WITH Arbre AS (
    SELECT NoeudId, ParentId, 0 AS Niveau FROM dbo.Noeuds WHERE ParentId IS NULL
    UNION ALL
    SELECT n.NoeudId, n.ParentId, a.Niveau + 1
    FROM   dbo.Noeuds AS n
    JOIN   Arbre AS a ON a.NoeudId = n.ParentId
)
SELECT NoeudId, ParentId, Niveau FROM Arbre
OPTION (MAXRECURSION 100);

APPLY#

CROSS APPLY / OUTER APPLY évaluent une expression de table pour chaque ligne de l'ensemble de gauche, ce qui permet les « top N par groupe » et le dépliage de valeurs. Utilisé avec une fonction table en ligne ou une sous-requête, il reste ensembliste : l'optimiseur le réécrit en jointure quand c'est possible.

-- Les 3 dernières commandes de chaque client
SELECT c.ClientId, d.CommandeId, d.DateCommande
FROM   dbo.Clients AS c
CROSS APPLY (SELECT TOP (3) o.CommandeId, o.DateCommande
             FROM dbo.Commandes AS o
             WHERE o.ClientId = c.ClientId
             ORDER BY o.DateCommande DESC, o.CommandeId DESC) AS d;

Opérateurs ensemblistes#

UNION ALL concatène (sans coût de déduplication), UNION déduplique, INTERSECT garde les lignes communes, EXCEPT retire les lignes du second ensemble. EXCEPT dans les deux sens est le moyen le plus simple de comparer deux résultats.

Écritures ensemblistes#

• INSERT … SELECT charge un ensemble en une instruction.
• UPDATE … FROM … JOIN et DELETE … FROM … JOIN modifient en fonction d'une autre table (veiller au déterminisme, voir principes).
• MERGE combine insertion, mise à jour et suppression ; plusieurs problèmes connus incitent à l'utiliser avec prudence et à envisager des instructions séparées.
• OUTPUT renvoie ou enregistre les lignes affectées (avant/après) : audit sans second passage.

-- Écriture ensembliste avec trace des changements
UPDATE c
SET    c.TauxRemise = r.Taux
OUTPUT inserted.CommandeId, deleted.TauxRemise AS Ancien, inserted.TauxRemise AS Nouveau
INTO   dbo.Audit_Remises (CommandeId, Ancien, Nouveau)
FROM   dbo.Commandes AS c
JOIN   dbo.Remises  AS r ON r.CategorieId = c.CategorieId;

Types table et paramètres table#

Un type table (CREATE TYPE … AS TABLE, SQL Server 2008) permet de passer un jeu de lignes à une procédure ou une fonction (paramètre READONLY). L'appelant fige l'ensemble ; la routine le traite sans le recalculer, à l'abri des autres transactions. Détails, démonstration et limites : Déterminisme, §7.

Séries et tables de nombres#

Génération de séquences (calendriers, plages, jeux de test) sans boucle :

-- Série de nombres sans boucle
-- SQL Server 2022 et Azure SQL :
SELECT value AS n FROM GENERATE_SERIES(0, 99);

-- Avant 2022 : table de nombres par produit cartésien
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;

Fonctions : en ligne ou scalaires ?#

Les fonctions table en ligne (une seule instruction RETURN SELECT …) sont développées dans la requête appelante, comme une vue paramétrée : elles restent ensemblistes. Les fonctions scalaires sont, historiquement, exécutées ligne par ligne, sans parallélisme ni estimation de coût correcte. SQL Server 2019 (niveau de compatibilité 150) peut inliner certaines d'entre elles sous conditions ; ce n'est ni systématique, ni rétroactif. Les fonctions table à instructions multiples ont également des estimations de cardinalité limitées. Voir aussi le déterminisme des fonctions et la génération de code et l'inlining.

Index et statistiques#

L'approche ensembliste ne dispense pas d'index : elle les rend utiles. Pour une fonction de fenêtre, un index dont les colonnes clés suivent PARTITION BY puis ORDER BY évite un tri coûteux. Des statistiques à jour sont nécessaires à de bonnes estimations. Un index columnstore ou le mode batch (SQL Server 2019, niveau de compatibilité 150) peut accélérer les opérateurs de fenêtre sur de gros volumes.

Observer#

• Le plan d'exécution réel : opérateurs, estimations contre réalité, avertissements (déversements, conversions implicites).
• SET STATISTICS IO, TIME ON : lectures logiques et temps CPU.
• Le Query Store (SQL Server 2016+) : historique des plans et de leurs performances.

Voir Mesurer sérieusement.

Traitement par lots#

Quand un traitement doit être découpé (limiter le journal, les verrous, la durée d'une transaction), on boucle sur des lots d'ensembles, pas sur des lignes. La boucle est alors un mécanisme d'ordonnancement ; chaque itération reste ensembliste.

-- Traitement par lots : une boucle, mais sur des ENSEMBLES de 50 000 lignes
DECLARE @n int = 1;
WHILE @n > 0
BEGIN
    DELETE TOP (50000) FROM dbo.Journal
    WHERE  DateEvenement < DATEADD(YEAR, -2, SYSUTCDATETIME());
    SET @n = @@ROWCOUNT;
END;

Sources et vérification#

• Microsoft Learn — Clause OVER — cadre par défaut, ROWS/RANGE (SQL Server 2012+), conseils de performance
• Microsoft Learn — STRING_AGG — SQL Server 2017+
• Microsoft Learn — Scalar UDF Inlining — SQL Server 2019, niveau de compatibilité 150
• Microsoft Learn — Référence Transact-SQL — documentation officielle ; vérifier la disponibilité par version