Temporary Table Vs Table Variable.


This article describes about uses of SQL server Temporary Table and Temp Variable. It also describes difference between Temp table and Temp Variable.

Temporary Table

As the name suggest, 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.

SQL temp tables 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

    

sql server insert into temp table


Global Temporary Tables

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

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

    

Example:

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

    

sql server global temp table in tempdb

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 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 existence of temporary table remains in memory. For more info see SQL Server Stored Procedure.

Table Variables

Table Variables can be seen as 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 table and inserting records and selecting records should be done in a single instance. Otherwise it would show error - must declare the table variable.

sql server table variable


Difference between Temp table and table variable

Temp Table (#)

User defined function (@)

Temp table can be part of Transaction.

Table variable does not have any effect of Transaction.

Recompilation of query does not effect on Temp table.

Recompilation of query has effect on table variables.

Temp table can have clustered or non clustered index.

Table variable can have only clustered index.

It allows you SELECT INTO statements to insert data from existing table.

It does not allow you SELECT INTO statements to insert data from existing table.

Temp table can be accessed in nested stored procedure. For example you are working on stored procedure A, from stored proc A you are calling stored proc B. The temp table created in stored proc A can be accessed in stored proc B.

Table variable cannot be accessed in nested stored procedure.

Temp table allows TRUNCATE or ALTER table statements.

Table variable does not allow TRUNCATE or ALTER table statements.

Temp table cannot be used in Used defined functions.

Table variable can be used in User defined functions.

Download Source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com