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. Egalement 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.

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).

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.

Pour observer une syntaxe utilisant la récursivité, je vous invite à consulter l’article de Charles-Henri Sauget avec le très bon cas d’usage de la construction d’une dimension date à partir d’une CTE récursive.

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.