Is data copied when selecting into a temp table?

Question

Hi Guys,

I am wondering, when selecting rows and inserting them into a temp table, is the data actually copied or just referenced?

For example:

SELECT * INTO #Temp FROM SomeTable

If the table is very large, is this going to be a costly operation?

From my tests it seems to execute about as fast as a simple SELECT, but I'd like a better insight about how it actually works.

Cheers.

Answer 1

Temporary tables are allocated in tempdb. SQL server will generally try to keep the tempdb pages in memory, but large tables may end up written out to disk.

And yes, the data is always copied. So, for instance, if an UPDATE occurs on another connection between selecting into your temporary table and a later usage, the temporary table will contain the old value(s)