SQL SERVER – Common Table Expression (CTE) and Few Observation

SQL SERVER - Common Table Expression (CTE) and Few Observation TSQL2sDay This blog post is written in response to the T-SQL Tuesday hosted by Bob Pusateri. He has picked very interesting topic which is related to APPLY clause of the T-SQL. When I read the subject, I really liked the subject. This is very new subject and it is quite a interesting choice by Bob.

Common Table Expression (CTE) are introduced in SQL Server 2005 so it is available with us from last 6 years. Over the years I have seen lots of implementation of the same as well lots of misconceptions. Earlier I had presented on this subject many places. Here is the quick note on the subject, which I used to keep with me.

Please note this note is in no particular order:

  • 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
  • Use MAXRECURSION to prevent infinite loop in recursive CTE

Here are few blog posts where I used to demonstrate regarding how to begin with CTE.

Beginning CTE:

Read world use case:

I really had great time writing this blog post as all the memories of working with CTE is brought back when it was newly introduced.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – SQL Server Management Pack Guide for System Center Operations Manager 2007
Next Post
SQL SERVER – Resource Database ID – 32767

Related Posts

30 Comments. Leave new

  • Yap Chee Chau
    May 10, 2011 10:55 am

    Hi Pinal,

    What is the main reason of always begin CTE with semi-comma ?

    • Becuase WITH is already a command used in options like backup with move, etc. So in order to differentiate, it should be preceded by a semicolon

  • How we can avoid cursors ?

  • Wilfred van Dijk
    May 10, 2011 3:17 pm

    I do not agree that CTE are not a replacement of temp table or Temp variable Table. I think it should be “CTE can be a replacement of temp table or temp variable table”.

    I’ve seen and created many examples of T-SQL code where temptables are being replaced by CTE. In fact I recommend replacing a temptable by a CTE if possible.

    • Please note that CTE can be used in a Single SELECT block only unlike temp table or table variable

  • Nice discussion on CTE’s, I have Used CTE’s in certain scenarios and worked very well. I am just curious to know the Performance Hit on using CTE’s in more elaborate situations like trying to build a Bill Of Material for parts.

    • A year and a half later but just in case it’s useful: I’m pretty certain a CTE could only improve performance as it amounts to little more than a way to alias a bit of code you’d like to repeat. In that sense the only effect it could have on the execution plan is making clear to the engine that it has to fetch something only once, not twice and that its self contained (no possibility of inline referencing a field outside its brackets).

  • Bob Pusateri
    May 10, 2011 10:03 pm

    Hi Pinal,

    Thank you so much for contributing to T-SQL Tuesday and for sharing the links!

  • anil kumar gutlapalli
    May 31, 2011 2:55 pm


    You are the most talented person in SQL i have ever seen!!!

    Thank you for such a great geek info..!

  • i have query
    i need the result like
    my table is
    id name value
    1 a 100
    2 b 200
    3 c 300

    i need the result set like

    id name value newadd
    1 a 100 100
    2 b 200 300
    3 c 300 600

    kindly let me know the query pls help me i am stuk in this

    • just repeat the column again in select query

      select MyTable.id,MyTable.name,MyTable.value,MyTable.value as newadd
      from MyTable

    • select id,name,c.value
      ,(select sum(value) from tblvalues
      where id<=c.id) 'RunningTotal'
      from tblvalues c

  • Try this Amit,

    create table #testme(
    id int,
    name char(2),
    value int

    insert into #testme
    select 1,’a’,100
    union all
    select 2,’b’,200
    union all
    select 3,’c’,300
    union all
    select 4,’d’, 400

    select t.id, t.name, t.value, SUM(t1.value) AS NewValue
    from #testme t
    inner join #testme t1 on t.id >= t1.id
    group by t.id, t.name, t.value

  • Hello Pinal,

    Which is faster as per performance (If I want to use in pagination)?
    Identity column in temp table or ROW_NUMBER in CTE ?

  • there is typo error “Read world use case” it should be “Real World use Case”

  • Avani Negandhi
    October 24, 2013 11:03 am

    Hello. How many RDBMSs support CTE or the conepts like CTE with the different name?

  • I got a table
    CutomerId, Name, Add1, Add2, Phone

    CustomerID, ProductId, DateOrder,Qty

    I would like to display reocrds in first row and all his/her purchases from purchaseTable using CTE or Select statement as follows

    CustomerID: 1
    ProductID, DateOrder, Qty
    ProductID, DateOrder, Qty
    CustomerID: 2
    ProductID, DateOrder, Qty
    ProductID, DateOrder, Qty

    Thanks and appreciated.

  • Why doesn’t this work:
    with Base as

  • Hello All,

    It fetch me a lot after going through your conversations.
    i have a question
    How can i make use of CTE in ORACLE?

  • Nice…With CTE we can very easily delete duplicate row

  • पटेल चंद्रसेन
    April 1, 2014 4:02 pm

    Hi pinal
    you mentioned that “The scope of the CTE is limited to very first SELECT statement”.

    But when I run this query ..

    WITH cte
    SELECT TOP 12 * FROM Users
    UPDATE Properties SET Status=4 WHERE Id = 566622

    SELECT * FROM cte

    then I get error “Invalid object name ‘cte’.”
    This means CTE is limited to very first ANY statement not only SELECT statement.

  • Hi Pinal,
    I Need tsql code which arrange 8 resources in 24/7 operations.5days working and 2 days off.Please help me i am new to sql server.i gone through all your sql posts so i thought your right person to help me

  • Hi pinal
    I faced one Interview Question
    When will we use CTE in SQL Server?
    Why do we need to use CTE in SQL Server?

  • order item orderquantity Onhandqty
    1 a 10 100
    b 20
    2 a 30 90
    d 10
    3 a 10 60


Leave a ReplyCancel reply

Exit mobile version