L’objet de ces trois articles est d’aborder un problème qui ne semble pas possible de réaliser simplement avec une requête SQL, la récursivité. La solution réside dans les CTE (expressions de tables communes).
Dans le premier article, nous posons les bases du problème. Dans le deuxième article, nous expliquons ce que sont les expressions CTE. Enfin, le dernier article montre comment la récursivité est traitée avec les expressions CTE.

Les expressions de tables communes

Si nous regardons dans la documentation, nous pouvons lire la chose suivante :

Une expression de table commune (CTE, Common Table Expression) peut être considérée comme un jeu de résultats temporaire défini dans l’étendue d’exécution d’une seule instruction SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Une expression CTE est comparable à une table dérivée, en ce sens qu’elle n’est pas stockée sous forme d’objet et dure uniquement le temps de la requête. Contrairement à une table dérivée, une expression CTE peut faire référence à elle-même est être référencée plusieurs fois dans la même requête.

Par la suite, la même documentation précise :

Une expression CTE peut être utilisée pour :

• créer une requête récursive.
• remplacer une vue lorsque l’usage général d’une vue n’est pas nécessaire, c’est-à-dire que la définition n’a pas besoin d’être stockée dans des métadonnées ;
• permettre le groupement par une colonne dérivée d’une sous-sélection scalaire, ou d’une fonction non déterministe ou à accès externe ;
• faire plusieurs fois référence à la table résultante dans la même instruction

Si nous essayons de résumer tout cela, une expression CTE permet de définir une sorte de table temporaire dont la durée de vie est celle de la requête dont l’expression CTE appartient.
Pour rappel, les tables dérivées correspondent aux instructions SELECT imbriquées dans la clause FROM d’un SELECT principal. L’instruction SELECT imbriquée est mis entre parenthèses et possède un alias de nom. Voici un exemple de tables dérivées :

SELECT [FirstName] + N' ' + [LastName] AS [EmployeeName],
ISNULL([NumberDirectEmployeeReported], 0) AS NumberDirectEmployeeReported
FROM [dbo].[MyEmployees] AS [E] LEFT JOIN
(SELECT [ManagerID], COUNT([EmployeeID]) AS [NumberDirectEmployeeReported] FROM [dbo].[MyEmployees] GROUP BY [ManagerID]) AS [DirectReported]
ON [E].[EmployeeID] = [DirectReported].[ManagerID]
ORDER BY [EmployeeName];

Ici, la table dérivée s’appelle [DirectReported]. Le résultat de cette requête est le suivant :

Nous allons utiliser cette requête et la transformer pour qu’elle retourne le même résultat mais en utilisant une expression CTE à la place de la table dérivée.
Une expression CTE se décompose en trois parties :

1. Le nom de l’expression CTE et le nom des colonnes associées ;
2. Le corps de l’expression CTE décrit sous la forme d’une instruction SELECT ;
3. Enfin, l’instruction principale qui peut être de type SELECT, INSERT, UPDATE ou DELETE et qui utilise le nom de l’expression CTE et le nom des colonnes associées.

Donc, une instruction utilisant une expression CTE ressemble à ce qui suit (cas d’une instruction principale SELECT) :

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
SELECT <column_list>
FROM expression_name;

Pour écrire une instruction avec une expression CTE, il faut écrire dans un premier temps, le contenu de l’expression CTE.
D’abord l’entête, c’est-à-dire nom et colonnes :

WITH DirectReported]([ManagerID], [NumberDirectEmployeeReported]) AS (

Ensuite, nous écrivons le corps de l’expression CTE :

SELECT [ManagerID], COUNT([EmployeeID]) FROM [dbo].[MyEmployees] GROUP BY [ManagerID]

Et enfin, l’instruction SELECT principale appelle l’expression CTE en utilisant le nom de cette dernière :

SELECT [FirstName] + N' ' + [LastName] AS [EmployeeName],
ISNULL([NumberDirectEmployeeReported], 0) AS NumberDirectEmployeeReported
FROM [dbo].[MyEmployees] AS [E] LEFT JOIN
[DirectReported] ON [E].[EmployeeID] = [DirectReported].[ManagerID]
ORDER BY [EmployeeName];

Maintenant, rassemblons tout cela pour obtenir le résultat final :

WITH [DirectReported]([ManagerID], [NumberDirectEmployeeReported]) AS (
SELECT [ManagerID], COUNT([EmployeeID]) FROM [dbo].[MyEmployees] GROUP BY [ManagerID])
SELECT [FirstName] + N' ' + [LastName] AS [EmployeeName],
ISNULL([NumberDirectEmployeeReported], 0) AS NumberDirectEmployeeReported
FROM [dbo].[MyEmployees] AS [E] LEFT JOIN
[DirectReported] ON [E].[EmployeeID] = [DirectReported].[ManagerID]
ORDER BY [EmployeeName]

Cette nouvelle requête nous fournit le même résultat que la requête utilisant la table dérivée.
A ce point de notre exposé, intéressons-nous plus particulièrement à la comparaison des plans d’exécution des deux instructions, à savoir la première avec la table dérivée et la seconde avec l’expression CTE.

Nous noterons que les deux plans d’exécutions sont strictement identiques.
Je vous propose ici de lister les principaux avantages des expressions CTE :

• Permet de traiter des problèmes complexes. À noter que dans une instruction, il est possible d’ajouter plusieurs expressions CTE ;
• Une seule instruction est utilisée (aucun autre objet n’est nécessaire comme des vues ou des tables temporaires) ;
• Généralement, on considère que les instructions utilisant les expressions CTE sont plus lisibles et donc maintenable que d’autres solutions ;
• Les performances des instructions utilisant des expressions CTE sont généralement les plus performantes. Ceci vient du fait que l’expression CTE est incluse dans le plan d’exécution avec le reste de l’instruction principale et est optimisée par l’optimiseur statistique. Pour plus de détails, nous pourrons nous reporter à cet article.

Dans le prochain article, je vous propose d’écrire une instruction utilisant une expression CTE de type récursif.
Bien à vous