Difference between temp table and table variable

In SQL, temp tables and table variables are used to save or store data in temporary basis. Both are almost same, but have some difference. This article describes a comparison on SQL temp table and table variable. SQL offer four types of table structure to store data. They are:

• Local temporary table (#table_name)
• Global temporary table (##table_name)
• Permanent table (table_name)
• Table variable (@table_name)

Local Temporary Table (#table_name)
Local temporary tables are created in the system database tempdb and populated on disk. Local temporary tables are visible only to the connection that creates it, and are destroyed or deleted when the connection is closed. But we can manually clean up the data by using: DROP TABLE #temp. One of the main benefits of using this table is that it reduces number of locking and loggin. One problems of using this table is that if we don’t use ROLLBACK in transaction then it may create problems. An example of local temporary table is given bellow:

CREATE TABLE #temp(
 ID INT,
 Name NVARCHAR(50)
)

Global Temporary Table (##table_name)
Global temporary tables are visible to everyone or all session, and are deleted when all connections that have referenced them are closed.Global temporary tables function much like local temporary tables. It reduces number of locking and logging. An example of global temporary table is given bellow:

CREATE TABLE ##temp(
 ID  INT,
 Name NVARCHAR(50)
)

Permanent table (table_name)
Permanent tables are visible to everyone, and are deleted when the server is restarted.A permanent table is created in the local database. Also we can choose different database or server to create permanent table. An example of permanent table is given bellow:

CREATE TABLE temp(
 ID  INT,
 Name VARCHAR(50)
)

Table Variable (@table_name)
Table variables are visible only to the connection that creates it and are deleted when the batch or stored procedure ends. A table variable is created in memory and its performance is higher than #temp tables. Because, it has less locking and logging. It can have a primary index. It is suitable for small amount data. Table variables are only allowed in SQL Server 2000+. We can’t truncate a table variable.It has following limitations:

• Table variables can not have Non-Clustered Indexes. Only Clustered indexes can be created.
• We can not create constraints in table variables
• We can not create default values on table variable columns
• We can’t create the statistics against table variables.

An Example of table variable is given bellow:

DECLARE @temp TABLE(
 ID  INT,
 Name VARCHAR(50)
)

temp table VS table variable
Temp tables and table variables are not same. They have some differences. The difference between temp tables and table variables are given bellow:
#temp table

1. Stored data in system hard disk
2. It is slower than @table variable
3. Suitable for large amount of data
4. Can have indexes

@table variable

1. Stored data in system ram
2. It is faster than #temp table
3. Suitable for small amount of data, it depends on system ram size
4. Can only have a primary index

The use of temp table and table variables can improves our applications. One should have the correct knowledge about on SQL temporary table and table variable.