TechCodeview

Nous utiliserons les expressions de table communes ou CTE de SQL Server pour faciliter les jointures et les sous-requêtes complexes. Il permet également d'interroger des données hiérarchiques, telles qu'une hiérarchie organisationnelle. Cet article donne un aperçu complet du CTE, des types de CTE, des avantages, des inconvénients et de la manière de les utiliser dans SQL Server.

Qu’est-ce que le CTE dans SQL Server ?

Un CTE (Common Table Expression) est un ensemble de résultats unique qui n'existe que pendant la durée de la requête. . Il nous permet de faire référence à des données dans la portée d'exécution d'une seule instruction SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ou MERGE. Il est temporaire car son résultat ne peut être stocké nulle part et sera perdu dès que l'exécution d'une requête est terminée. Il est d'abord venu avec la version SQL Server 2005. Un administrateur de base de données a toujours préféré utiliser CTE comme alternative à une sous-requête/vue. Ils suivent la norme ANSI SQL 99 et sont conformes à SQL.

Syntaxe CTE dans SQL Server

La syntaxe CTE comprend un nom CTE, une liste de colonnes facultative et une instruction/requête qui définit l'expression de table commune (CTE). Après avoir défini le CTE, nous pouvons l'utiliser comme vue dans une requête SELECT, INSERT, UPDATE, DELETE et MERGE.

Voici la syntaxe de base de CTE dans SQL Server :

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

Dans cette syntaxe :

• Nous avons d'abord spécifié le nom du CTE auquel il sera fait référence ultérieurement dans une requête.
• L'étape suivante consiste à créer une liste de colonnes séparées par des virgules. Cela garantit que le nombre de colonnes dans les arguments de définition CTE et le nombre de colonnes dans la requête doivent être les mêmes. Si nous n'avons pas défini les colonnes des arguments CTE, il utilisera les colonnes de requête qui définissent le CTE.
• Après cela, nous utiliserons le mot-clé AS après le nom de l'expression, puis définirons une instruction SELECT dont le jeu de résultats remplit le CTE.
• Enfin, nous utiliserons le nom CTE dans une requête telle que les instructions SELECT, INSERT, UPDATE, DELETE et MERGE.

Il convient de garder à l'esprit lors de l'écriture de la définition de la requête CTE : nous ne pouvons pas utiliser les clauses suivantes :

1. ORDER BY sauf si vous utilisez également comme clause TOP
2. DANS
3. Clause OPTION avec conseils de requête
4. POUR PARCOURIR

L'image ci-dessous est la représentation de la définition de la requête CTE.

Ici, la première partie est une expression CTE qui contient une requête SQL pouvant être exécutée indépendamment dans SQL. Et la deuxième partie est la requête qui utilise le CTE pour afficher le résultat.

Exemple

Comprenons comment fonctionne CTE dans SQL Server à l'aide de divers exemples. Ici, nous allons utiliser un tableau ' client ' pour une démonstration. Supposons que ce tableau contienne les données suivantes :

Dans cet exemple, le nom du CTE est clients_in_newyork , la sous-requête qui définit le CTE renvoie les trois colonnes nom du client, email, et État . En conséquence, le CTE customer_in_newyork renverra tous les clients qui vivent dans l'État de New York.

Après avoir défini le CTE customer_in_newyork, nous l'avons référencé dans le SÉLECTIONNER déclaration pour obtenir les détails des clients situés à New York.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

Après avoir exécuté l’instruction ci-dessus, le résultat suivant sera obtenu. Ici, nous pouvons voir que le résultat renvoie uniquement les informations sur les clients situés dans l'État de New York.

CTE multiples

Dans certains cas, nous devrons créer plusieurs requêtes CTE et les joindre pour voir les résultats. Nous pouvons utiliser le concept de plusieurs CTE dans ce scénario. Nous devons utiliser l'opérateur virgule pour créer plusieurs requêtes CTE et les fusionner en une seule instruction. L'opérateur virgule ',' doit être précédé du nom du CTE pour distinguer plusieurs CTE.

Plusieurs CTE nous aident à simplifier les requêtes complexes qui sont finalement réunies. Chaque élément complexe possédait son propre CTE, qui pouvait ensuite être référencé et joint en dehors de la clause WITH.

REMARQUE : La définition CTE multiple peut être définie à l'aide de UNION, UNION ALL, JOIN, INTERSECT ou EXCEPT.

La syntaxe ci-dessous l'explique plus clairement :

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

Exemple

Comprenons comment fonctionnent plusieurs CTE dans SQL Server. Ici, nous allons utiliser ce qui précède ' client ' table pour une démonstration.

Dans cet exemple, nous avons défini les deux noms CTE clients_in_newyork et clients_en_californie . Ensuite, l'ensemble de résultats des sous-requêtes de ces CTE remplit le CTE. Enfin, nous utiliserons les noms CTE dans une requête qui renverra tous les clients situés dans New York et État de Californie .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

État de New York et de Californie.

Pourquoi avons-nous besoin du CTE ?

À l'instar des vues de base de données et des tables dérivées, les CTE peuvent faciliter l'écriture et la gestion de requêtes complexes en les rendant plus lisibles et plus simples. Nous pouvons réaliser cette caractéristique en décomposant les requêtes complexes en blocs simples qui peuvent être réutilisés lors de la réécriture de la requête.

Certains de ses cas d’utilisation sont indiqués ci-dessous :

• C'est utile lorsque nous devons définir une table dérivée plusieurs fois au sein d'une seule requête.
• C'est utile lorsque nous devons créer une alternative à une vue dans la base de données.
• Ceci est utile lorsque nous devons effectuer le même calcul plusieurs fois sur plusieurs composants de requête simultanément.
• C'est utile lorsque nous devons utiliser des fonctions de classement telles que ROW_NUMBER(), RANK() et NTILE().

Certains de ses avantages sont indiqués ci-dessous :

point java

• CTE facilite la maintenance du code.
• CTE augmente la lisibilité du code.
• Cela augmente les performances de la requête.
• CTE permet de mettre en œuvre facilement des requêtes récursives.

Types de CTE dans SQL Server

SQL Server divise les CTE (Common Table Expressions) en deux grandes catégories :

1. CTE récursif
2. CTE non récursif

CTE récursif

Une expression de table courante est connue sous le nom de CTE récursive qui se référence elle-même. Son concept est basé sur la récursivité, qui est définie comme ' l'application d'un processus ou d'une définition récursive à plusieurs reprises .' Lorsque nous exécutons une requête récursive, elle parcourt à plusieurs reprises un sous-ensemble de données. Elle est simplement définie comme une requête qui s'appelle elle-même. Il y a une condition de fin à un moment donné, donc elle ne s'appelle pas infiniment.

Un CTE récursif doit avoir un UNION TOUS et une deuxième définition de requête qui fait référence au CTE lui-même afin d'être récursive.

Exemple

Comprenons comment fonctionne le CTE récursif dans SQL Server. Considérez la déclaration ci-dessous, qui génère une série des cinq premiers nombres impairs :

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Ce CTE donnera le résultat suivant où nous pourrons voir la hiérarchie des données des employés :

CTE non récursif

Une expression de table courante qui ne fait pas référence à elle-même est connue sous le nom de CTE non récursif. Un CTE non récursif est simple et plus facile à comprendre car il n'utilise pas le concept de récursivité. Selon la syntaxe CTE, chaque requête CTE commencera par un ' Avec 'clause suivie du nom du CTE et de la liste des colonnes, puis AS entre parenthèses.

Inconvénients du CTE

Voici les limites de l'utilisation de CTE dans SQL Server :

• Les membres du CTE ne peuvent pas utiliser les clauses de mots clés telles que Distinct, Group By, Have, Top, Joins, etc.
• Le CTE ne peut être référencé qu'une seule fois par le membre récursif.
• Nous ne pouvons pas utiliser les variables de table et les CTE comme paramètres dans les procédures stockées.
• Nous savons déjà que le CTE pourrait être utilisé à la place d'une vue, mais un CTE ne peut pas être imbriqué, contrairement aux vues.
• Puisqu'il s'agit simplement d'un raccourci vers une requête ou une sous-requête, il ne peut pas être réutilisé dans une autre requête.
• Le nombre de colonnes dans les arguments CTE et le nombre de colonnes dans la requête doivent être identiques.