Le Expressions de table communes (CTE) ont été introduits dans le SQL standard afin de simplifier diverses classes de requêtes SQL pour lesquelles une table dérivée était tout simplement inadaptée. CTE a été introduit dans SQL Server 2005, l'expression de table commune (CTE) est un jeu de résultats nommé temporaire que vous pouvez référencer dans un SÉLECTIONNER , INSÉRER , MISE À JOUR , ou SUPPRIMER déclaration. Vous pouvez également utiliser un CTE dans une vue CREATE, dans le cadre de la requête SELECT de la vue. De plus, depuis SQL Server 2008, vous pouvez ajouter un CTE à la nouvelle instruction MERGE.

Pourquoi avons-nous besoin de CTE dans le serveur SQL ?

Une table commune [obsolète]CTE est un puissant SQL construction qui permet de simplifier les requêtes. Les CTE agissent comme des tables virtuelles (avec des enregistrements et des colonnes) créées lors de l'exécution de la requête, utilisées par la requête et supprimées après l'exécution de la requête.

Utiliser le CTE

Nous pouvons définir les CTE en ajoutant une clause WITH directement avant l'instruction SELECT, INSERT, UPDATE, DELETE ou MERGE. La clause WITH peut inclure un ou plusieurs CTE séparés par des virgules.

tableau de réaction

Syntaxe:

[AVEC [, …]]

::=

nom_cte [(nom_colonne [, …])]

COMME (cte_query)

listes Java

Argument

1. Nom de l'expression : Un identifiant valide pour une expression de table commune. Le nom_expression doit être différent des noms des autres expressions de table courantes définies dans la même clause WITH, mais le nom_expression peut être identique au nom de la table ou de la vue de base. Toutes les références à expression_name dans la requête utilisent l'expression de table commune au lieu de l'objet de base.
2. Nom de colonne: Spécifie un nom de colonne dans une expression de table commune. Les noms en double dans une même définition CTE ne sont pas autorisés. Le nombre de noms de colonnes doit correspondre au nombre de colonnes dans le jeu de résultats de CTE_query_definition. Une liste de noms de colonnes est facultative uniquement si toutes les colonnes résultantes dans la définition de requête portent des noms différents.
3. CTE_QueryDefinition : Spécifie une instruction SELECT dont le jeu de résultats satisfait une expression de table commune. L'instruction SELECT pour CTE_query_defining doit répondre aux mêmes exigences que la création d'une vue, sauf qu'un CTE ne peut pas définir un autre CTE. Pour plus d’informations, consultez la section Remarques et CREATE VIEW (Transact-SQL). Si plusieurs CTE_query_settings sont définis, les définitions de requête doivent être combinées avec l'un des opérateurs d'ensemble UNION ALL, UNION, EXCEPT ou INTERSECT.
   

Règles de définition et d'utilisation des expressions de table communes récursives

Les directives suivantes s'appliquent à la définition d'expressions de table communes récursives :

1. Une définition CTE récursive doit contenir au moins deux définitions de requête CTE, un membre d'ancrage et un membre récursif. Vous pouvez définir plusieurs membres d’ancrage et récursifs. Toutefois, toutes les définitions de requête de membre d’ancrage doivent être placées avant la première définition de membre récursive. Toutes les définitions de requête CTE sont des membres d'ancrage, sauf si elles font référence au CTE lui-même.
2. Les éléments d'ancrage doivent être combinés avec l'un des opérateurs d'ensemble suivants : UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL est le seul opérateur d'ensemble valide entre le dernier membre d'ancrage et le premier membre récursif lors de la jointure de plusieurs membres récursifs. Les éléments d'ancrage et récursifs doivent avoir le même nombre de colonnes.
3. Le type de données des colonnes du membre récursif doit être le même que le type de données de la colonne correspondante du membre d’ancrage.
4. Les éléments suivants ne sont pas autorisés dans sa CTE_query_definition pour les membres récursifs :

Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

Création d'une expression de table commune récursive

Un CTE récursif est un CTE qui se référence lui-même au sein de ce CTE. Le CTE récursif est utile lorsque vous travaillez avec des données hiérarchiques, car le CTE continue de s'exécuter jusqu'à ce que la requête renvoie la hiérarchie entière.

Un exemple typique de données hiérarchiques est un tableau qui comprend une liste d'employés. Pour chaque employé, le tableau fournit une référence au manager de cette personne. Cette référence est elle-même un identifiant d'employé dans la même table. Vous pouvez utiliser un CTE récursif pour afficher la hiérarchie des données des employés.

Si un CTE est créé incorrectement, il peut entrer dans une boucle infinie. Pour éviter cela, l'indicateur MAXRECURSION peut être ajouté dans la clause OPTION de l'instruction principale SELECT, INSERT, UPDATE, DELETE ou MERGE.

chaîne pour discuter

Un tableau est créé :

CREATE TABLE Employees (  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL )  INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)>

Une fois la table Employees créée, l'instruction SELECT suivante, précédée d'une clause WITH qui inclut un CTE nommé cteReports, est créée :

WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (  SELECT EmployeeID, FirstName, LastName, ManagerID, 1  FROM Employees  WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,   r.EmpLevel + 1  FROM Employees e  INNER JOIN cteReports r  ON e.ManagerID = r.EmpID  ) SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees   WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports  ORDER BY EmpLevel, MgrID>

Ainsi, les CTE peuvent être utiles lorsque vous devez générer des jeux de résultats temporaires accessibles dans une instruction SELECT, INSERT, UPDATE, DELETE ou MERGE.

Fonctionnalités et limites des expressions de table communes dans Azure

L’implémentation actuelle des CTE dans Azure Synapse Analytics et Analytics Platform System (PDW) présente les fonctionnalités et limitations suivantes :

1. Un CTE ne peut être spécifié que dans une instruction SELECT.
2. Un CTE ne peut être spécifié que dans une instruction CREATE VIEW.
3. Un CTE ne peut être spécifié que dans une instruction CREATE TABLE AS SELECT (CTAS).
4. Un CTE ne peut être spécifié que dans une instruction CREATE REMOTE TABLE AS SELECT (CRTAS).
5. Un CTE ne peut être spécifié que dans une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS).