Temp Tables Vs Table Variables

It’s always a tricky decision while thinking about using Temp Table of Table Variable. Let’s look into this article to check the differences between these two.

#Temp tables are the regular SQL tables that are defined and stored in TempDB database of Sql server. It is more or less equal to permanent tables but the only difference with #table is that could not have foreign key over it.

While going through the rest of the article we will look into the various important aspects of both the objects i.e. (#table & @table variable).

How to create / declare policy:

#Table: Temp tables could be created by writing table creation script or by writing the insert into script. Both the ways Process ID specific #table get created into TempDB database.

@Table Variable: Table variables can only be created by specifying Declare @name as Table syntax. Creating a table variable is little different from Create table syntax because we have to declare table variable like the way we declare local variables.

Note: Table variables cannot be created with the Select into syntax.

Lifespan of #table & @table variable:

Temporary table are of two type namely #table (Local temporary table) & ##table (Global temporary table). The lifespan of local temporary table is limited till the specific user session. Explaining in brief whenever any Process ID creates a temporary table then that #table gets created into TempDB database with that Process ID Specific access rights. If some other process ID also creates the #table with the same name then also without creating any mismatch between the names Sql engines creates the temp table for another user & keeps them unique for each process use.

Lifespan of #table is limited till the user does not forcefully drop the table or close the connection. Once the connection is closed or #table is dropped the #table gets flushed from the TempDB database.

@Table Variable: The lifespan of table variables are limited till the execution of process or execution of queries. Once the execution of queries ends the data & table architecture gets flushed from the Sql database memory.

Performance Parameter:

While comparing on the performance platform we need to discuss various parameters.

#Table: #tables are physically stored on system drive due to which it does not use the system buffer the way table variable uses. Index creation is permitted on #table which enables the user to fetch the data with the use of Index in faster manner. #table can be used to store huge data without harming the system performance. The only burden which #table put’s on system is its Read & Write operation.

#table participate in transactions so whenever the transaction related request occurs #tables behave accurately & revert back to the original positions.

@Table Variable: A myth which is associated with @table variable is that it should be used for the smaller data size operation because it uses the Sql buffer for storing & managing data which may cause the performance issues while working with huge data size.

@table variable do not participate in transactions & do not behave as per the transaction rules.

Note: #Tables causes a recompilation due to which a same execution plan could not be used. The cost of Read / Write operation of table variables are less as compared to #tables due to which it shows good performance while we need to write & then read data from processed table.