What's the difference between a temp table and

Question

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 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?

Answer 1

@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.

Good background article

Answer 2

Temporary Table

Temporary table behave like a real tables but created on run time. Its work similar to real table. We can do almost every operations which possible into real tables. We can use DDL Statements like ALTER, CREATE, DROP on Temporary Tables.

Any changes in structure of Temporary table is possible after creation. Temporary Table stored into “tempdb” Database of system Databases.

Temporary Table participate in transactions, logging or locking. Due to that reason it’s slower than Table Variable.

Table Variable

It’s Variable but work like a table. It’s also created into Tempdb Database not in the memory. Table Variable only available in the batch or stored Procedure scope. You no need to drop Table Variable , It’s automatically dropped when batch and store Procedure execution process complete

Table variable support primary Key, identity on creation time. But it not support non-clustered index. After declaration primary key, identity you can’t modify them.

Table variables don't participate in transactions, logging or locking. Transactions, logging and locking not effect to Table Variables.

Read this article for more - http://goo.gl/GXtXqz

Answer 3

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.

Answer 4

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.

Answer 5

1. Temp table: A Temp table is easy to create and back up data.

   Table variable: But the table variable involves the effort when we usually create the normal tables.

2. Temp table: Temp table result can be used by multiple users.

   Table variable: But the table variable can be used by the current user only. 

3. Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist.

   Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.

4. Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,

   Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.

5. Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table.

   Table variable: But the table variable can be used up to that program. (Stored procedure)

6. Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions.

   Table variable: But we cannot do it for table variable.

7. Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions .

   Table variable: But the function allows us to use the table variable. But using the table variable we can do that.

8. Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls.

   Table variable: Whereas the table variable won't do like that.