Accueil > Travailler avec des CTE : Common Table Expressions de SQL Server
Alexandre Plassais
26 juin 2013

Travailler avec des CTE : Common Table Expressions de SQL Server

Comment travailler avec des 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.

Nos autres articles
Commentaires

Bonjour,
est ce que dans cet exemple, les CTE peuvent être utiles sur une requête WHERE MATCH associée à INNER JOIN ?
Cette requête fonctionne tant que je n’ajoute pas le INNER JOIN :
SELECT Animal2.Name, Animal2.BreederId, Animal2.Male, Animal2.BirthDate, Animal2.NickName
FROM Animal Animal1, GenealogyLink, Animal Animal2
WHERE MATCH(Animal1-(GenealogyLink)->Animal2)
INNER JOIN Breeder ON Animal2.BreederId = Breeder.Id
AND Animal1.Name = ‘J »Sirène’
AND Animal1.BreederId = 3
ORDER BY Animal1.Name ASC;

Merci,
Vincent

(bon, le temps que je poste le message, j’ai eu une réponse sur MSDN, Cellenza m’a porté chance ^^)

WITH CTE AS (
SELECT Animal2.Name, Animal2.Male, Animal2.BirthDate, Animal2.NickName, Animal1.BreederId AS AffixBreederId
FROM Animal Animal1, GenealogyLink, Animal Animal2
WHERE MATCH(Animal1-(GenealogyLink)->Animal2)
AND Animal1.Name = ‘J »Sirène’
AND Animal1.BreederId = 3
)
SELECT CTE.Name, CTE.Male, Breeder.Affix, CTE.BirthDate, CTE.NickName
FROM CTE
INNER JOIN Breeder ON CTE.AffixBreederId = Breeder.Id

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.

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.

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Restez au courant des dernières actualités !
Le meilleur de l’actualité sur le Cloud, le DevOps, l’IT directement dans votre boîte mail.