Nov 8, 2015

Temp Table vs Table Variable in SQL

Both temp table and table variable are used to store data temporarily for a particular scope. You can perform all DML operations on both like you perform in a normal table. In terms of querying data they are similar to normal tables except the scope which is limited to a particular session.

Name of temp table starts with "#" and name of table variable starts with "@"

Syntax for Temp Table
CREATE TABLE #temp (id int, name VARCHAR(100))

Syntax for Table Variable
DECLARE @tab TABLE (id int, name VARCHAR(100))

Both have some similarities and differences.

Similarities
  • Both are instantiated in TempDB (System Database)
  • Clustered index can be created on both Temp Table and Table Variable
  • Perform any DML operations like Insert/Update/Delete.
Differences
  • Transaction logs are logged for Temp tables but not for Table Variable
  • Tables variables can not have non-clustered index
  • Indexes can be explicitly created on Temp tables but not on table variable i.e index can be created while creating table only
  • Scope of table variable is limited to the Object only in which it is declared while scope of temp variable is the session in which it is created.
  • Temp table can be created using "SELECT INTO" but not Table variables.
  • Table variables can be used as parameter.
  • Default collation for table variable is collation of the current database where as temp tables takes the default collation of tempdb.

    Choose :
  • OR
  • To comment
No comments:
Write Comments