sql temp tables @tmp vs #tmp

whats the difference between the two types of temp tables @tmp vs #tmp in SQL 2005? and are their other types i am unaware of?

thanks

#tmp is a temp table and acts like a real table mostly. It can have indexes, can have statistics, participates in transactions, optimiser will work out correct row estimates

@tmp is a table variable. No indexes, no statistics, not transaction aware, optimiser always assumes exactly 1 row

Otherwise, they are both scoped (slightly different), in memory/cache but context is tempdb though, will spill to disk if too big etc

Edit:

About keys on table variables. They make no difference. There are no stats and one row is assumed. It will change a table scan to a clustered index scan which is the same. Check any query plan and estimated rows.

Also, just read this Read What a difference a temp table makes over a table variable

The first thing that I did was put a primary key on the @ComputersToProcess table variable. That turned the table scan into a Clustered Index Scan, but didn’t do anything for performance.

See http://support.microsoft.com/kb/305977:

Table variables have the following advantages over temporary tables:

• As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
• Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

These are some of the drawbacks as compared to temporary tables:

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

• A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

For practical performance comparisons, see also:

• http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
• http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html
• http://www.sqlservercentral.com/articles/63472/

Just adding onto existing answers. There's actually 3 types of temp tables. In addition to the other answers, you can create global temp tables like so ##globalTempTable

These are visible to all sql server connections, and are rarely used, however useful in noting that they do indeed exist.

Here's a good read on the difference between standard and global temp tables http://www.codeproject.com/KB/database/TempTable.aspx

@tmp refers to a variable of type table stored in memory whereas #tmp refers to a table in the TEMP database.

There is one other type of "temp table" I you use a CTE it is like creating a temp table.