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

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 (http://blog.SQLAuthority.com)

About these ads

27 thoughts on “SQL SERVER – Common Table Expression (CTE) and Few Observation

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

  2. 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).

  3. Pinal,

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

    Thank you for such a great geek info..!

  4. Pingback: SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

      • Hi
        Try This

        CREATE TABLE #test
        (
        job varchar(100),
        dept1 int,
        dept2 int,
        dept3 int
        )

        INSERT INTO #test VALUES(‘postman’, 100, 200, 300)

        SELECT a.job,a.dept1,a.dept2,a.dept3,A1.[SUM]
        FROM #test a
        INNER JOIN
        (
        SELECT b.dept1+b.dept2+b.dept3 AS [SUM] ,b.job
        FROM #test b
        ) A1 ON a.job = A1.job

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

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

  8. 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 ?

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

  10. 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)

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s