Tables Variables ARE Stored in tempdb

Well here's something I never knew.  When you declare a table variable it does get instantiated into tempdb.dbo.sysobjects.  I was under the impression it wouldn't.  If you run this code...

declare @junk table(i int)

select *
from tempdb.dbo.sysobjects
where name not like 'sys%'

... you can see it happening.  I found this and a few other interesting table variable nuggets at INF: Frequently Asked Questions - SQL Server 2000 - Table Variables.

Show Comments