SQL Server Temporary Table Vs Table Variable.

Temporary Table

As the name suggests, Temporary Tables are temporary in nature. They are created at run-time, but we can perform all the operations on these tables as can be done on normal/permanent tables. The Temporary tables are created in tempdb database.

Types of Temporary Tables

1. Local Temporary Table
2. Global Temporary Table

Local Temporary Table

These tables are created by using ‘#’ as prefix for table name. These tables are available only for the user who created it or we can say current user. Also, once the query window is closed the existence of the table gets vanished; i.e. table gets automatically deleted.

Syntax:

 CREATE TABLE #<tablename#>
 ( Column1 datatype, column2 datatype……) 
    

Example

CREATE TABLE #temp_Students
(
    StudentName varchar(100),
    Stream Varchar(20),
    TotalMarks int
)
     

Temp table #temp_Students get created in tempdb database.

Now insert some records into temp table.

 INSERT INTO #temp_Students
 VALUES ('Rakhi Sharma','Science',450)
 INSERT INTO #temp_Students
 VALUES ('Veer Khanna','Accounts',430)
 
 SELECT * FROM #temp_Students

    

Global Temporary Tables

These tables are created by using ‘##’ as a prefix for the table name. These tables are available in all databases. Whenever the connection is closed global temp table gets deleted.

    CREATE TABLE ##<tablename>
    ( Column1 datatype, column2 datatype……) 

    

Example:

CREATE TABLE ##temp_Employee
(
 EmpID int,
 EmpFullName varchar(100),
 Designation Varchar(50)
)

    

Inserting records into Global Temporary Table

 INSERT INTO ##temp_Employee
 VALUES (1001,'Spence Huge','IT Manager')
 INSERT INTO ##temp_Employee
 VALUES (1002,'Mellisa Karl','Accountant')
 
 SELECT * FROM ##temp_Employee

    

Now we have got the idea of what Temporary tables are, we should know when to use a global /local temporary table and what the advantages are.

• Complex Queries and Joins – in these kinds of queries Local temporary tables can be used. For more details see Types of SQL Joins with Example.
• Stored Procedures – Whenever we are dealing with Stored Procedures and it require row manipulation Global Temporary tables are best to be used and we can call Procedure from a Procedure, in that case, the existence of a temporary table. For more info see SQL Server Stored Procedure.

Table Variables

Table Variables can be seen as a alternative of using Temporary Tables. These table variables are none less than any other tables as all table related actions can be performed on them. The Syntax of creating a Table Variable is close to creating a normal table but since it is a variable, so we declare a Table Variable. These are declared by using ‘@’ as a prefix for table name.

Syntax:

  DECLARE @<tablename> Table
  (Column1 datatype,Column2 datatype……, ….)

    

Example:

  
  DECLARE @ProductInfo TABLE
  (
    ProductID int, 
    ProductName Varchar(100),
    Cost int
  )

  INSERT INTO @ProductInfo
  VALUES (101,'PLC',15000)
  INSERT INTO @ProductInfo
     VALUES (201,'Automatic Drives',35000)

    

While inserting records into Table Variable keep in mind that declaring tables and inserting records and selecting records should be done in a single instance. Otherwise, it would show error - must declare the table variable.

Difference between Temp table and table variable