SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Common Table Expressions in SQL Server 2005


Common Table Expressions in SQL Server 2005

Author
Message
Eralper
Eralper
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 466

You can use variables in CTEs.

Below there is a sample you can widely meet in Yukon T-SQL enhancements in SQL Server 2005 books.

Note that you should use ";" after any sql statement just running before CTE (or With keyword)

Declare @Id as int
Set @Id = 13;
With SampleCTE (Id, Unit, ReportstoId, TypeId)
As
(
Select Id, Unit, ReportstoId, TypeId From CompanyUsers Where Id = @Id
Union All
Select CompanyUsers.Id, CompanyUsers.Unit, CompanyUsers.ReportstoId, CompanyUsers.TypeId
From CompanyUsers
Inner Join SampleCTE On SampleCTE.Id = CompanyUsers.ReportstoId
)
Select TypeName, Unit From SampleCTE
Inner Join Types On Types.Id = SampleCTE.TypeId
Go

Eralper

htpp://www.kodyaz.com



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Patrick Emmons-205367
Patrick Emmons-205367
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
I don't mean to be dense but what is the difference between CTEs and temporary tables?
Eralper
Eralper
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 466

Temporary tables are kept until you drop them or the session is closed. And you can use them repeatedly within the session.

But CTE is only used by the Select, Insert, Update or Delete statement that comes after the CTE.

You can see the temporary table created under the tempdb user tables section by using SQL Query Analyzer.



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
ewilson10
ewilson10
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 22
Hello. Would it be correct to say that in views CTEs give most of the flexability of temporary tables while temporary tables still have a place in scripts since they can be "forward-referenced"?



Everett Wilson
ewilson10@yahoo.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116459 Visits: 41430
Guess I didn't know that... how do you forward reference a temp table?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eralper
Eralper
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 466

Hi,

I don't know the term "forward-referenced", sorry.

Today a new article of Srinivas is published under the link,

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

This article summarizes the recursive usage of common table expressions very well with a few good examples.

I think if we compare CTE and temp tables, temp tables are more flexable. But if do not need the table repeatedly in your codes, you can choose CTE.

Eralper

http://www.kodyaz.com



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
ewilson10
ewilson10
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 22
Hm, well I took it to mean the data in the table can be modified after it's creation. From the article:

Note that by defining multiple CTEs, you can incrementally build on the earlier CTEs or define new results that are then used later on. Note however, that you cannot create a CTE that uses forward-reference (a CTE that is yet to be defined).



Everett Wilson
ewilson10@yahoo.com
Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 445

If I repeat the same derived table 2 or 3 times in my SQL select statement, the execution plan looks like it executes the same select code several times. Do CTEs make the execution plan more efficient in such cases or is it merely a (very) nice shorthand?





David Lu
David Lu
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 135

Srinivas Sampath:

Very nice job with almost no extra words in explain the CTE.

Some thoughts:

1. It looks like CTE could replace table variable in most of the case, is this right?

2. How long the live of CTE will be lasted? If is created in procA and it at the end called procB, could the same CTE be used in procB as a ## global temp table?

Want to see more articles from you!

thanks

David





Eralper
Eralper
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 466

Hi David,

As far as I know you can only use a CTE just after you define it.

Eralper

http://www.kodyaz.com



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search