CTE command In Sql Server

Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution of the SQL Query.
It also can reference itself. It can be referenced multiple times. The SQL Scripts of creation of all the tables and their insert data statement is given below:-

Query for creation of the database used in this article and then using it.

Create database CTE_Learning

Query to used the database created above

Use CTE_Learning

SQL Query for creation of the table Employee

Create table Employee (Employeeid int identity(1,1), EmpFname nvarchar(150), EmpLname nvarchar(150), Managerid int, regdate datetime)

SQL Query for creation of the table Project

Create table Project (Projectid int identity(1,1), Projectname nvarchar(150))

SQL Query for creation of the table Empproj

Create table Empproj (Empprojid int identity(1,1), Employeeid int, Projectid int)

Query for inserting the data in the table Employee

Insert into Employee(EmpFname, EmpLname, Managerid, regdate)
    Select ‘Samir’, ‘Sethi’, 0, getdate()
    union all
    Select ‘Vivek’, ‘Johari’, 1, getdate()
    union all
    Select ‘Avinash’, ‘Dubey’, 2, getdate()
    union all
    Select ‘Sunny’, ‘Rana’, 3, getdate()
    union all
    Select ‘Himanshu’,’Aggarwal’,3,getdate()

Query for inserting the data in the table Project 

Insert into Project (Projectname)
            Select ‘Project1’
            Union all
            Select ‘Project2’
            union all
            Select  ‘Project3’
Query for inserting the data in the table Empproj 

Insert into Empproj (Employeeid, Projectid)
               Select 1,1
              Union  all
               select 1, 2
              Union  all
               select 1,3
              Union  all
               select 2,1
              Union  all
               Select 2,2
              Union  all
                Select 3,1
              Union all
                Select 4,2
 The Common table expression has the following syntax:

With Expressionname (Column list)
AS
(
   CTE query definition
)

Here, Expressionname is the name of the CTE, Column list is the name of the unique column names which are return as the record set through the execution of the CTE query and CTE query definition is the SQL query which we are going to use in the CTE.

Suppose, we want to get the recordset which contains the details of the employees who are attacted to more than 1 projects then we can use the CTE as given below:-

With CTE_Projinfo (employeeid, total_projects) 
As
(
  select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1
)

select e.employeeid , e.empfname + ‘ ‘ + e.empLname, e.regdate from employee e inner join CTE_Projinfo
on e.employeeid = CTE_Projinfo.employeeid

Here CTE_Projinfo is the CTE expression name
        employeeid, total_projects are the names of the columns return by the execution of the CTE query

select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1

In the above example, we have used the CTE “CTE_Projinfo ” to get the details of the employees by using the inner join with the table employee.

Also we can includes multiple CTEs within a WITH Clause
For example,

With
    CTE_Projinfo (employeeid, total_projects) 
As
(
  select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1
),

CTE_Empproject (employeeid, projectname)
AS
(
  Select CTE_Projinfo.employeeid,Projectname from CTE_Projinfo  inner join  Empproj
on CTE_Projinfo.Employeeid=Empproj.employeeid inner join Project 
on Empproj.projectid=Project.projectid
)

select e.employeeid , e.empfname + ‘ ‘ + e.empLname, e.regdate,CTE_Empproject.projectname from employee e inner join CTE_Empproject 
on e.employeeid =  CTE_Empproject.employeeid 

Here we have includes two CTEs expressions  CTE_Projinfo and CTE_Empproject within a same With caluse. In this example, we have seperated the two CTEs with the help of “,”.

  
Since a CTE can reference itself also, we can use the CTE to get the hierarchy of an organization. 

For example, we can use the following CTE to get the  hierarchical data of the employees of an organisation.
      
With CTE_managerlevel (Employeeid,employeename,managerid,joblevel) as
(
  select e.employeeid , e.empfname + ‘ ‘ + e.empLname, e.Managerid,0 from employee e where e.employeeid=1
  UNION ALL
 select m.employeeid , m.empfname + ‘ ‘ + m.empLname, m.Managerid,CTE_managerlevel.joblevel+1 from employee m inner join CTE_managerlevel
 on m.Managerid=CTE_managerlevel.employeeid
)

select Employeeid,employeename,managerid,joblevel from CTE_managerlevel

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL CTE, SQL Server and tagged , . Bookmark the permalink.

31 Responses to CTE command In Sql Server

  1. Anonymous says:

    Excelleent Post,Very informative as well as easy to understand and follow.
    Keep it up.
    -Akhil

  2. --Jeff Moden says:

    I've not done a deep dive on it but this is a pretty good post. Thanks for posting it.

    You might want to also mention a couple of other things about CTEs.

    1. They work just like a sub-query in a FROM clause except, as you mentioned, they can be referenced in the FROM clause of the outer query more than once.

    2. If you do choose to reference a CTE in the outer query more than once, remember the other name for a CTE… "Inline View". Just as if you had referenced a view more than once, the entire CTE will be executed more than once which will usually and instantly make your code twice as slow. It is sometimes much more effective to store the results of a query in a TempTable and then reference the temp table more than once.

    3. Recursive CTEs (rCTE for short) have a number of great uses. They also have some uses that are better not done using an rCTE. In particular, rCTEs that count can really kill the performance of a query. It's usually much better to use a "Tally Table" or other method to cound. Please see the following URL for an article on why counting with an rCTE is so very bad.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    –Jeff Moden

    • admin says:

      Hi Jeff,
      Thanks for sharing such a invaluable knowledge. Your comments will definitely helps the reader in better understanding of CTE.

      Regards
      Vivek

  3. --Jeff Moden says:

    As a sidebar, I always take "post approvals" on such blogs as a bit of a lack of confidence and certainly as censorship. Because of that, I might not be back to this particular blog.

    –Jeff Moden

  4. admin says:

    Thanks Jeff for your valuable comments. I used "Post Approvals" so that I can block the Spam messages. But now I remove that restriction also.

  5. Anonymous says:

    nice article vivek ..thx

  6. HI Vivek,

    I am sharan patil ,Having 3.2 year exp as msbi technologies,I am Also peppering (MCTS),Can I pls help me??

  7. Pingback: Dentist in Bismarck North Dakota

  8. Pingback: rottespærre

  9. Pingback: homes for sale in Rochester MI

  10. Pingback: upgloves

  11. Pingback: เหนียวหนี้ห

  12. Pingback: http://elsalvadorla.org/blue-film/

  13. Pingback: fun 88

  14. Pingback: sen bir oğlansın

  15. Pingback: บริษัท ออล ครีเอตี้ แลนด์ จำกัด

  16. Pingback: https://www.findnctrianglehomes.com/

  17. Pingback: Website

  18. Pingback: Plumbers in West Palm Beach

  19. Pingback: Download South African Music SA

  20. Pingback: Empire Market

  21. Pingback: Tochka Market

  22. Pingback: rubiks.glove.co.il

  23. Pingback: Empire Market Exit Scam

  24. Pingback: Samsara Market

  25. Pingback: sahabat qq

  26. Pingback: saranapoker

  27. Pingback: Ignou reports

  28. Pingback: lapak qq

  29. Pingback: login sbobet888

Leave a Reply