Utilisation d'expressions de table communes
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.
Une expression CTE peut être utilisée pour :
créer une requête récursive. Pour plus d'informations, consultez Requêtes récursives utilisant des expressions de table communes.
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.
L'utilisation d'une expression CTE présente pour avantages une meilleure lisibilité et une plus grande facilité de maintenance des requêtes complexes. La requête peut être divisée en blocs de construction logiques simples. Ces blocs simples peuvent servir ensuite à créer des expressions CTE intermédiaires plus complexes jusqu'à la génération du jeu de résultats final.
Les expressions CTE peuvent être écrites dans des routines définies par l'utilisateur, telles que des fonctions, des procédures stockées, des déclencheurs ou des vues.
Structure d'une expression CTE
Une expression CTE est composée du nom représentant l'expression CTE, d'une liste de colonnes facultative et d'une requête définissant l'expression CTE. Une fois l'expression CTE définie, elle peut être référencée comme une table ou une vue dans une instruction SELECT, INSERT, UPDATE ou DELETE. Une expression CTE peut également être utilisée dans une instruction CREATE VIEW dans le cadre de son instruction SELECT de définition.
La structure de base de la syntaxe d'une expression CTE est la suivante :
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
La liste des noms de colonne est facultative uniquement si des noms distincts pour toutes les colonnes résultantes sont fournis dans la définition de la requête.
L'instruction permettant d'exécuter l'expression CTE est la suivante :
SELECT <column_list>
FROM expression_name;
Exemple
L'exemple suivant montre les composants de la structure CTE : nom d'expression, liste de colonnes et requête. L'expression CTE Sales_CTE possède trois colonnes (SalesPersonID, SalesOrderID, and OrderDate) et est définie comme le nombre total de commandes par an pour chaque représentant commercial.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Voici un jeu de résultats partiel :
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003