Travailler avec des CTE : Common Table Expressions de SQL Server

29/07/2014 – Florian Eiden : Corrections suite aux remarques très judicieuses d’Olivier Pieri en commentaire

Les CTE, Common Table Expressions ou Expressions de table communes, ont été implémentées à partir de la version 2005 de SQL Server, mais restent trop peu utilisées, à mon goût, aux vues de leur utilité !

Elles permettent de remplacer les sous requêtes qui rendent un code illisible (et souvent sans commentaire) dans une requête standard.

Également au menu : la possibilité d’écrire des requêtes récursives tout en restant sur des commandes ensemblistes. Parce que les curseurs c’est sympa, mais traiter les données ligne par ligne ça l’est moins.

Un code plus lisible

Exemple sur une requête simple qui retourne le produit le plus acheté par les clients :

La version en sous requête

SELECT *
FROM (
SELECT C.NomClient
, C.PrenomClient
, NomProduit
, RANK() OVER (PARTITION BY C.ClientID ORDER BY SUM(P.MontantProduit * A.QuantiteProduit) DESC) RankingProduit
FROM Achats A
INNER JOIN Clients C
ON A.ClientID = C.ClientID
INNER JOIN A.Produits P
ON A.ProduitID = P.ProduitID
GROUP BY C.NomClient
, C.PrenomClient
, NomProduit
) TMP
WHERE RankingProduit = 1

La version en CTE

WITH RankingProduit AS (
SELECT C.NomClient
, C.PrenomClient
, NomProduit
, P.MontantProduit
, SUM(A.QuantiteProduit)
, RANK() OVER (PARTITION BY C.ClientID ORDER BY SUM(P.MontantProduit * A.QuantiteProduit) DESC) RankingProduit
FROM Achats A
INNER JOIN Clients C
ON A.ClientID = C.ClientID
INNER JOIN A.Produits P
ON A.ProduitID = P.ProduitID
GROUP BY C.NomClient
, C.PrenomClient
, NomProduit
, P.MontantProduit
)

SELECT *
FROM RankingProduit
WHERE RankingProduit = 1

Certes, sur une requête simple le gain en lisibilité n’est pas flagrant, mais il est bon de savoir que nous pouvons cumuler les CTE et réutiliser le résultat d’une table précédente. Nous pourrons donc avoir une requête avec des étapes dans l’ordre, ce qui, avec des commentaires, simplifie grandement le travail des personnes qui passeront derrière nous (parfois nous même).

Les tables de travail unifiées dans une requête

Dans certains cas, elles nous permettront de contourner l’utilisation de procédures stockées avec des tables temporaires. Quand, par exemple, nous avons des données de différents systèmes qui ne sont pas traitées pour être au même format, nous pourrons utiliser des tables de travail pour tout faire dans une seule et même requête :

WITH Source1 AS ([Traitement Source1])
, Souce2 AS ([Traitement Source2])

SELECT *
FROM Source1 S1
INNER JOIN Source2 S2
ON S1.ID = S2.ID

Au niveau des performances pures, l’usage des CTE est complétement transparent.

Les CTE, pour plus de performances

Je ne vois donc aucune raison de ne pas utiliser les tables de travail, que ce soit pour la lisibilité ou les performances, je ne peux que vous conseiller de vous en servir au maximum.