SQL temporary table vs Table variable

In this article you will learn about sql temporary table and table variable, what are the differences? which is better and why?

Create TABLE #tempStudents (
    [StuId] [bigint] IDENTITY(1,1) NOT NULL
);

single hash #tempStudents - normal temporary table. and double hash ##tempStudents - global temporary table

Before i talk about the difference between sql temp table and table variable, let’s learn what they are and how to use them.

SQL temporary tables are just like normal tables but exist temporarily on the SQL Server for current connection instance.

Temp tables are created under system database=> tempdb=> temporary tables

SQL temporary table

By default, temp tables are only current connection specific, that means temp tables are accessible only the connection under which the table is created, however , there is way to create global temporary tables .

sql temp table example

We can create temporary table just by inserting data from any existing table or view like example below.

SELECT Firstname, Email
INTO #tempStudents 
FROM [dbo].[tbStudent]
select * from #tempStudents

We also can create temporary table with create command like example below.

Create TABLE #tempStudents (
    [StuId] [bigint] IDENTITY(1,1) NOT NULL,
    [Firstname] [varchar](50) NOT NULL,
    [Lastname] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [ContactNumber] [varchar](50) NULL
);

Now the above table is accessible only within connection, once we close the connection the table will be dropped automatically.

We also can drop the table manually.

drop table #tempStudents
Global temporary table

Sometimes you may need a temporary table that is accessible across different connections. In that scenario, you can create global temporary table with double hash (##). notice in sql temp table difference between # and ##.

Create TABLE ##tempStudents1 (
    [StuId] [bigint] IDENTITY(1,1) NOT NULL,
    [Firstname] [varchar](50) NOT NULL,
    [Lastname] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [ContactNumber] [varchar](50) NULL
);

You can access this table from different connection, and this will be deleted automatically when any connection closes and the table is not being accessed by other connection. you can also manually drop the table by executing drop statement.

drop table ##tempStudents1
SQL table variable

SQL table variables are basically in-memory tables which will allow you to hold data.

You can declare a table variable with declare keyword, like declare @tableName TABLE

declare  @tabStudents TABLE (
    [StuId] [bigint] IDENTITY(1,1) NOT NULL,
    [Firstname] [varchar](50) NOT NULL,
    [Lastname] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [ContactNumber] [varchar](50) NULL
);
Table variable has limited scope

Table variable are just like local variables, can be accessed only within the code block, you can not access the variable from outside the code block.

If you define a table variable in a UDF (user-defined function) or SP (stored procedures), the table variable will no longer exist once the UDF or stored procedure execution complete and exits the scope.

Sql temp table vs table variable

We have seen both table variable and temp table, they are very similar in nature, now let’s understand the difference between sql temp table and table variable, so you know which one to use and why!

Temp Table
  • Temporary table (#tempTablename) is created in the tempdb database
  • Temporary table can NOT be passed as a parameter to any function
  • Temporary table can be used in transactions or logging
  • Temporary table is accessible any time within that connected instance, and global temporary table is accessible even from other connection too.
Table Variable
  • Table variable (@variableTablename) is created in the memory
  • Table variable can be passed as a parameter to stored procedures or functions
  • Table variable can NOT be used in transactions or logging
  • Table variable is accessible only within the code block, once we come out of the scope, the existence of table variable is over.

How to create Table Variable in sql query?

DECLARE @tbWeekDays TABLE(DyNumber INT, shortName VARCHAR(40) , dayName VARCHAR(40))
 
INSERT INTO @tbWeekDays
VALUES 
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday')	
SELECT * FROM @tbWeekDays

Table variable values are stored in-memory.

You may be interested to read following posts

 
Hire SQL Developer
SQL Temp Table
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
SQL Table Variable
MS SQL Examples | Join MS SQL Course