How SELECT loaded into Memory - If the table is huge
Not exactly, but in effect pretty close.
Pages are read as they are needed (imagine the query thread is reading page after page). If a page isn't in cache, then it is read into cache. The cost in cache for that page would then be 1, since it has been accessed 1 time. When there is memory pressure (imagine your thread continues and reaches a point where all memory is now used), then memory pressure will trigger removal of &quot;cheap pages&quot; from cache. A page which has been read only only once (like the pages read in the beginning of your query processing) will have a cost of 1, and are likely to be targets from removal compared to other pages, which might have been read multiple times (having a cost higher than 1).
It is basically like any cache handling mechanism, in principal.