Les Expressions de Table communes ou CTE en abrégé sont utilisées dans SQL Server pour simplifier les jointures et les sous-requêtes complexes, et pour fournir un moyen d’interroger des données hiérarchiques telles qu’un organigramme. Dans cet article, nous allons vous présenter les expressions de table courantes, les deux types de CTEs et leurs utilisations. De plus, nous présenterons L’ensemble de CTE., Une fois que vous êtes familier, je vous encourage fortement à lire également ces articles:

• CTE non récursif
• Cte récursif

Introduction aux Expressions de Table communes

un CTE (Common Table Expression) est un ensemble de résultats temporaire que vous pouvez référencer dans une autre instruction SELECT, INSERT, UPDATE ou DELETE. Ils ont été introduits dans SQL Server version 2005. Ils sont conformes à SQL et font partie de la spécification ANSI SQL 99.

un CTE renvoie toujours un jeu de résultats., Ils sont utilisés pour simplifier les requêtes, par exemple, vous pouvez en utiliser une pour éliminer une table dérivée du corps de la requête principale.

Remarque: Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon Guide pour commencer à utiliser SQL Server.

Qu’est-ce qu’une expression de Table CTE ou Common dans SQL Server?

un CTE (Common Table Expression) définit un jeu de résultats temporaire que vous pouvez ensuite utiliser dans une instruction SELECT. Cela devient un moyen pratique de gérer des requêtes compliquées.,

Les Expressions de Table communes sont définies dans l’instruction à l’aide de L’opérateur WITH. Vous pouvez définir une ou plusieurs expressions de table communes de cette manière.

Ici est un exemple simple d’un CCE:

WITH Employee_CTE (EmployeeNumber, Title)AS(SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee)SELECT EmployeeNumber, TitleFROM Employee_CTE

nous allons briser ce un peu.

La partie bleue est la CTE. Notez qu’il contient une requête qui peut être exécutée seule dans SQL., Ceci est appelé la CTE définition de la requête:

SELECT NationalIDNumber, JobTitleFROM HumanResources.Employee

Lorsque vous l’exécutez, vous voyez les résultats comme:

Notez que lorsque nous définissons la CTE nous donner le résultat d’un nom, ainsi que ses colonnes. De cette façon, un CTE agit comme une vue. Le résultat et les colonnes sont nommés différemment. Cela vous permet d’encapsuler une logique de requête compliquée avec l’expression de table commune.

maintenant, en revenant au CTE, notez que L’instruction WITH., Là, vous verrez le nom et les colonnes sont définies. Ces colonnes correspondent aux colonnes renvoyées par la requête interne.

notez enfin que notre requête finale fait référence au CTE et aux colonnes définies.

du point de vue de notre requête externe, tout ce qu’elle « voit” est cette définition. Il ne se préoccupe pas de la façon dont le CTE est construit, juste de son nom et de ses colonnes.,

en tant que tel, les résultats du CTE sont:

notez les noms des colonnes, ils sont basés sur ceux définis dans le CTE.

je tiens à souligner que vous pouvez définir plus d’un CTE dans une instruction WITH. Cela peut vous aider à simplifier certaines requêtes très compliquées qui sont finalement réunies. Chaque pièce compliquée peut inclure dans leur propre CTE qui est ensuite mentionné et joint en dehors de la clause WITH.,

Voici un exemple d’utilisation de deux CTE, c’est un exemple simple, mais il montre comment deux CTE sont définis, puis utilisés dans une jointure interne

WITH PersonCTE (BusinessEntityID, FirstName, LastName)AS (SELECT Person.BusinessEntityID, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'C%'),PhoneCTE (BusinessEntityID, PhoneNumber)AS (SELECT BusinessEntityID, PhoneNumber FROM Person.PersonPhone)SELECT FirstName, LastName, PhoneNumberFROM PersonCTEINNER JOINPhoneCTEON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;

La première expression de table commune est colorée en vert, la seconde en bleu. Comme vous pouvez le voir dans L’instruction SELECT, les CTE sont joints comme s’ils étaient des tables. J’espère que vous pouvez voir que vos requêtes deviennent plus compliquées, les CTE peuvent devenir un moyen vraiment utile de séparer les opérations; par conséquent, simplifiez votre requête finale.

Pourquoi avez-vous besoin de CTE?,

Il y a plusieurs raisons pour lesquelles vous pouvez utiliser un CTE sur d’autres méthodes. Certains d’entre eux incluent:

• lisibilité – les CTE favorisent la lisibilité. Plutôt que de regrouper toute la logique de requête en une seule grande requête, créez plusieurs CTE, qui sont combinés plus tard dans l’instruction. Cela vous permet d’obtenir les morceaux de données dont vous avez besoin et de les combiner dans une sélection finale.
• remplacer une vue – vous pouvez remplacer un CTE par une vue. C’est pratique si vous n’avez pas les autorisations pour créer un objet view ou si vous ne voulez pas en créer un car il n’est utilisé que dans cette requête.,
• récursivité – utilisez les CTE pour créer des requêtes récursives, c’est-à-dire des requêtes qui peuvent s’appeler elles-mêmes. C’est pratique lorsque vous devez travailler sur des données hiérarchiques telles que des organigrammes.
• Limitations-surmontez les limitations de l’instruction SELECT, telles que le référencement lui-même (récursivité) ou l’exécution D’un groupe à l’aide de fonctions non déterministes.
• classement-chaque fois que vous souhaitez utiliser une fonction de classement telle que ROW_NUMBER(), RANK(), NTILE (), etc.

Types de CTE

Les Expressions de Table communes peuvent être placées dans deux grandes catégories: Cte récursif et CTE non récursif.,

Les CTE récursifs sont des expressions de table courantes qui se référencent elles-mêmes. La récursivité peut être un sujet assez difficile à saisir, Je ne l’ai vraiment pas compris jusqu’à ce que je prenne une classe LISP en 1986, mais j’espère pouvoir vous l’expliquer.

nous allons approfondir la récursivité dans un article séparé, mais pour l’instant, laissez-moi vous présenter la récursivité en utilisant ce diagramme:

ici vous voyez l’image des miroirs opposés. En raison de la réflexion, il devient une image dans une image.

requêtes Récursives sont comme ça.,

Lorsqu’une requête récursive est exécutée, elle s’exécute à plusieurs reprises sur un sous-ensemble des données. Une requête récursive est essentiellement une requête qui s’appelle elle-même. À un moment donné, il y a une condition de fin, donc il ne s’appelle pas indéfiniment.

d’une manière lorsque vous regardez la photo, vous pouvez l’imaginer chaque image dans une image est l’image que l’appelant lui-même. Cependant, contrairement à la” réflexion infinie  » dans les miroirs, il arrive un moment où une requête récursive rencontre la condition de fin et cesse de s’appeler.,

à ce moment-là, la récursivité commence à se dérouler, à collecter et à calculer des données à mesure qu’elle examine chaque résultat successif.

Les CTE Non récursifs, comme leur nom l’indique, n’utilisent pas la récursivité. Ils n’ont pas de référence eux-mêmes. Ils sont plus faciles à comprendre, nous les examinerons donc d’abord en détail dans le prochain article de cette série.

Conclusion

J’espère que vous avez maintenant une appréciation de ce que sont les CTE et pourquoi nous pouvons vouloir les utiliser. Dans les deux prochains articles, nous allons entrer dans beaucoup plus de détails sur CTE, et quand les utiliser.,

jusque-là, je recommanderais de revoir mes articles sur les jointures et les sous-requêtes car nous nous inspirerons de ces types de requêtes pour nos exemples.