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 (http://blog.SQLAuthority.com)
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..!
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#109-112 Common Table Expression (CTE) and Few Observation Multiple CTE in One SELECT Statement Query Delete Duplicate Rows Simple Example of Recursive CTE [...]
[...] on CTE: Simple Example of Recursive CTE Multiple CTE in One SELECT Statement Query Common Table Expression (CTE) and Few Observation Delete Duplicate Rows Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE [...]
hello sir,
my crytriea is like this
job dept1 dept2 dept3 sum
postman 100 200 300
how to do this
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
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
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 ?