Common Table Expression (CTE)

Les CTE (common Table Expression) peuvent être vu comme des results set temporaires.
Les CTE sont plus facile à utiliser que les tables temporaires et permettent une utilisation récursive (voir article Recursive Queries using CTE sur MSDN).
La durée de vie d'une CTE est limitée à l'exécution de la requête.
Pour plus d'information, voir l'article Using Common Table Expressions sur MSDN.

Les CTE deviennent utiles lorsque l'on doit appliquer une where clause sur un champ du query dont la valeur est non deterministe.
A titre d'exemple, un query dont le champs txt varchar(80) est calculé via une fonction utilisant un curseur sql (cela peut arriver si l'on doit faire des manipulations complexes sur les données pour composer le texte).
Si l'on désire n'afficher que lignes ayant plus d'un caractère, un select standard ne peut pas convenir.

Exemple:

En déclarant la fonction suivante (utilisée dans l'exemple ci-après):

Create function dbo.ChangeText( @txtIn varchar(50) )
returns Varchar(50) 
AS
BEGIN
  Return @TxtIn + '~Test~'
END

Il n'est pas possible d'exécuté la requête suivante:

select NameFr, dbo.ChangeText( NameFr ) as txt
FROM Apb.COUNTRY
where LEN( txt ) > 1

Erreur Sql:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'txt'.

Par contre, l'usage d'un Common Table Expression d'atteindre l'objectif en stockant temporairement le dataset dans cte_Names.
Par la suite, le contenu de cte_Names est utilisé pour atteindre le résultat souhaité (à l'aide d'une seconde requête sql).

with cte_Names ( NameFr, txt )
AS (
   select NameFr, dbo.ChangeText( NameFr )
   FROM Apb.COUNTRY)
  
select NameFr, txt
FROM cte_Names
where LEN( txt ) > 1

Plus d'information

• Using Common Table Expressions
• Recursive Queries using CTE 
• Voir aussi "Derived Table"