Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is the behavior of aliased SELECT statements? Expand / Collapse
Author
Message
Posted Friday, March 11, 2011 12:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:48 PM
Points: 200, Visits: 58
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!
Post #1077139
Posted Friday, March 11, 2011 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1077173
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse