May 20, 2011 at 5:28 am
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?
May 20, 2011 at 5:31 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?
I want to be first to say DEPENDS!
They are dynamic objects that in some cases are exactly like subqueries, sometimes like a view and sometimes like a temporary heap.
If the data fits in memory and is not forced to spool in tempdb it will be in memory, otherwise it will hit tempdb.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 10:54 am
mister.magoo (5/20/2011)
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?
I want to be first to say DEPENDS!
They are dynamic objects that in some cases are exactly like subqueries, sometimes like a view and sometimes like a temporary heap.
If the data fits in memory and is not forced to spool in tempdb it will be in memory, otherwise it will hit tempdb.
Thanks. So if enough memory is available it will stay there or else in tempdb? Is there any article which describes in detail about architecture of CTE?
May 20, 2011 at 12:12 pm
There's a good overview of CTEs here: http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
Another: http://msdn.microsoft.com/en-us/library/ms190766.aspx
Start with those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 2:58 pm
Function, procedure, view definitions etc are stored in the database where they are created. This definition is stored on disk, guaranteed.
A CTE declared inside a stored procedure is therefore stored on disk.
May 20, 2011 at 4:31 pm
Nils Gustav Stråbø (5/20/2011)
Function, procedure, view definitions etc are stored in the database where they are created. This definition is stored on disk, guaranteed.A CTE declared inside a stored procedure is therefore stored on disk.
Well, sure yes - I hadn't read it that way - that the question was about where the definition is stored - exactly right if that is the question!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 11:44 pm
I'm not sure is that's what OP asks, but since he/she use the word "declare", then that was how interpreted it.
If it is CTE execution he means, then it is executed as any other T-SQL statements.
May 21, 2011 at 7:14 pm
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?
May 21, 2011 at 8:03 pm
sqldba_icon (5/21/2011)
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?
At proc creation time or proc / statement execution time?
May 21, 2011 at 8:25 pm
Ninja's_RGR'us (5/21/2011)
sqldba_icon (5/21/2011)
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?At proc creation time or proc / statement execution time?
If you can provide info for both that would be great.
May 21, 2011 at 8:48 pm
sqldba_icon (5/21/2011)
Ninja's_RGR'us (5/21/2011)
sqldba_icon (5/21/2011)
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?At proc creation time or proc / statement execution time?
If you can provide info for both that would be great.
I'm not 100% sure about those internals.
The def of the proc is definitely saved in the system tables.
For the CTE at exec time I guess it's the same thing as a derived table, but this is where I hit the limits how my knowledge in the internals so I preffer not to say anything.
May 22, 2011 at 3:00 am
sqldba_icon (5/21/2011)
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?
I'm not sure I understand your question. When you say "where is the CTE stored", do you mean the definition (the T-SQL text), or are you talking about the execution plan, or perhaps the data cache? Please provide some more details.
May 22, 2011 at 8:06 am
Nils Gustav Stråbø (5/22/2011)
sqldba_icon (5/21/2011)
In my case a CTE is using inside a proc, now where is this CTE stored? I thought memory? Isn't it?I'm not sure I understand your question. When you say "where is the CTE stored", do you mean the definition (the T-SQL text), or are you talking about the execution plan, or perhaps the data cache? Please provide some more details.
When a CTE is created where is it stored during creation time and run time
May 22, 2011 at 9:23 am
Repeating yourself is not adding clarity.
Where is what stored? The definition? It's part of the procedure definition, so in the system tables. There's no separate storage, no more than a query in a procedure is stored elsewhere.
The contents? Typically they're not, a CTE is a named subquery, little more.
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
May 22, 2011 at 9:49 am
GilaMonster (5/22/2011)
Repeating yourself is not adding clarity.Where is what stored? The definition? It's part of the procedure definition, so in the system tables. There's no separate storage, no more than a query in a procedure is stored elsewhere.
The contents? Typically they're not, a CTE is a named subquery, little more.
More to the point, why do you need to know that? What problem are you trying to solve?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply