Temporary ViewsCommon Table Expressions


Temporary Views—Common Table Expressions

If there are temporary stored procedures and temporary tables, are there also temporary views? Well, yes and no. Yes, there are objects that behave like views and that are temporary, but no, Microsoft has decided to call them Common table expressions (CTEs), rather than temporary views.

You can use CTEs to simplify your queries and wrap part of a complicated query in a CTE and reference it by name.

A CTE is defined using a With clause that is placed just before a SQL statement (Select, Insert, Update, and Delete). The scope of the CTE is limited to the SQL statement and consists of two parts. The With clause defines the name of the CTE and the names of the columns in its result set. It contains one or more query definitions in brackets:

      WITH CompOrg(ParentID, CompOrgCount) AS      (          SELECT ParentOrgUnitID, COUNT(*)          FROM dbo.OrgUnit          WHERE ParentOrgUnitID IS NOT NULL          GROUP BY ParentOrgUnitID      )      SELECT OrgUnit.OrgUnit, CompOrgCount      FROM CompOrg p      inner join dbo.OrgUnit OrgUnit      on p.ParentId = OrgUnit.OrgUnitId      ORDER BY OrgUnit.OrgUnit 

The CompOrg CTE returns a list of composite organizations (organizations that have children) and the count of their children. The CTE is referenced by a following query.

Note 

The With clause of the CTE must he placed after the semicolon (;) that marks the end of the previous Transact-SQL statement (except when the previous statement is Create View). In earlier versions of SQL Server, semicolons were allowed hut they were not a required way to delimit individual Transact-SQL statements. It was enough to put white space between statements. This is the first structure in SQL Server that requires a semicolon.

Limitations of Nonrecursive CTEs

Naturally, CTEs have some limitations:

  • 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.

Recursive CTEs

One very cool feature is that a CTE can call itself recursively. This method allows you to manage hierarchies easily. A recursive CTE must contain at least two types of query definitions. The first type of query definition is called the anchor member and it can contain only reference(s) to base table(s). The second type is called the recursive member and it must contain a reference to the CTE itself. Anchor and recursive members must be linked together using the Union All set operator.

      WITH SubOrg(ParentOrgUnitID, OrgUnitID, OrgLevel) AS      (          -- anchor member (for first iteration)          SELECT ParentOrgUnitID, OrgUnitID, 0 AS OrgLevel          FROM dbo.OrgUnit          WHERE ParentOrgUnitID IS NULL          UNION ALL          -- recursive member (for later iterations)          SELECT o.ParentOrgUnitID, o.OrgUnitID, p.OrgLevel + 1          FROM dbo.OrgUnit O               INNER JOIN SubOrg p               ON o.ParentOrgUnitID = p.OrgUnitID      )      SELECT s.OrgUnitID, o.OrgUnit, s.ParentOrgUnitID, s.OrgLevel      FROM SubOrg s      INNER JOIN dbo.OrgUnit o      ON o.OrgUnitId = s.OrgUnitId      ORDER BY s.OrgLevel 

A mistake during the design of the CTE could lead to an infinite loop. Such a mistake typically occurs when the recursive member returns the same values for both parent and child columns. By default, the SQL Server engine will stop processing the CTE after 100 iterations:

      Msg 530, Level 16, State 1, Line 1      The statement terminated. The maximum recursion 100 has been exhausted before      statement completion. 

You can limit the number of iterations using the MaxRecursion option:

      WITH SubOrg(ParentOrgUnitID, OrgUnitID, OrgLevel) AS      (          SELECT ParentOrgUnitID, OrgUnitID, 0 AS OrgLevel          FROM dbo.OrgUnit          WHERE ParentOrgUnitID IS NULL          UNION ALL          SELECT o.ParentOrgUnitID, o.OrgUnitID, p.OrgLevel + 1          FROM dbo.OrgUnit o             INNER JOIN SubOrg p             ON o.ParentOrgUnitID = p.OrgUnitID      )      SELECT s.OrgUnitID, o.OrgUnit, s.ParentOrgUnitID, s.OrgLevel      FROM SubOrg s      inner join dbo.OrgUnit o      on o.OrgUnitId = s.OrgUnitId      order by s.OrgLevel      OPTION (MAXRECURSION 10); 

If you do not want to limit the number of iterations, set the MaxRecursion option to 0.

Limitations of Recursive CTEs

Recursive CTEs have a different set of rules from nonrecursive CTEs:

  • There can be more than one anchor member and there can be more than one recursive member.

  • All anchor members must be positioned before recursive members.

  • You can link anchor members using the Union All, Union, Intersect, or Except set operators.

  • The recursive members must be linked using the Union All operator.

  • A set of anchor members must be linked with a set of recursive members using the Union All operator.

  • The number and data type of the columns in all query definition members must match.

  • A recursive member must have one (and only one) reference to its CTE.

  • Recursive members cannot contain Select Distinct, Group By, Having, scalar aggregations, Top, subqueries, or hints on CTE references.

  • Only Inner Join is allowed to link tables and CTEs inside a recursive member.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net