Quand utiliser Common Table Expression (CTE)

Un exemple, si vous avez besoin de referencer / joindre le même set de données plusieurs fois, vous pouvez le faire en définissant un CTE. Par conséquent, il peut s'agir d'une forme de réutilisation du code.

Un exemple d'auto-referencement est la récursivité: requêtes récursives à l'aide de CTE

Pour des définitions Microsoft passionnantes extraites de la documentation en ligne:

Un CTE peut être utilisé pour:

• Créez une requête récursive. Pour plus d'informations, consultez Requêtes récursives à l'aide d'expressions de table communes.

• Substituer une vue lorsque l'utilisation générale d'une vue n'est pas requirejse c'est-à-dire que vous n'avez pas besoin de stocker la définition dans les métadonnées.

• Activez le regroupement par une colonne dérivée d'une sous-sélection scalaire ou une fonction non déterministe ou disposant d'un access externe.

• Référencez plusieurs fois la table résultante dans la même instruction.

Je les utilise pour décomposer des requêtes complexes, en particulier des jointures complexes et des sous-requêtes. Je trouve que je les utilise de plus en plus comme des «pseudo-vues» pour m'aider à comprendre l'intention de la requête.

Mon seul reproche à leur sujet est qu'ils ne peuvent pas être réutilisés. Par exemple, je peux avoir un proc stocké avec deux instructions de mise à jour qui pourraient utiliser le même CTE. Mais la 'scope' du CTE est la première requête seulement.

Le problème est que les «exemples simples» n'ont probablement pas vraiment besoin de CTE!

Toujours, très pratique.

Il y a deux raisons que je vois d'utiliser cte.

Pour utiliser une valeur calculée dans la clause where. Cela me semble un peu plus propre qu'une table dérivée.

Supposons qu'il existe deux tables – Questions et Réponses réunies par Questions.ID = Réponses.Question_Id (et l'ID de quiz)

 WITH CTE AS ( Select Question_Text, (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers FROM Questions Q ) SELECT * FROM CTE WHERE Number_Of_Answers > 0 

Voici un autre exemple où je veux get une list de questions et réponses. Je veux que les Réponses soient regroupées avec les questions dans les résultats.

 WITH cte AS ( SELECT [Quiz_ID] ,[ID] AS Question_Id ,null AS Answer_Id ,[Question_Text] ,null AS Answer ,1 AS Is_Question FROM [Questions] UNION ALL SELECT Q.[Quiz_ID] ,[Question_ID] ,A.[ID] AS Answer_Id ,Q.Question_Text ,[Answer] ,0 AS Is_Question FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id ) SELECT Quiz_Id, Question_Id, Is_Question, (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name FROM cte GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question order by Quiz_Id, Question_Id, Is_Question Desc, Name 

L'un des scénarios que j'ai trouvés utiles pour utiliser CTE est lorsque vous voulez get des lignes DISTINCT de données basées sur une ou plusieurs colonnes mais returnner toutes les colonnes de la table. Avec une requête standard, vous devez d'abord vider les valeurs distinctes dans une table temporaire, puis essayer de les relier à la table d'origine pour récupérer le rest des colonnes ou écrire une requête de partition extrêmement complexe qui peut renvoyer les résultats dans un passage mais, selon toute vraisemblance, il sera illisible et causera des problèmes de performance.

Mais en utilisant CTE (comme répondu par Tim Schmelter sur Sélectionner la première instance d'un logging )

 WITH CTE AS( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) SELECT * FROM CTE WHERE RN = 1 

Comme vous pouvez le voir, c'est beaucoup plus facile à lire et à maintenir. Et en comparaison avec d'autres requêtes, est beaucoup mieux à la performance.

Peut-être est-il plus significatif de penser à un CTE comme substitut à une vue utilisée pour une seule requête. Mais ne nécessite pas la surcharge, les métadonnées ou la persistance d'une vue formelle. Très utile lorsque vous avez besoin de:

• Créez une requête récursive.
• Utilisez le résultat du CTE plus d'une fois dans votre requête.
• Promouvoir la clarté de votre requête en réduisant les gros morceaux de sous-requêtes identiques.
• Activer le regroupement par une colonne dérivée dans le jeu de résultats du CTE

Voici un exemple de copyr-coller pour jouer avec:

 WITH [cte_example] AS ( SELECT 1 AS [myNum], 'a num' as [label] UNION ALL SELECT [myNum]+1,[label] FROM [cte_example] WHERE [myNum] <= 10 ) SELECT * FROM [cte_example] UNION SELECT SUM([myNum]), 'sum_all' FROM [cte_example] UNION SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1 UNION SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0; 

Prendre plaisir

Aujourd'hui, nous allons en apprendre davantage sur l'expression de table commune qui est une nouvelle fonctionnalité introduite dans SQL Server 2005 et disponible dans les versions ultérieures.

Expression de la table commune: – L'expression de table commune peut être définie comme un set de résultats temporaire ou, en d'autres termes, comme un substitut de vues dans SQL Server. L'expression de table commune n'est valide que dans le lot d'instructions où elle a été définie et ne peut pas être utilisée dans d'autres sessions.

Syntaxe de déclaration de CTE (expression de table commune): –

 with [Name of CTE] as ( Body of common table expression ) 

Prenons un exemple: –

 CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL) insert into Employee(Name) values('Neeraj') insert into Employee(Name) values('dheeraj') insert into Employee(Name) values('shayam') insert into Employee(Name) values('vikas') insert into Employee(Name) values('raj') CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100)) insert into dept values(10,'IT') insert into dept values(15,'Finance') insert into dept values(20,'Admin') insert into dept values(25,'HR') insert into dept values(10,'Payroll') 

J'ai créé deux tables employee et Dept et inséré 5 lignes dans chaque table. Maintenant, je voudrais joindre ces tables et créer un jeu de résultats temporaire pour l'utiliser plus loin.

 With CTE_Example(EID,Name,DeptName) as ( select Employee.EID,Name,DeptName from Employee inner join DEPT on Employee.EID =DEPT.EID ) select * from CTE_Example 

Permet de prendre chaque ligne de la déclaration un par un et de comprendre.

Pour définir CTE, nous écrivons la clause "with", puis nous donnons un nom à l'expression de la table, ici j'ai donné le nom "CTE_Example"

Ensuite, nous écrivons "As" et entourons notre code entre deux crochets (—), nous pouvons joindre plusieurs tables dans les parenthèses ci-jointes.

Dans la dernière ligne, j'ai utilisé "Select * from CTE_Example", nous référons l'expression de la table commune dans la dernière ligne de code, donc nous pouvons dire que c'est comme une vue, où nous définissons et utilisons la vue en un seul batch et CTE n'est pas stocké dans la database en tant qu'object permanent. Mais il se comporte comme une vue. nous pouvons effectuer des suppressions et des mises à jour sur CTE, ce qui aura un impact direct sur la table référencée utilisée dans CTE. Prenons un exemple pour comprendre ce fait.

 With CTE_Example(EID,DeptName) as ( select EID,DeptName from DEPT ) delete from CTE_Example where EID=10 and DeptName ='Payroll' 

Dans l'instruction ci-dessus, nous supprimons une ligne de CTE_Example et supprime datatables de la table référencée "DEPT" utilisée dans le CTE.

J'utilise généralement CTE dans la création de vues où je ne peux pas utiliser de tables temporaires locales ou globales.

  ;with cte as ( Select Department, Max(salary) as MaxSalary from test group by department ) select t.* from test t join cte c on c.department=t.department where t.salary=c.MaxSalary; 

essaye ça