Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bazalddo
Starting Member
22 Posts |
 Posted - 2011-05-25 : 05:26:30
|
| Hi,Can anyone tell me the difference between a CTE (Common Table Expression) and a temporary table.I know they are defined differently syntactically but I don't understand the difference in how they may be used.ThanksShahbaz AhdiApps DeveloperODEON/UCI Cinema |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-25 : 07:36:11
|
| If you look at the query plan often the cte will be evaluated multiple times.It is more like a table variable than a temp table.The table variable needs to be defined then populated whereas the cte can get the definition from the query - in that way it is like a select into which can only be used with a temp table not a table variable but it doesn't have the overhead of a temp table.CTE also allows recursionAlso you can define multiple ctes to use the result of previous ones which is quicker to code than other tables.seehttp://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/I use cte's a lot even when recursion is not needed as they are quicker to code and don't take up resources after the statement (hopefully). Anything that is needed for multiple statements I will use a table variable or temp table depending on size and indexing needs.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-25 : 08:38:06
|
| A CTE is little more than a named subquery or temporary view. As part of query parsing, it'll be inlined into the query.A temp table, like a table variable, is an actual table created and stored in TempDB--Gail ShawSQL Server MVP |
|
|
|
Bazalddo
Starting Member
22 Posts |
Posted - 2011-05-25 : 09:03:34
|
| Okay thanks alot :)Shahbaz AhdiApps DeveloperODEON/UCI Cinema |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-05-27 : 15:00:16
|
| Here's a couple:- CTE's are easier to change since less code is involved- CTE's can be slower since you can create indexes on them |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-05-27 : 15:09:47
|
quote:
Originally posted by denis_the_thief
Here's a couple:- CTEs are easier to change since less code is involved- CTEs can be slower since you can't create indexes on them
|
|
|
|
|
|
|
Difference between CTE and temp table