Common table Expressions (CTE)

Here are the two formats for assigning column aliases in a CTE: inline and external formats.

Inline format:

1  withC as2 (3   SELECT  Year(OrderDate) asOrderYear, CustID4    fromsales.orders5 )6 SELECTOrderYear,COUNT(DISTINCTCustID asnumcusts7  fromC8 GROUP  byOrderYear;

External format:

1  withC (OrderYear, CustID) as2 (3   SELECT  Year(OrderDate), CustID4    fromsales.orders5 )6 SELECTOrderYear,COUNT(DISTINCTCustID asnumcusts7  fromC8 GROUP  byOrderYear;

Defining multiple CTE only requires separating them with commas in the same with clause. Each CTE can refer to all the CTE defined before it, while an external query can reference all the CTE, for example:

1  withC1 as2 (3   SELECT  Year(OrderDate) asOrderYear, CustID4    fromsales.orders5 ),6C2 as7 (8   SELECTOrderYear,COUNT(DISTINCTCustID asnumcusts9    fromC1Ten   GROUP  byOrderYear One ) A SELECTOrderYear, Numcusts -  fromC2 - WHERENumcusts>  -;

The CTE also enables recursive querying of data. The following code demonstrates how to use a recursive CTE to return information about an employee (Don Funk, employee ID 2) and all of its levels (direct or indirect):

1  withEmpscte as2 (3   SELECTEmpid, Mgrid, FirstName, LastName4    fromHR. Employees5   WHEREEmpid= 26   7   UNION  All8   9   SELECTc.empid, C.mgrid, C.firstname, C.lastnameTen    fromEmpscte asP One     JOINHR. Employees asC A        onC.mgrid=P.empid - ) - SELECTEmpid, Mgrid, FirstName, LastName the  fromEMPSCTE;

For security reasons, SQL Server restricts the number of recursive members that can be invoked by default to 100 times, and when the number of recursive members calls exceeds this value, the code terminates as a result of a recursive failure. To modify the default maximum recursion count, you can specify the option (maxrecursion N) hint (hint) at the end of the external query, where the range of n is an integer between 0 and 32767. If you want to remove this limit, you can set Maxrecursion to 0.

Common table Expressions (CTE)