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 |
|
reganwick
Starting Member
4 Posts |
 Posted - 2007-07-16 : 17:05:53
|
| I know that like table variables, CTEs are in memory. My question is whether like table vars, CTEs also will make use of tempdb - and if so on what conditions. |
|
|
mattyblah
Starting Member
49 Posts |
Posted - 2007-07-16 : 23:12:47
|
| cte's are not in memory. sql server expands the cte at run time. |
 |
|
|
reganwick
Starting Member
4 Posts |
Posted - 2007-07-17 : 09:21:48
|
| Clarification: I am asking about the result set. Is it always just in memory or are there conditions when it is written to tempdb. If a CTE resulted in let's say 100 records, would the rest of the sproc access them via memory or (sometimes?) tempdb?Thank you |
|
|
|
mattyblah
Starting Member
49 Posts |
Posted - 2007-07-17 : 11:22:13
|
| CTEs have the characteristics of a derived table. nothing is written to tempdb, ever, i believe. if you query a cte twice, you are essentially running the underlying query twice. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-17 : 12:45:20
|
| if there is more data than sql server can put in memory (it decides that) then it will put the resultset in the temp table._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
CTEs and tempdb