In SQL Server 2005, we can create temp tables one of two ways:
declare @tmp table (Col1 int, Col2 int);
or
create table #tmp (Col1 int, Col2 int);
What are the differences between the 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?
@wcm - actually to nit pick the Table Variable isn't Ram only - it can be partially stored on disk.
A temp table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but obviously if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.
For all of you who believe the myth that temp variables are in memory only
First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.
Read the article here: TempDB:: Table variable vs local temporary table
In which scenarios does one out-perform the other? For smalller tables (less than 1000 rows) use a temp variable otherwise use a temp table
KiwiBastard had a good explanation. But to add to that, table variables are stored in memory, where as temp tables are stored on disk. So if you will have a very small number of rows then the table variable will almost always be faster. If your result set is very large, then you probably want to use a temp table.
Also, I'm not sure if this is true anymore, but it used to be the case that if you used temp tables in a sproc, then it would get re compiled every time it ran.
No matter where a temporary table or temporary variable is stored, there's one very significant difference. A table variable is not part of your transaction, and there is nothing written to the transaction log when you make changes to the table variable. This will result in better performance.
There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.
As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)
Some points to consider when choosing between them:
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.
Some further reading:
MSDN FAQ on difference between the two: http://support.microsoft.com/default.aspx/kb/305977
MDSN blog article: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Article: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html#
Consider also that you can often replace both with derived tables which may be faster as well. As with all performance tuning, though, only actual tests against your actual data can tell you the best approach for your particular query.
The other main difference is that table variables don't have column statistics, where as temp tables do. This means that the query optimiser doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows.