Differences between Temp Table and Table Variable

Differences between Temp Table and Table Variable:

Temp Table:

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below- Local Temp Table and Global Temp Table.

Local Temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

Syntax for Local Temp table:

Create table #Table
(

Column1 int,

Column2 varchar (2)

)            

Global Temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

Syntax for global Temp table:

Create table ##Table
(

Column1 int

 , Column2 varchar (2)

)            

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Syntax for Table Variable:

Declare @Employee table
(

Id INT,

Name Varchar (50) 

)

Performance driven differences:

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile.

Next: Differences between CTE and View