非再帰共通テーブル式には、次のガイドラインが適用されます。再帰共通テーブル式に適用されるガイドラインについては、後述の「再帰共通テーブル式の定義および使用に関するガイドライン」を参照してください。

• CTE の後には、その CTE 列の一部または全部を参照する単一の SELECT、INSERT、UPDATE、MERGE、または DELETE ステートメントを指定する必要があります。CTE は、CREATE VIEW ステートメントの中で、ビューの SELECT ステートメントの定義の一部として指定することもできます。
  
• 非再帰 CTE では、複数の CTE クエリを定義できます。これらのクエリ定義は、UNION ALL、UNION、EXCEPT、または INTERSECT のいずれかの set 演算子で連結する必要があります。
  
• CTE は、自分自身および同一の WITH 句内で先に定義された CTE を参照できます。前方参照は許可されません。
  
• 1 つの CTE の中で複数の WITH 句を指定することはできません。たとえば、CTE_query_definition にサブクエリが含まれる場合、そのサブクエリに、別の CTE を定義している WITH 句を入れ子の状態で含めることはできません。
  
• 次の句は、CTE_query_definition の中で使用できません。
  
  • COMPUTE または COMPUTE BY
    
  • ORDER BY (TOP 句が指定されている場合は除く)
    
  • INTO
    
  • クエリ ヒントを含む OPTION 句
    
  • FOR XML
    
  • FOR BROWSE
    
• バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。
  
• CTE を参照するクエリは、カーソル定義に使用できます。
  
• リモート サーバー上のテーブルは、CTE 内で参照できます。
  
• CTE を実行するときには、クエリ内のビューを参照するヒントと同様に、CTE を参照するヒントと基になるテーブルに CTE がアクセスした際に発見されたその他のヒントとの間で、競合が発生する可能性があります。この競合が発生すると、クエリはエラーを返します。詳細については、「ビューの解決」を参照してください。
  
• CTE が UPDATE ステートメントの対象である場合、ステートメント内の CTE に対するすべての参照を一致させる必要があります。たとえば、FROM 句で CTE に別名を割り当てた場合、CTE に対するすべての参照で別名を使用する必要があります。CTE へのあいまいな参照は、予期しない結合動作やクエリ結果につながる場合があります。詳細については、「UPDATE (Transact-SQL)」を参照してください。
  

再帰共通テーブル式の定義には、次のガイドラインが適用されます。

• 再帰 CTE の定義には、少なくとも 2 つの CTE クエリ定義を含める必要があります。1 つはアンカー メンバで、もう 1 つは再帰メンバです。アンカー メンバと再帰メンバは複数定義できます。ただし、すべてのアンカー メンバの定義は、最初の再帰メンバの定義よりも前に記述する必要があります。CTE 自体を参照しない CTE クエリ定義はすべてアンカー メンバとなります。
  
• アンカー メンバは、UNION ALL、UNION、INTERSECT、または EXCEPT のいずれかの set 演算子で連結する必要があります。UNION ALL は、最後のアンカー メンバと最初の再帰メンバを連結する場合、および複数の再帰メンバを連結する場合に使用できる唯一の set 演算子です。
  
• アンカー メンバの列数と再帰メンバの列数は、同じである必要があります。
  
• 再帰メンバの列のデータ型は、アンカー メンバの対応する列のデータ型と同じである必要があります。
  
• 再帰メンバの FROM 句は、CTE の expression_name を一度だけ参照します。
  
• 再帰メンバの CTE_query_definition では、次のアイテムは許可されません。
  
  • SELECT DISTINCT
    
  • GROUP BY
    
  • HAVING
    
  • スカラ集計
    
  • TOP
    
  • LEFT、RIGHT、OUTER JOIN (INNER JOIN は使用できます)
    
  • サブクエリ
    
  • CTE_query_definition 内の、CTE に対する再帰参照に適用されるヒント
    

再帰共通テーブル式の使用には、次のガイドラインが適用されます。

• 再帰 CTE に含まれる SELECT ステートメントが返す列で NULL 値が許容されるかどうかにかかわらず、再帰 CTE が返すすべての列で NULL 値が許可されます。
  
• 再帰 CTE が適切に構成されていない場合、無限ループが発生する可能性があります。たとえば、再帰メンバのクエリ定義が親列と子列に対して同じ値を返す場合、無限ループが生成されます。無限ループを防ぐには、MAXRECURSION ヒントを使用したり、INSERT、UPDATE、MERGE、DELETE、または SELECT ステートメントの OPTION 句に 0 ～ 32,767 の値を指定したりすることにより、特定のステートメントに許可される再帰レベルの数を制限します。これにより、無限ループの原因となったコードの問題が解決されるまで、ステートメントの実行を制御できます。サーバー全体での既定値は 100 です。0 を指定した場合、制限は適用されません。MAXRECURSION の値は 1 つのステートメントに 1 つだけ指定できます。詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。
  
• 再帰共通テーブル式を含むビューを使用してデータを更新することはできません。
  
• CTE を使用するクエリにカーソルを定義できます。CTE はカーソルの結果セットを定義する select_statement の引数です。再帰 CTE では、高速順方向専用および静的 (スナップショット) カーソルのみ使用できます。他の種類のカーソルを再帰 CTE で指定した場合、カーソルの種類は静的に変換されます。
  
• リモート サーバー上のテーブルは、CTE 内で参照できます。CTE の再帰メンバがリモート サーバーを参照する場合、各リモート テーブルごとにスプールが作成されます。そのため、ローカルからそのテーブルに繰り返しアクセスできます。CTE クエリの場合、Index Spool/Lazy Spool がクエリ プランに表示され、追加の WITH STACK 述語を持ちます。これは、適切な再帰を確認する方法の 1 つです。
  
• SQL Server 2008 では、CTE の再帰部分に分析関数および集計関数を使用することはできません。
  

次の例は、各マネージャに直接レポートする Adventure Works Cycles の従業員数を示しています。

次の例は、マネージャにレポートする従業員の平均数を示しています。

次の例は、SalesOrderHeader テーブルに記録された、各販売員ごとの販売注文数の合計と最新の販売注文日を示しています。ステートメントの実行中、CTE は 2 回参照されます。1 回は、選択された販売員の列を返すためであり、もう 1 回は、販売員のマネージャについて同様の詳細を取得するためです。販売員とマネージャのデータは 1 行で返されます。

次の例は、マネージャおよびマネージャにレポートする従業員の階層リストを示しています。

次の例は、マネージャおよびマネージャにレポートする従業員を示しています。返されるレベルの数は 2 つに制限されます。

次の例は、例 C にマネージャと従業員の名前および各自の役職を追加したものです。各レベルをインデントすることにより、マネージャおよび従業員の階層をさらに強調しています。

MAXRECURSION を使用すると、不適切に作成された再帰 CTE による無限ループの発生を防ぐことができます。次の例では、無限ループを意図的に作成し、MAXRECURSION ヒントを使用して再帰レベルの数を 2 に制限しています。

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

コードのエラーが訂正されると、MAXRECURSION は不要になります。次の例は、訂正されたコードを示しています。

次の例は、ProductAssemblyID = 800 の自転車を組み立てるのに必要な製品アセンブリとコンポーネントの階層を示しています。

次の例は、ManagerID 12 に直接または間接的にレポートしたすべての従業員の 25% に対して VacationHours の値を更新します。共通テーブル式は、ManagerID 12 に直接レポートした従業員やその従業員にレポートした従業員などを含む、従業員の階層リストを返します。共通テーブル式が返した行のみが変更されます。

次の例では、複数のアンカー メンバと再帰メンバを使用して、指定された個人のすべての先祖を返します。テーブルが 1 つ作成され、値が挿入されます。このテーブルは、再帰 CTE が返す家系図になります。