In SQL Server 2005, we can create temp tables one of two ways:
declare @tmp table (Col1 int, Col2 int);
create table #tmp (Col1 int, Col2 int);
What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.
In which scenarios does one out-perform the other?