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 |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-11-16 : 04:44:23
|
| Hi,What is the difference between CTE and table variables.I read that CTE is used so that the tables do not get persisted in the database. Do the table variables have the same efficiency? i.e. not persisted in the database.Can you throw some light over here if you understand the two concepts clearly please?Thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-16 : 11:45:31
|
BOL says:quote:
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
a derived table isn't a table variable. it's a result set.table variables are persisted in memory. only if the table variable grows too much then it gets stored in tempdb.i'd think it's the same for CTE's.Also CTE's can be use in views unlike table variables.does that answer your question?Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-16 : 11:55:17
|
| Another important note is that CTEs are defined within a single query and only available within that statement, whereas a table variable can be populated and reused in multiple statements by a single connection. Therefore neither is persisted permanently, but a table variable does stay in memory for longer than a single statement. Whether tempdb ever actually gets used for either of them depends on the size of the data, SQL Server will put some of the data in tempdb for a CTE or for a table variable, only if it becomes too large to fit into memory (that's all internal, you don't have any control over it). |
|
|
|
|
|
|
CTE vs Table Variables