What's the difference between a temp table and table variable in SQL Server?

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 can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) [SQL 2014 has non-unique indexes too][1].

- Table variables don't participate in transactions and `SELECT`s are implicitly with `NOLOCK`. The transaction behaviour can be very helpful, 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. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb ([ref][2]). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

- Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:

- [Martin Smith's great answer][3] on dba.stackexchange.com

- MSDN FAQ on difference between the two:

[To see links please register here]

- MDSN blog article:

[To see links please register here]

- Article:

[To see links please register here]

- Unexpected behaviors and performance implications of temp tables and temp variables: [Paul White on SQLblog.com][4]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

Differences between `Temporary Tables (##temp/#temp)` and `Table Variables (@table)` are as:

1. `Table variable (@table)` is created in the `memory`. Whereas, a `Temporary table (##temp/#temp)` is created in the `tempdb database`. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.

1. `Table variables` cannot be involved in `transactions, logging or locking`. This makes `@table faster then #temp`. So table variable is faster then temporary table.

1. `Temporary table` allows Schema modifications unlike `Table variables`.

1. `Temporary tables` are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

1. `Temporary tables` are allowed `CREATE INDEXes` whereas, `Table variables` aren’t allowed `CREATE INDEX` instead they can have index by using `Primary Key or Unique Constraint`.

In SQL the Temporary tables are stored in the TempDB and the local temporary tables are only visible in the current session and it will not be visible in another session. This can be shared between nested stored procedure calls. The Global temporary tables are visible to all other sessions and they are destroyed when the last connection referencing table is closed. For Example,

Select Dept.DeptName, Dept.DeptId, COUNT(*) as TotalEmployees
into #TempEmpCount
from Tbl_EmpDetails Emp
join Tbl_Dept Dept
on Emp.DeptId = Dept.DeptId
group by DeptName, Dept.DeptId

Table variables are similar to tempTables, a table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures. The same query can be written using Table variable by

Declare @tblEmployeeCount table
(DeptName nvarchar(20),DeptId int, TotalEmployees int)
Insert @tblEmployeeCount
Select DeptName, Tbl_Dept.DeptId, COUNT(*) as TotalEmployees
from Tbl_EmpDetails
join Tbl_Dept
on Tbl_EmpDetails.DeptId = Tbl_Dept.DeptId
group by DeptName, Tbl_Dept.DeptId

It surprises me that no one mentioned the key difference between these two is that the temp table supports **parallel insert** while the table variable doesn't. You should be able to see the difference from the execution plan. And here is [the video from SQL Workshops on Channel 9][1] and [MSDN doc][2].

This also explains why you should use a table variable for smaller tables, otherwise a temp table, as [SQLMenace answered][3] before.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]