Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Common Table Expressions in SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, March 2, 2005 8:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:42 AM
Points: 178, 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
Post #165047
Posted Wednesday, March 2, 2005 8:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 2:22 PM
Points: 3, Visits: 5
I don't mean to be dense but what is the difference between CTEs and temporary tables?
Post #165063
Posted Wednesday, March 2, 2005 9:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:42 AM
Points: 178, 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
Post #165072
Posted Wednesday, March 2, 2005 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 23, 2009 9:44 AM
Points: 179, 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
Post #165083
Posted Wednesday, March 2, 2005 7:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #165274
Posted Thursday, March 3, 2005 8:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:42 AM
Points: 178, 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
Post #165453
Posted Thursday, March 3, 2005 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 23, 2009 9:44 AM
Points: 179, 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
Post #165481
Posted Thursday, March 3, 2005 8:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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?




Post #165616
Posted Friday, March 4, 2005 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:10 PM
Points: 192, 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




Post #165795
Posted Monday, March 7, 2005 12:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:42 AM
Points: 178, 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
Post #165949
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse