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)

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 ?

    Reply
    • 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

      Reply
  • How we can avoid cursors ?

    Reply
  • 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.

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

      Reply
  • 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.

    Reply
    • 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).

      Reply
  • 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!

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

    Pinal,

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

    Thank you for such a great geek info..!

    Reply
  • 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

    Reply
    • just repeat the column again in select query

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

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

      Reply
  • 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

    Reply
  • 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 ?

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

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

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

    PurchaseTable
    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.

    Reply
  • Why doesn’t this work:
    with Base as
    (select
    SHIP_QT
    ,LOCATION_ID
    ,SHP_TO_CUST_ID
    ,SLD_TO_CUST_ID
    ,CARR_ID
    ,COMPANY_CODE_ID
    ,SHIP_TYPE_ID
    ,SLS_ORD_ID
    ,SLS_ORD_LINE_NUM
    ,ALLOC_NOT_SHIP_QT
    ,SHIP_DT_DISCREP_FLAG
    from DELIVERY_LINE)

    Reply
  • 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?

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

    Reply
  • पटेल चंद्रसेन
    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
    AS
    (
    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.

    Reply
  • 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

    Reply
  • 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?

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

    Reply

Leave a Reply

Menu