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

3 Commentaires Laisser un commentaire

Olivier PIERI
juin 28, 2013 10:15

L’utilisation du terme work table pour parler des Common Table Expression n’est pas très judicieux.
Surtout que les work table, dans sql server, c’est completement autre chose… (table de travail interne a l’optimiseur pour executer certaines requites).

cordialement

Répondre
Olivier PIERI
juin 28, 2013 14:22

et pour etre un peu plus précis, l’apport essentiel des Common Table Expression est de pouvoir faire des requêtes recursives. de pouvoir parcourir des hiercarchies et gérer les nomenclatures, ce qui n’est pas faisable de maniere ensembliste sans elle.

Répondre
Arnaud Villenave
Arnaud Villenave
juillet 1, 2013 09:39

Faire du récursif c’est toujours risqué. Certes tu gagnes en taille de code mais il a beaucoup de risque qu’il te fasse plus de temps pour comprendre ce que fait véritablement le code…
OUI on peut faire du récursif avec mais perso j’utilise ce format principalement pour les perf car la différence est notable par rapport à des requêtes imbriquées.

Répondre

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *