Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - As part of performance tuning exercise our developer always suggested to use SET based operations

  • As part of performance tuning exercise our developer always suggested to use SET based operations rather than using procedural languages. One of the suggestions given for substitute for hierarchical data structure was to use of CTE. What are CTEs inside SQL Server, what are its advantages and how can you rewrite a subquery using CTE?

    Posted on 01-26-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

4  Answers  

Subscribe to Notifications
  • Score
    6

    Common Table Expressions (CTE)

    CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. This not only simplifies most of the complex and impossible queries in T-SQL, but also provides one medium to deal with recursive queries. Moreover, I can say it is mainly for recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.

    The Common Table Expression (CTE) is called as a temporary named RESULT SET, within the scope of an executing statement that can be used within a SELECT, INSERT, UPDATE, or DELETE or even in a CREATE VIEW or MERGE statement.

    CTE Syntax:

    [ WITH <common_table_expression_name>]

    [ ( column_name [ ,n ] ) ]

    AS

    ( CTE_query_definition ),…n

    • < common_table_expression_name> : name of the CTE, used as a identifier. This should be different from any other CTE defined within the same “WITH” clause. The expression name can be same as the name of the base table or view (any reference to the expression name will use the CTE and not the base table).
    • [ ( column_name [ ,n ] ) ]: Represents the column names for the defined CTE (Optional if there is no duplicity in column names in the result set of the CTE_query_definition). And if the column names are defined with the CTE, then the number of columns should match with the inner result set’s columns.
    • ( CTE_query_definition ): This specifies a SELECT statement whose result set populates the common table expression. A CTE cannot define another CTE inside it and the SELECT statement must meet the same criteria as for creating a view.

    More than one CTEs can be created within one “WITH” statement, but scope of all those CTEs will be limited to the immediate t-sql statement after “WITH” (described below in detail).

    Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.

    Microsoft offers the following four advantages of CTEs:

    • Create a recursive query.
    • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
    • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
    • Reference the resulting table multiple times in the same statement.

    Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

    Simple CTE Example: To get the employee information from the ‘employee’ table.

    WITH emp_CTE as (

                select EmpName, empEmailId, salary

                from dbo.employee)

     select * from emp_CTE;

     This is just to show how CTEs are working. Let’s take another example, where we want information from employee along with departmental information and then want to do some operations on it.

    WITH emp_CTE as (

                select EmpName, empEmailId, salary, DeptName, DeptStatus

                from dbo.employee A

                inner join

                dbo.Department D on A.DeptID = D.DeptID )

      select * from emp_CTE where DeptStatus = 'Active';

    This is yet another simple query, where one CTE named “emp_CTE” is defined to keep information about employees along with departmental information.

    Some more CTEs examples:

    WITH   CTE1(CL11,CL22)  as   (select 1 as CL1, 3 as CL2)

            ,CTE2(CL3,cl5,cl6)    as    (select 1,5,7)

    select * from CTE1 inner join CTE2 on CL11 = CL3

    Here we defined two CTEs (i.e. CTE1 and CTE2) within the same “WITH” statement, and both these CTEs are used in the immediate t-sql statement.

    CTEs with Union ALL

    WITH CTE_union(CL1,CL2) AS    (

                SELECT 1 AS CL1, 3 AS CL2

                UNION ALL

                SELECT 5 as CL11,7 as CL22)

    Select * from CTE_union

    CTEs are very useful for on the fly IDENTITY columns:

    SQL Server supports two types of Common Table Expressions (CTEs):

    • Recursive CTEs: Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc.
    • Non- Recursive CTEs: These are the other complex queries that can be simplified with the help of CTEs like using cursors, getting sequence numbers, filtrations, etc.

    Recursive CTEs: The Recursive CTEs must have two types of query definition members:

    • Recursive Member: If the query definition references the CTE itself, then it is called Recursive member. This is defined at the last i.e. after defining all the anchor members.
    • Anchor Member: All other CTE query definitions without reference of CTE are Anchor Members. All anchor member query definitions must be put before the first recursive member definition.

    The following are the restrictions to Recursive CTEs:

    • In Recursive CTEs, it’s not possible to have multiple query definitions (i.e. multiple CTEs [, separated]) within a single “WITH” statement.
    • All anchor members supports UNION ALL, UNION, INTERSECT, or EXCEPT for combining the anchor definition. But only UNION ALL is supported for combining the last anchor member and the first recursive member and even for multiple recursive members.
    • The total number of columns and the data types of those columns must match for the anchor member and the recursive member. (Note: data type should be same for recursive member and the corresponding anchor member)
    • CTE expression_name can be referenced only once in the FROM clause of a recursive member.

    Recursive Common Table Expression : These are very useful in case of hierarchical data, because the CTE Query continues to execute till it reaches its lower granular level.

    Basically any recursive CTE consists of three elements:

    1. First invocation of the CTE: In first invocation, all the members of the CTE will be executed i.e. both the anchor members and the recursive members. And as we have CTE referenced in the recursive members, it will automatically invoke the CTE for further iterations and thus called as recursive mode or recursive invocation. In this case Anchor member’s result set will be considered as the referenced data for the CTE. As in the above figure, in the first invocation, r0 is the value for the Anchor query and hence become the data for the CTE when referenced from the Recursive query.
    2. Recursive invocation of the CTE: In all recursive invocation of the CTE, only recursive members will be executed and the anchor members will remain idle. And the data for the CTE referenced will be the result set built from the last call of the CTE. Elaborated briefly below.
    3. Recursive Termination Condition: Well practically, we don’t have any control on the termination of the Recursive CTEs. I mean it is implicit; it will stop itself when no rows are returned from the previous invocation. Hence, the control condition can be applied indirectly i.e. the query’s result set can be limited with the help of filter conditions.

    In order to avoid error we can use MAXRECURSION hint in the OPTION clause of the outer query where the CTE is referenced. We can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767.

    CTE Example for SubQuery : In below example shows select with subquery

    SELECT * FROM  (

            SELECT A.Address, E.Name, E.Age From Address A

            Inner join Employee E on E.EID = A.EID) T

    WHERE T.Age > 50

    ORDER BY T.NAME

    Then how we can convert it into cte expression query. Following is the answer for this.

    With T1(Address, Name, Age)  --Column names for Temporary table

    AS

    (

       SELECT A.Address, E.Name, E.Age from Address A

       INNER JOIN EMP E ON E.EID = A.EID

    ),

    T2(Name, Desig)

    AS

    (

      SELECT NAME, DESIG FROM Designation)

      SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table

    WHERE T1.Age > 50 AND T1.Name = T2.Name

    ORDER BY T1.NAME

    Replied on Jan 26 2012 1:28AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    9

    Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.CTE is the best as it act as a normal view only.

    CTE is just shorthand for a query or subquery.
    CTE sometimes call them in-line views, can make things look clearer.

    The cte isn't stored anywhere as its scope is transient (the length of the statement in which it occurs) However it usage can result in tempdb usage if the generated resultset(s) are large (or complex) enough Tempdb is used to store CTE values( intermediate query result) when it uses spool operator. Spooling will happen mainly if the CTE is recursive:

    A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

    Syntax

    [ WITH <common_table_expression> [ ,...n ] ]
    
    <common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
        AS
        ( CTE_query_definition )
    

    More than one CTEs can be created within one “WITH” statement, but scope of all those CTEs will be limited to the immediate t-sql statement after “WITH”

    http://msdn.microsoft.com/en-us/library/ms175972.aspx

    SQL Server supports two types of Common Table Expressions (CTEs):

    Recursive CTEs: Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc.
    Non- Recursive CTEs: These are the other complex queries that can be simplified with the help of CTEs like using cursors, getting sequence numbers, filtrations, etc.

    Guidelines for Creating and Using Common Table Expressions

    • A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
    • Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
    • A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
    • Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTEquerydefinition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

    • The following clauses cannot be used in the CTEquerydefinition:

      • COMPUTE or COMPUTE BY
      • ORDER BY (except when a TOP clause is specified)
      • INTO
      • OPTION clause with query hints
      • FOR XML
      • FOR BROWSE
    • When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

    • A query referencing a CTE can be used to define a cursor.
    • Tables on remote servers can be referenced in the CTE.
    • 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. When this occurs, the query returns an error. For more information, see View Resolution.
    • When a CTE is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Ambiguous CTE references may produce unexpected join behavior and unintended query results. For more information, see UPDATE (Transact-SQL).

    Common Table Expression (CTE) simple example

    WITH [CTE Example] (Id, Title)
    AS
    (
      SELECT EmployeeID, Title FROM HumanResources.Employee
     )
     SELECT * FROM [CTE Example]
    

    Common Table Expression (CTE) as SubSelect

    WITH [CTE Stock] (ProductID, [Total Quantity])
    As
    (
     Select ProductID, Sum(Quantity)
     From Production.ProductInventory
     Group By ProductID
    )
    Select
     p.ProductNumber,
     p.Name,
     cte.[Total Quantity]
    From Production.Product p
    Inner Join [CTE Stock] cte
     On cte.ProductID = p.ProductID
    Order By p.ProductNumber;
    

    Multiple CTE in SQL Server 2008

    WITH [CTE Example] (Id, Title)
    AS
    (
     SELECT EmployeeID, Title FROM HumanResources.Employee
    ), CTE (Num, EmployeeName)
    AS
    (
     SELECT
      CTE.Id,
      ISNULL(FirstName,'') + ISNULL(MiddleName,'') + ISNULL(LastName,'')
     FROM [CTE Example] CTE
     INNER JOIN Person.Contact C
      ON CTE.Id = C.ContactID
    )
    select * from CTE
    

    The following guidelines apply to defining a recursive common table expression:

    • The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitionsare anchor members unless they reference the CTE itself.
    • Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
    • The number of columns in the anchor and recursive members must be the same.
    • 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 FROM clause of a recursive member must refer only one time to the CTE expression_name.
    • The following items are not allowed in the CTEquerydefinition of a recursive member:

      • SELECT DISTINCT
      • GROUP BY
      • HAVING
      • Scalar aggregation
      • TOP
      • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
      • Subqueries
      • A hint applied to a recursive reference to a CTE inside a CTEquerydefinition.

    To prevent an infinite loop in recursive CTE, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement.

    Syntax In general form a recursive CTE has the following syntax:

    WITH cte_alias (column_aliases)  
    AS  
    ( 
    cte_query_definition   --initialization 
    UNION ALL 
    cte_query_definition2 --recursive execution 
    )  
    SELECT * FROM cte_alias
    

    Recursive Query Example

    USE AdventureWorks
    GO
    WITH Emp_CTE AS (
    SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
    FROM HumanResources.Employee e
    INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
    )
    SELECT *
    FROM Emp_CTE OPTION (MAXRECURSION 5)
    GO
    

    Notes

    • CTE are not replacement of the Temp Table or Temp Variable Table
    • Always begin CTE with semi-comma
    • The scope of the CTE is limited to very first SELECT statement
    • Usually the execution plan of the CTE like sub-query but there are cases when it can be different as well

    Advantages of using CTE

    • Create a recursive query, like hierarchy tree using Common Table Expressions.
    • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
    • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
    • Reference the resulting table multiple times in the same statement.
    • Using CTE improves the readability and makes maintenance of complex queries easy.
    • The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
    • CTE can be defined in functions, stored procedures, triggers or even views.
    • After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
    • If you are calling CTE twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster.
    • CTEs are very useful for on the fly IDENTITY columns:

    How can you rewrite a subquery using CTE?
    We can rewrite sub-queries using the Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

    Rewrite a subquery using CTE Example 1

    --Using Subquery
    USE NORTHWIND
    GO
    SELECT C.CustomerID, C.CompanyName,
    (SELECT COUNT(*) FROM Orders O WHERE O.CustomerID = C.CustomerID)
    FROM   Customers C
    GO
    
    --Using Common Table Expression    
    USE NORTHWIND
    GO
    WITH OrderCnt (CustomerID, Cnt) AS (
    SELECT CustomerID, COUNT(*)
    FROM   Orders
    GROUP  BY CustomerID
    )
    SELECT C.CustomerID, C.CompanyName, coalesce(OC.Cnt, 0)
    FROM   Customers C
    LEFT   JOIN OrderCnt OC ON C.CustomerID = OC.CustomerID93
    

    Rewrite a subquery using CTE Example 2

    USE AdventureWorks
    GO
    --Using Subquery
    SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
    FROM HumanResources.Department ed
    INNER JOIN (SELECT EmployeeID,DepartmentID,ShiftID
    FROM HumanResources.EmployeeDepartmentHistory) AS ecte 
    ON ed.DepartmentID = ecte.DepartmentID
    GO
    
    --Using Common Table Expression
    WITH EmployeeDepartment_CTE AS (
    SELECT EmployeeID,DepartmentID,ShiftID
    FROM HumanResources.EmployeeDepartmentHistory
    )
    SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
    FROM HumanResources.Department ed
    INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID
    

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions

    http://msdn.microsoft.com/en-us/library/ms175972.aspx

    http://pushpendra-sql-ssis.blogspot.com/2011/02/common-table-expressionscte.html

    http://www.sqllion.com/2010/08/common-table-expressions-cte

    http://blog.sqlauthority.com/2011/07/16/sql-server-interview-questions-and-answers-frequently-asked-questions-day-16-of-31

    Replied on Jan 26 2012 10:35AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    What are CTEs inside SQL Server

    A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

    what are its advantages

         A CTE can be used to: 
         Create a recursive query. 
         Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
         Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
         Reference the resulting table multiple times in the same statement.
         Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. 
         CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
         Create a recursive query, like hierarchy tree using Common Table Expressions. 
         Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata. 
         Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. 
         Reference the resulting table multiple times in the same statement. 
         Using CTE improves the readability and makes maintenance of complex queries easy. 
         The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated. 
         CTE can be defined in functions, stored procedures, triggers or even views. 
         After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data. 
         If you are calling CTE twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster. 
         CTEs are very useful for on the fly IDENTITY columns:
    

    Structure of CTE

    WITH expression_name [ ( column_name [,...n] ) ] 
    
    AS 
    
    ( CTE_query_definition ) 
    
    The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
    
    The statement to run the CTE is:
    
    SELECT  
    
    FROM expression_name; 
    

    how can you rewrite a subquery using CTE?

    Co-related subquery Vs CTE

    SELECT C.CustomerID, C.CompanyName,
    (SELECT COUNT(*) FROM Orders O WHERE O.CustomerID = C.CustomerID)
    FROM   Customers C
    
    
    WITH OrderCnt (CustomerID, Cnt) AS (
    SELECT CustomerID, COUNT(*)
    FROM   Orders
    GROUP  BY CustomerID
    )
    SELECT C.CustomerID, C.CompanyName, coalesce(OC.Cnt, 0)
    FROM   Customers C
    LEFT   JOIN OrderCnt OC ON C.CustomerID = OC.CustomerID
    

    Sub Query Vs CTE

    SELECT * FROM  (
            SELECT A.Address, E.Name, E.Age From Address A
            Inner join Employee E on E.EID = A.EID) T
    WHERE T.Age > 50
    ORDER BY T.NAME
    
    With T1(Address, Name, Age)  --Column names for Temporary table
    AS
    (
       SELECT A.Address, E.Name, E.Age from Address A
       INNER JOIN EMP E ON E.EID = A.EID
    ),
    T2(Name, Desig)
    AS
    (
      SELECT NAME, DESIG FROM Designation)
      SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
    WHERE T1.Age > 50 AND T1.Name = T2.Name
    ORDER BY T1.NAME
    

    how innovatively have you used CTEs?

    1. The very best place I used CTE is for Server side pagination as below:
    CREATE PROC GetPagedEmployees (@NumbersOnPage INT=25,@PageNumb INT = 1)
    AS BEGIN
    
    WITH AllEmployees AS
    (SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
    [FirstName],[MiddleName],[LastName],[EmailAddress] FROM [Person].[Contact])
    
    SELECT [FirstName],[MiddleName],[LastName],[EmailAddress] 
    FROM AllEmployees WHERE RowID BETWEEN 
    ((@PageNumb - 1) * @NumbersOnPage) + 1 AND @PageNumb * NumbersOnPage
    ORDER BY RowID
    
    END
    
    1. To find out the tree level for data(Its pseudo code only, my apologies not giving the exact view of my system.)
    DECLARE @t TABLE ( 
      id     INT, 
      parent INT) 
    
    INSERT @t 
           (id, 
            parent) 
    VALUES (1, 
            NULL), 
           (2, 
            1), 
           (3, 
            2), 
           (4, 
            3), 
           (5, 
            NULL), 
           (6, 
            5); 
    
    WITH cte 
         AS (SELECT id, 
                    parent, 
                    CAST(RIGHT(Replicate('0', 12) + CONVERT(VARCHAR(12), id), 12) AS 
                         VARCHAR 
                         ( 
                         MAX)) 
                    PATH 
             FROM   @t 
             WHERE  parent IS NULL 
             UNION ALL 
             SELECT child.id, 
                    child.parent, 
                    parent.PATH + RIGHT(Replicate('0', 12) + 
                                  CONVERT(VARCHAR(12), child.id), 12) AS 
                    PATH 
             FROM   @t child 
                    JOIN cte parent 
                      ON parent.id = child.parent) 
    SELECT * 
    FROM   cte 
    ORDER  BY PATH  
    

    Can there be recursive CTEs? Yes.Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc. To prevent an infinite loop in recursive CTE, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement.

    USE AdventureWorks
    GO
    WITH Emp_CTE AS (
    SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
    FROM HumanResources.Employee e
    INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
    )
    SELECT *
    FROM Emp_CTE OPTION (MAXRECURSION 5)
    GO
    
    Replied on Jan 27 2012 5:37AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

    A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

    A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps.

    Structure of a Recursive CTE

    The structure of the recursive CTE in Transact-SQL is similar to recursive routines in other programming languages. Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows.

    A recursive CTE consists of three elements:

    • Invocation of the routine.

    The first invocation of the recursive CTE consists of one or more CTEquerydefinitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.

    CTEquerydefinitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

    • Recursive invocation of the routine.

    The recursive invocation includes one or more CTEquerydefinitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

    • Termination check.

    The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

    Pseudocode and Semantics

    The recursive CTE structure must contain at least one anchor member and one recursive member. The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.

    WITH ctename ( columnname [,...n] )

    AS

    (

    CTEquerydefinition –- Anchor member is defined.

    UNION ALL

    CTEquerydefinition –- Recursive member is defined referencing cte_name.

    )

    -- Statement using the CTE

    SELECT *

    FROM cte_name

    The semantics of the recursive execution is as follows:

    1.Split the CTE expression into anchor and recursive members.

    2.Run the anchor member(s) creating the first invocation or base result set (T0).

    3.Run the recursive member(s) with Ti as an input and Ti+1 as an output.

    4.Repeat step 3 until an empty set is returned.

    5.Return the result set. This is a UNION ALL of T0 to Tn.

    -- Create an Employee table.
    CREATE TABLE dbo.MyEmployees
    (
        EmployeeID smallint NOT NULL,
        FirstName nvarchar(30)  NOT NULL,
        LastName  nvarchar(40) NOT NULL,
        Title nvarchar(50) NOT NULL,
        DeptID smallint NOT NULL,
        ManagerID int NULL,
     CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
    );
    -- Populate the table with values.
    INSERT INTO dbo.MyEmployees VALUES 
     (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
    ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
    ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
    ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
    ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
    ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
    ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
    ,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
    ,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
    
    
    USE AdventureWorks2008R2;
    GO
    WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
    AS
    (
    -- Anchor member definition
        SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
            0 AS Level
        FROM dbo.MyEmployees AS e
        INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
            ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
        WHERE ManagerID IS NULL
        UNION ALL
    -- Recursive member definition
        SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
            Level + 1
        FROM dbo.MyEmployees AS e
        INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
            ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
        INNER JOIN DirectReports AS d
            ON e.ManagerID = d.EmployeeID
    )
    -- Statement that executes the CTE
    SELECT ManagerID, EmployeeID, Title, DeptID, Level
    FROM DirectReports
    INNER JOIN HumanResources.Department AS dp
        ON DirectReports.DeptID = dp.DepartmentID
    WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
    GO
    

    Example Code Walkthrough

    1.The recursive CTE, DirectReports, defines one anchor member and one recursive member.

    2.The anchor member returns the base result set T0. This is the highest ranking employee in the company; that is, an employee who does not report to a manager.

    Here is the result set returned by the anchor member:

    ManagerID      EmployeeID           Title                         Level
    NULL                1          Chief Executive Officer                0
    

    3.The recursive member returns the direct subordinate(s) of the employee in the anchor member result set. This is achieved by a join operation between the Employee table and the DirectReports CTE. It is this reference to the CTE itself that establishes the recursive invocation. Based on the employee in the CTE DirectReports as input (Ti), the join (MyEmployees.ManagerID = DirectReports.EmployeeID) returns as output (Ti+1), the employees who have (Ti) as their manager. Therefore, the first iteration of the recursive member returns this result set:

    ManagerID        EmployeeID       Title                         Level
    1                   273        Vice President of Sales               1
    

    4.The recursive member is activated repeatedly. The second iteration of the recursive member uses the single-row result set in step 3 (containing EmployeeID 273) as the input value, and returns this result set:

    ManagerID    EmployeeID          Title                                Level
    273                 16         Marketing Manager                         2
    273                 274        North American Sales Manager              2
    273                 285        Pacific Sales Manager                     2
    

    The third iteration of the recursive member uses the result set above as the input value, and returns this result set:

    ManagerID EmployeeID               Title                         Level
    16                 23         Marketing Specialist                   3
    274                275        Sales Representative                   3
    274                276        Sales Representative                   3
    285                286        Sales Representative                   3
    

    5.The final result set returned by the running query is the union of all result sets generated by the anchor and recursive members.

    Here is the complete result set returned by the example:

    ManagerID    EmployeeID              Title                                Level
    NULL              1          Chief Executive Officer                         0
    1                 273        Vice President of Sales                         1
    273               16         Marketing Manager                               2
    273              274        North American Sales Manager                     2
    273              285        Pacific Sales Manager                            2
    16                 23         Marketing Specialist                           3
    274              275        Sales Representative                             3
    274              276        Sales Representative                             3
    285             286        Sales Representative                              3
    

    Advantages

    As discussed above, the use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies

    • CTEs make queries more simpler and easy to read.
    • Can be used to create a recursive query.
    • Allow grouping by a column which might be derived from a scalar subset
    • Can reference itself multiple times
    Replied on Jan 31 2012 11:19PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.