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.