GilaMonster (8/31/2008)
Very nice.Just one small thing. You say that for data storage, temp tables are in TempDB and table variables are in memory and tempDB. I may be misunderstanding what you mean there, but temp tables are also memory resident unless it becomes necessary to write them to disk (memory pressure, too large a table)
Also, the Appendix states that table variables are held in memory and temporary tables are held in tempdb. Whilst usually correct*, it is also the case that both are physically created in tempdb, as can be demonstrated by running the following code on a SQL Server which has no other activity occurring (runs on SQL 2000, 2005 and 2008):
-- make a list of all of the user tables currently active in the
-- TempDB database
if object_id('tempdb..#tempTables') isnot null droptable #tempTables
select name into #tempTables from tempdb..sysobjectswhere type ='U'
-- prove that even this new temporary table is in the list.
-- Note the suffix at the end of it to uniquely identify the table across sessions.
select * from #tempTables where name like '#tempTables%'
GO
-- create a table variable
declare@MyTableVariable table (RowID int)
-- show all of the new user tables in the TempDB database.
select name from tempdb..sysobjects
where type ='U' and name notin (select name from #tempTables)
GO
* According to KB305977, a table variable can hold more data than could fit into memory.
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k