Différence entre CTE et SubQuery ?

Question

De ce poste Comment utiliser ROW_NUMBER dans la procédure suivante ?

Il existe deux versions de réponses où l'une utilise un sub-query et l'autre utilise un CTE pour résoudre le même problème.

Alors, quel est l'avantage d'utiliser un CTE (Common Table Expression) sur une "sous-requête" (donc, plus de lisible ce que la requête fait réellement)

Le seul avantage de l'utilisation d'un CTE sur sub-select c'est que je peux en fait nom le site sub-query . Y a-t-il d'autres différences entre ces deux lorsqu'un CTE est utilisé comme un CTE simple (non récurrent) ?

Answer 1

Dans la sous-requête vs simple (non récursif) CTE, elles sont probablement très similaires. Vous devrez utiliser le profileur et le plan d'exécution réel pour repérer les différences éventuelles, et cela sera spécifique à votre configuration (nous ne pouvons donc pas vous donner la réponse complète).

En général Un CTE peut être utilisé de manière récursive, ce qui n'est pas le cas d'une sous-requête. Cela les rend particulièrement bien adaptés aux structures arborescentes.

Answer 2

Le principal avantage de la Expression commune du tableau (lorsqu'il n'est pas utilisé pour les requêtes récursives ) est l'encapsulation. Au lieu de devoir déclarer la sous-requête à chaque endroit où vous souhaitez l'utiliser, vous pouvez la définir une fois, mais y faire référence plusieurs fois.

Cependant, cela ne no signifie qu'elle n'est exécutée qu'une seule fois (conformément à les itérations précédentes de cette même réponse (merci à tous ceux qui ont fait des commentaires). La requête a certainement le potentiel d'être exécutée plusieurs fois si elle est référencée plusieurs fois ; l'optimiseur de requête prend finalement la décision quant à comment le CTE doit être interprété.

Answer 3

CTE sont les plus utiles pour la récursion :

WITH hier(cnt) AS (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @n
        )
SELECT  cnt
FROM    hier

retournera @n rangs (jusqu'à 101 ). Utile pour les calendriers, les séries de lignes fictives, etc.

Ils sont également plus lisibles (à mon avis).

En dehors de cela, CTE et subqueries sont identiques.

Answer 4

Une différence qui n'a pas été mentionnée est qu'un seul CTE peut être référencé dans les différentes parties d'une union.

Answer 5

Sauf erreur de ma part, vous pouvez nommer les CTE et les sous-requêtes tout aussi facilement.

Je pense que la principale différence est la lisibilité (je trouve le CTE plus lisible parce qu'il définit votre sous-requête en amont plutôt qu'au milieu).

Et si vous avez besoin de faire quelque chose avec la récursion, vous allez avoir un peu de mal à le faire avec une sous-requête ;)