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:
- SQL SERVER – Simple Example of Recursive CTE
- SQL Server – Multiple CTE in One SELECT Statement Query
- SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
Read world use case:
- SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
- SQL SERVER – 2005 – 2008 – Delete Duplicate Rows
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)
30 Comments. Leave new
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 ?
1 Use temp table
2 Set based approach with single query
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).
Hi Pinal,
Thank you so much for contributing to T-SQL Tuesday and for sharing the links!
Pinal,
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”
Hello. How many RDBMSs support CTE or the conepts like CTE with the different name?
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.
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)
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
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.
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
Shifts are AM,PM and NT(each shift 8hours)
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