March 11, 2011 at 12:42 pm
I have had success in avoiding the explicit creation of temp tables to store intermediate results by aliasing a SELECT statement and using it as a table in a DML statement. My question is, how does SQL Server handle this behavior internally? Is this aliased statement stored in the buffer cache, or is it stored in tempdb as some kind of internal object, or as a temp table, or is there another behavior that I am not thinking of? Thanks in advance!
March 11, 2011 at 2:19 pm
If you look at the execution plan, it will tell you what it's doing with it. You can also check "set statistics io on" and see data movement in it (if it does materialize it into tempdb) that way.
What it does depends on a few things. If there's a lot of data, it will usually dump it into a worktable, which is essentially an implicit temp table. If it's only a small amount of data, it will just hold it in memory. That's WAY oversimplified, but it's essentially the 100-mile overview.
- 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply