Limitations of Nonrecursive CTEs

Query definitions in a CTE cannot contain these clauses: Compute, Compute By, Into, For Xml, For Browse, or an Option clause with query hints.

Query definitions in a CTE can contain an Order By clause only if it contains a Top clause.

Query definitions in a CTE cannot contain query hints that will contradict hints in the query.

If there are multiple query definitions in a CTE, they must be linked using one of the following set operators: Union All, Union, Intersect, or Except.

You can define multiple CTEs one after the other (by putting With clauses one after the other), but you cannot nest With clauses (that is, a query definition cannot contain a With clause).

A CTE can reference only itself or a previously defined CTE, not CTEs that are defined later.

No comments: