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 12»»

Common Table Expressions Expand / Collapse
Author
Message
Posted Tuesday, December 8, 2009 3:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
so for example. I have the below. Is this the same basic principle as using an into #tablename and then querrying it? Are the CTE's specific to the runtime of a stored procedure or once their defined thay can be refferenced forever?

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS
(
SELECT
EmployeeID,
LastName,
FirstName,
(SELECT COUNT(1) FROM Employees e2
WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
ReportsTo
FROM Employees e
)

SELECT LastName, FirstName, NumberOfSubordinates
FROM EmployeeSubordinatesReport
Post #831093
Posted Tuesday, December 8, 2009 4:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
BaldingLoopMan (12/8/2009)
so for example. I have the below. Is this the same basic principle as using an into #tablename and then querrying it? Are the CTE's specific to the runtime of a stored procedure or once their defined thay can be refferenced forever?

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS
(
SELECT
EmployeeID,
LastName,
FirstName,
(SELECT COUNT(1) FROM Employees e2
WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
ReportsTo
FROM Employees e
)

SELECT LastName, FirstName, NumberOfSubordinates
FROM EmployeeSubordinatesReport


They can only be referenced by the immediately following SELECT/INSERT/UPDATE/DELETE statement. Think of them more as a derived table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #831095
Posted Tuesday, December 8, 2009 4:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
If you haven't already, you may also want to read this article.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #831098
Posted Tuesday, December 8, 2009 6:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
Seriously? You can only use a CTE in the statement immediately following?
When I read the article, the impression I got was that you could define a CTE at the top of something like a stored proc, then use it all the way through. Is my understanding wrong?
Post #831123
Posted Tuesday, December 8, 2009 7:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:49 PM
Points: 1,519, Visits: 4,074
niall.baird (12/8/2009)
Seriously? You can only use a CTE in the statement immediately following?
When I read the article, the impression I got was that you could define a CTE at the top of something like a stored proc, then use it all the way through. Is my understanding wrong?


Lynn Pettis(12/8/2009)

They can only be referenced by the immediately following SELECT/INSERT/UPDATE/DELETE statement.


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #831128
Posted Tuesday, December 8, 2009 7:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
I *can* read Garadin. That is why I was questioning the above statement. Seems to me that defining a #table would be more use, as you can use it in more places - unless of course, I am missing something.
Post #831129
Posted Tuesday, December 8, 2009 7:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
In SQL Server 2000, at a previous employer, I wrote several queries that used the same derived table several times in a single select statement. It was a royal pain to write, and when the derived table had to be modified it had to be modified in several places. Enter SQL Server 2005 and CTE's. Now, just before the select statement, I can define to "derived table" once and use it several times, just like a table in the select statement. If the CTE needs to be modified, one place to make the change.

CTE's are very useful when used properly. Sometimes a temporary table is a better option in a stored proc.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #831131
Posted Tuesday, December 8, 2009 7:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
That makes a bit more sense - thanks for explaining, Lynn
Post #831132
Posted Tuesday, December 8, 2009 8:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:49 PM
Points: 1,519, Visits: 4,074
niall.baird (12/8/2009)
I *can* read Garadin. That is why I was questioning the above statement. Seems to me that defining a #table would be more use, as you can use it in more places - unless of course, I am missing something.


heh, I couldn't resist.

Seriously though, don't take Lynn's word for it, run up a quick test and see for yourself.

They see a ton of usage even with their limitations. They *can* also incur less overhead than creating a temp table, depending on your usage.


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #831139
Posted Wednesday, December 9, 2009 7:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
All very useful info guys. Once again it appears the geniuses at sql server have designed a new way to do the same thing but w/ different syntax. Sure there are some minor performance gains as usual. Sorry for the disgruntled attitude. The gators lost, I just got in, and haven’t finished my coffee. Also I just ate about 2 lbs of bacon at an all u can eat breakfast buffet. Bacon is my weakness and I’m paying for it now.

Personally i develop using the #tables then convert them to @tables where needed when moving my code to prod. I like the #tables in development because i can see what is in them after the process runs and so on. It's essential to the dev process.

Perhaps i will start to integrate these CTE's going forward and be a good boy.
Post #831382
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse