May 23, 2011 at 4:09 am
Best bet, take a look at the execution plan. You'll see what is being done by SQL Server to retrieve the data as defined by the CTE and the query that uses it. Since a CTE is not a temporary table nothing in particular is created for it. Depending on what the CTE does, all manner of things like hash tables or work tables or spools, sorts, etc. can create temporary storage that goes to memory or tempdb. It's all down to your CTE.
"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
May 24, 2011 at 11:07 am
sqldba_icon (5/20/2011)
Can someone please tell me where is a CTE stored when declared or used inside a proc? Is it stored in memory or hard disk?
It seems to me the question is invalid - CTE's are not objects in their own rights, in the same way that Stored Procedures and Functions are objects. A CTE is a query, that is part of a batch of some sort - a Stored Procedure, a Function, etc.
Do you want to know
> where the sructure of the CTE is stored?
> where the intermediate / final results of the CTE are stored?
> where the query plan is stored?
> something else?
The question needs to be reworded (not just the same text repeated again), please.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply