February 14, 2012 at 3:34 pm
Please correct me if I am wrong.
Here are some differences between derived tables and CTEs:
1) CTEs lend themselves to recursive approaches. Where as derived tables would generally be neutral to recursion.
2) CTES start with ";WITH" whereas derived tables don't.
3) Derived tables are subqueries. CTEs can be subqueries but not necessarily.
Here are some differences between derived tables and temp tables:
1) derived tables would be accessible only for the scope of the query. temp tables would be accessible until the tempdb was cleared of them.
2) derived tables are the result of some query whereas temp tables exist because of a # or ## indication.
Question: Can anyone add any key/obvious difference between my comparisons above?
February 14, 2012 at 5:11 pm
There are a number of articles and threads on this subject. Enter CTE in the search box up thar on the right and you'll see what I mean. I hopped to one of the first articles that came up, http://www.sqlservercentral.com/articles/T-SQL/74400/, it appeared to have a pretty lively discussion attached. Maybe give it a try, and go from there.
February 15, 2012 at 5:27 am
Rowles (2/14/2012)
Please correct me if I am wrong.Here are some differences between derived tables and CTEs:
1) CTEs lend themselves to recursive approaches. Where as derived tables would generally be neutral to recursion.
2) CTES start with ";WITH" whereas derived tables don't.
That's a construct that people use because they don't terminate their statements with a semi-colon (;). A CTE requires that termination so people have been putting it front of the WITH statement that starts the CTE, but understand the difference. It's not that you start a CTE with ;WITH, it's that the last statement needs a terminating semi-colon
3) Derived tables are subqueries. CTEs can be subqueries but not necessarily.
Nope. A CTE is just a query. Nothing else. It's radically different from temporary tables and table variables because of this
Here are some differences between derived tables and temp tables:
1) derived tables would be accessible only for the scope of the query. temp tables would be accessible until the tempdb was cleared of them.
You're comparing apples & hammers. Derived tables are a query. They're part of the query, that's it. Temporary tables come in two flavors, temp tables & table variables. Temp tables can have a global scope if they are declared as ##tablename. That table will remain accessible until the last connection referencing it clears. All other temp table types are available for the length of the session connection unless explicitly dropped.
2) derived tables are the result of some query whereas temp tables exist because of a # or ## indication.
Yeah, but you're leaving out table variables which are declared just like regular variables & use @tablename
Question: Can anyone add any key/obvious difference between my comparisons above?
Yes, and lots, lots more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2012 at 12:14 pm
Is the semi-colon ";" optional when creating a CTE?
For example, a CTE could start with ";WITH" or "WITH" right?
February 18, 2012 at 12:22 pm
Rowles (2/18/2012)
Is the semi-colon ";" optional when creating a CTE?For example, a CTE could start with ";WITH" or "WITH" right?
A CTE starts with WITH. A CTE requires that the previous statement in the batch is properly terminated with a ;
A CTE does not start with a ;, no more than you'd start an English sentence with a .
.Nobody writes like this .Sentences don't start with sentence terminators .Similarly SQL statements don't start with statement terminators
SELECT name, type FROM sys.objects;
WITH abc AS (select name, type from sys.objects)
SELECT * FROM abc;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply