The following guidelines apply to using a recursive common table expression:Īll columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.Īn incorrectly composed recursive CTE may cause an infinite loop. LEFT, RIGHT, OUTER JOIN ( INNER JOIN is allowed)Ī hint applied to a recursive reference to a CTE inside a CTE_query_definition. See Breaking Changes to Database Engine Features in SQL Server 2016.) PIVOT (When the database compatibility level is 110 or higher. The following items aren't allowed in the CTE_query_definition of a recursive member: The FROM clause of a recursive member must refer only one time to the CTE expression_name. The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member. The number of columns in the anchor and recursive members must be the same. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members. All CTE query definitions are anchor members unless they reference the CTE itself.Īnchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. Multiple anchor members and recursive members can be defined however, all anchor member query definitions must be put before the first recursive member definition. The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. The following guidelines apply to defining a recursive common table expression: Guidelines for defining and using recursive common table expressions
When this occurs, the query returns an error. When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. Tables on remote servers can be referenced in the CTE. When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.Ī query referencing a CTE can be used to define a cursor. ORDER BY (except when a TOP clause is specified) The following clauses can't be used in the CTE_query_definition:
For example, if a CTE_query_definition contains a subquery, that subquery can't contain a nested WITH clause that defines another CTE. Specifying more than one WITH clause in a CTE isn't allowed. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.Ī CTE can reference itself and previously defined CTEs in the same WITH clause. Multiple CTE query definitions can be defined in a nonrecursive CTE. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view. For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.Ī CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. The following guidelines apply to nonrecursive common table expressions. Guidelines for creating and using common table expressions If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT. For more information, see the Remarks section and CREATE VIEW (Transact-SQL). The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE can't define another CTE. Specifies a SELECT statement whose result set populates the common table expression. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
The number of column names specified must match the number of columns in the result set of the CTE_query_definition. Duplicate names within a single CTE definition aren't allowed. Specifies a column name in the common table expression. Any reference to expression_name in the query uses the common table expression and not the base object. expression_name must be different from the name of any other common table expression defined in the same WITH clause, but expression_name can be the same as the name of a base table or view. Arguments expression_nameĪ valid identifier for the common table expression. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
0 kommentar(er)
