SqlTutorial-1 : Lesson-5 : Class-1 : Part-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Advanced-SELECTs: Common Table Expression (CTE) - WITH Statement

CTE is named expression that holds result of Sub-select temporarily. The main advantage of CTE is light-weight and recursion; hence it is ultimate choice for navigating hierarchical data.

INDEX:

1. Overview of CTE
• Aspects of CTE
• Advantages of CTE
• Limitations of CTE
• Main Usages:
2. Working with CTE
• Syntax
• Examples:
3. Recursive CTE- Basic understanding
4. Recursive CTE-Hierarchical data processing
5. Conclusion

1. Overview of CTE

1.1. Aspects of CTE

1. A CTE is essentially a disposable view, and can be used in place of derived tables.
2. CTE is defined by WITH-Keyword, and it is best practice to begin CTE with Semi-column, since CTE is not single-statement it is batch of statement.
3. CTE can be used in SELECT, INSERT, UPDATE or DELETE statement. But SELECT is desired.
4. CTE is single-time visible to sub-sequent query only.
5. CTE can be used to define VIEWS as part of View’s SELECT query.
6. CTE can be defined as Recursive-CTE or Non-Recursive-CTE;
7. Recursive-CTE calls itself whereas Non-Recursive does not. It is best practice to use MAXRECURSION to prevent infinite loops in Recursive-CTE.
8. CTE (Recursive) mainly used for navigating hierarchical data.
9. CTE (Non-Recursive) is substitutable to derived-table (or Inline-view or Sub-Query); Since CTE and Derived Table are non-persistent and have same visibility.
10. CTE is not substitutable to Temp-Table or Table-Variable; Because CTE and TempTable are differ based on: Visibility and Persistency; CTE visibility is limited to only a single SQL statement that would very first to it; whereas Temp-Table or Table-Variable is visible to at-least current scope where they defined and Temp-Table are persistent to Db.
11. CTE can be defined in SUB-ROUTIENS such as user-defined functions, stored procedures triggers or views.
12. You can define cursor on CTE-referenced Select Query.

1.2. Advantages of CTE

Main advantage of CTE is light-weight and Recursion. The other advantages are: Readability and maintenance;

1. Light-weight: CTEs are light-weight than Temporary-tables (or Table-Variable). Since, they are non-persistent and do not write any schema information or business-data to disk. The second thing is that CTEs get cleaned up automatically by SQL Server. Therefore, CTE does not create any maintenance over-head and no performance impact.
2. Recursion: A recursive-CTE is ultimate for navigating hierarchical data;
3. Readability: CTE improves readability; this is awesome aspect that developers always look for. CTE separates data extraction from your main query. Consider a complex query containing multiple joins, data filter operations, group by etc. Using CTE, you can put all JOIN-operations in one CTE-variable; and all filters in another CTE variable then conveniently access them in your query. This will rapidly increase readability of your query. Hence, CTE is substitutable to Derived Table.

1.3 Limitations of CTE

• CTE-Select cannot include following clauses:
  • DISTINCT
  • INTO
  • ORDER BY (except when a TOP clause is specified)
  • GROUP BY
  • OPTION clause with query hints
  • FOR BROWSE
• CTE-cannot include PIVOTed or Un-PIVOTed data;

2.3. Main Usages by Examples:

1. Extracting Hierarchical data (see Recursive-CTE)
2. Self-joining Sub-query result-set (see last-example)
3. Increasing readability for complex query

2. Working with Common-Table-Expression (CTE)

2.1. Syntax:

WITH expression_name [ ( column_name[,...n] ) ]
AS ( CTE_query_definition )

Where

• expression_name = The name of the common table expression.
• column_name [ ,...n ] = The unique column names of the expression.
• CTE_query_definition = The SELECT query that defines the common table expression.

2.2. Examples:

1. Simple-CTE: Defining a table expression with WITH statement.

   ;WITH MySearch (ID, f_Name)
            AS ( SELECT ID,first_Name FROM employee)
    Select * from MySearch 

2. Simple-CTE2: Defining CTE without Column aliasing

   ;WITH MySearch
            AS ( SELECT ID,first_Name FROM employee)
    Select * from MySearch 

3. Multiple CTEs using single WITH AS statement.

   ;WITH A AS (select * from Table1 ),
          B AS (select id, name Table2),
          C AS (Select name from Table3)
   Select * from A,B,C;

4. CTE with SET-operators.

   WITH cte_Sample1
        AS (SELECT name from TABLE1 UNION ALL SELECT name from TABLE2)
   Select * from cte_Sample1;

5. Implementing self join to derived table using CTE.

   WITH Temp AS (SELECT * FROM Table1)
   SELECT t1.*, t2.*
    FROM Temp t1 INNER JOIN Temp t2 ON t1.ID = t2.ID;

3. Recursive CTE - Basic understanding

• Recursive-CTE allows you to write hierarchical queries.
• Recursive-CTE referenced by itself;
• Recursive-CTE executes in BFS(Breadth-First-Search) order. For DEF(Depth-First-Search) order make anchor(outer) query ORDER BY a path;
• Recursive-CTE works as cursor in first iteration AnchorQuery executed and in sub-sequent iteration RecursiveQuery executed.

3.1. Syntax- Recursive-CTE

;WITH cte_name ( Column_name [,...n] )
AS (
       Select Query                       - - Anchor member is defined
       UNION ALL 
       Select Query referencing cte_name   - - Recursive member
)
Outer_Query [OPTION (MAXRECURSION N) ]

Where:

1. Anchor Select Query is executed only once and it holds result of recursion. But Recursive Select query executed multiple times repeatedly.
2. The FROM-Clause of Recursive-Select must refer CTE at most once.
3. The Anchor-Select and Recursive-Select should combined together by UNION-ALL
4. The no. of and type of columns in Anchor-Select and Recursive-Select must be same.
5. Use MAXRECURSION to prevent infinite loop in Recursive-CTE 

3.2. Examples

1. Simple-Recursive-CTE with single anchor query

   WITH MyCTE(x)
   as
   (
           Select x = 'hello'
           UNION ALL
           Select x + 'a' from MyCTE where len(x) < 10
   )
   select x from MyCTE
   order by x;
   
   OUTPUT :
   hello
   helloa
   helloaa
   helloaaa
   helloaaaa
   helloaaaaa
   

2. Simple Recursive-CTE: Multiple Anchor Queries

   with MyCTE(x)
   as
   (
   select x = 'hello'
   union all
   select x = 'goodbye'
   union all
   select x + 'a' from MyCTE
   where len(x) < 10
   )
   select x from MyCTE
   order by len(x), x
   
   OUTPUT :
   hello
   helloa
   goodbye
   helloaa
   goodbyea
   helloaaa
   goodbyeaa
   helloaaaa
   goodbyeaaa
   helloaaaaa

3. Multiple-Recrusive Queries in CTE

   WITH MyCTE(x)
   As
   (   Select x = 'hello'
       UNION ALL
       Select x + 'a' from MyCTE where len(x) < 10
       UNION All Select x + 'b' from MyCTE where len(x) < 10
    )
   Select x
   From MyCTE
   Order by len(x), x
   
   OUTPUT
   hello
   helloa
   hellob
   helloaa
   helloab
   helloba
   hellobb
   helloaaa
   helloaab
   helloaba
   helloabb
   hellobaa
   hellobab
   hellobba
   hellobbb
   helloaaaa
   ….
   63 rows of output.
   
   

4. Hirarchical Data Processing using Recursive-CTE

Let us consider a self-reference Table: Persons

4.1: Providing Hierarchical Context to Table

WITH cte_Person (Id, Name, Designation, Manager, [Level] , [Root] )
AS
(
      SELECT Id, Name, Designation, Manager, 0, Id
      FROM Person
      WHERE Manager IS NULL

      UNION ALL

      SELECT p.Id, p.Name, p.Designation, p.Manager, [Level] + 1, [Root]
      FROM Person p
      INNER JOIN cte_Person cte_p ON p.Manager = cte_p.Id
)
SELECT * FROM cte_Person;

Where [Level] and [Root] are derived columns not avialable in Table and evaluated by Recrusive-CTE;

Pictorial representation of above Hierarchical data

4.2: Querying Over hierarhical data

1. Find all sub-ordinates of ITManager-A directly or indirectly (i.e., C D G I J K L M N O P)

   WITH cte_Person AS (………do as above ………….)
   SELECT Name FROM cte_Person
   WHERE ([Root] = N'1') AND (Manager IS NOT NULL)

2. Find all sub-ordinates of FinanceManager(under E i.e. H)

   WITH cte_Person AS (………do as above ………….)
   SELECT Name FROM cte_Person
   WHERE ([Root] = N'2') AND (Manager IS NOT NULL)

3. Find all persons working under Asst.ITManager(Under G => I J K L M N O P )

   WITH cte_Person AS (………do as above ………….)
   SELECT Name FROM cte_Person  
   WHERE ([Root] = N'1') AND ([Level] > 2)

For the sake of siplicity;

1. We omit CTE definition in examples; Just copy it from above example and apply or you can define CTE in separate View and access in your query.
2. In WHERE-CLAUSE we use constant value as [Root]=1, instead use sub-query to get root value;

5. Conclusion

CTE is a new feature introduced in SQL Server 2005. It allows you to hold the result-set temporarily for immediate Sql Query. CTE is replicable with Derived table or inline-view. The beauty of CTE is that it is non-persistent and auto-disposable object; it does not write any schema information or user data to disk. The other awesome advantage of CTE is that recursion; you can use recursive-CTE to navigate hierarchical data reliability. In my opinion, CTE is desirable when

1. You need to hold result set temporarily for a single query only.
2. Your query is large and contains multiple joins.
3. You need to navigate the hierarchical data;
4. You need to Self-Join the Sub-query data;