Yes and no... if a table variable fits in memory, then it does just like a derived table does... it uses memory. If it doesn't fit in memory, it will use TempDB, just like a derived table does.

Now, here's the surprise for some folks... where does a Temp Table live? If you said TempDB, you're only half right because, just like a Table Variable, if it fit's in memory, it lives in memory. Both will have an "entry" in TempDB and a derived table can appear as a "work" table in TempDB... same holds true for CTE's.

Don't take my word for how both Table Variables and Temp Tables live in either memory or TempDB depending on their size though... I've posted it several times before on this very thread... if you haven't done so already, PLEASE read Q3/A3 and Q4/A4 in the following URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

For those that think SELECT/INTO isn't logged in a database with a SIMPLE recovery mode... EVERY action in a database is logged... SELECT/INTO, if it meets certain conditions, is MINIMALLY logged and will still blow away an INSERT into a new table even if the database is in the FULL recovery mode.

Again... don't take my word for it... run the following in each recovery mode... notice the number of logical reads on the INSERT INTO example...

--===== Create and populate a 10,000 row test table.

-- This is the "controlled" source for further tests

SELECT TOP 10000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

GO

----------------------------------------------------------------------------------------

-- The INSERT/INTO test

----------------------------------------------------------------------------------------

--===== Create a table for the Insert/Into test

CREATE TABLE Table1 (SomeID INT,SomeInt INT, SomeLetters2 CHAR(2))

--===== Do the test while capturing some statics information

SET NOCOUNT ON

PRINT '===== INSERT INTO ====='

SET STATISTICS IO ON

SET STATISTICS TIME ON

INSERT INTO Table1

SELECT * FROM dbo.JBMTest

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',78)

GO

----------------------------------------------------------------------------------------

-- The SELECT/INTO test

----------------------------------------------------------------------------------------

--===== Do the test while capturing some statics information

SET NOCOUNT ON

PRINT '===== SELECT INTO ====='

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT *

INTO Table2

FROM dbo.JBMTEST

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',78)

GO

--===== Simple house keeping

DROP TABLE dbo.Table1, dbo.Table2, dbo.JBMTest