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
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 .
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
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 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 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.
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!
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