Temporary table in SQL Server - WebNetGuru.com

Temporary table in SQL Server

Temporary Table in SQL Server creates for temporary purpose to store data for particular purpose. Temporary table is get deleted once the scope is get over.

There are two types of temporary tables in SQL Server.
1) Local temporary table
2) Global temporary table

Both Local and Global temporary table having different scope which we are going to see practically below. Keep in mind that most of the time temporary table called as temp table in SQL Server. SQL Server use the hash key (#) to create local temp table in SQL Server and use double hash key (##) to create global temp table in SQL Server.





Note : Learn how to create physical table in sql server.


Below are practical characteristic of the  local and global temporary table.

When to use temporary table in SQL Server ?

This question asked by the interviewer to check the basic knowledge of the candidate. Local temporary table having different purposes.

1)  Most of the time temporary table is created in store procedure to store the small temporary data for current active connection.

2) Let consider the scenario that, In School database having student table, in one procedure we required 10th class student data 5 times. So in this instead of writing select query 5 times on physical table we create one temporary table and use them 5 places.

3) Temporary table in SQL Server use for to improve the performance of procedures.

4) We can create the index on temporary table in SQL Server.

Local Temporary Table in SQL Server


Create temporary table in SQL Server

Syntax to create temporary table in SQL Server (#table) is similar to physical table syntax. Open query editor and create temporary table.

Below is syntax of temporary table, create temporary student table.


Example

CREATE TABLE #tempStudentTable
(
Id INT ,
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)

Execute above statement, so new temporary table get created. Below is the output.

create temporary table in sql server

Create temp table using insert into table SQL Server

This is another simple way to create temp table in SQL Server.
We can insert data in temporary table using INTO keyword, SQL Server uses INTO keyword to insert bulk data in temporary table.


create temporary table in sql server using INTO keyword

What is location of temporary table in SQL Server

Once temporary table get created it create into Object Explorer → Databases → System Databases → tempdb → temporary table → respected temporary table name. Find the location in below image.


What is location of temporary table in SQL Server

Scope of temporary table in SQL Server

Scope of temp table in SQL Server is limited to current connection or query editor. Insert data in temporary table and select record in different connections, SQL Server give an error message Invalid object name. Because the temporary table get created in connection 1, and we are trying to access in connection 2.


scope of temporary table in sql server

Once current connection get is over then temporary table also get removed from tempdb database.
In below image, connection 1 create the temporary table and when table connection 1 get removed then it temporary table get removed.


scope of temporary table in sql server

Create index on temporary table in SQL Server

As we already discuss that temporary table in SQL Server is slimier to physical table so this is allow creating index on temp table in SQL Server. In below example we create non-cluster index on temp table.

Create index on temporary table in SQL Server

How to drop temporary table in SQL Server ?

Temp table get removed from database automatically once scope get over but we can drop temp table in SQL Server manually.
DROP keyword is use to drop temp table in SQL Server.


drop temporary table in SQL Server syntax

Note : Learn What is exact difference between Delete, Truncate and Drop keyword in SQL Server.

Note : we can also user DROP TABLE IF EXIST < table name > in SQL server to drop table. It will check if table is available then it drops else it will not give an error.

Global Temporary Table in SQL Server

Global temporary table name itself explain that this is temporary table but scope of table is global in Server, It helps us to store temporary data which is accessible to all active connection.


Create global temporary table in SQL server

Syntax to create global temporary table (##table) is similar to physical table syntax. Open query editor and create temporary table.
Double hash key (##) key used to create temporary table. Below is example of create global temp table that is student.


Example

CREATE TABLE ##tempGlobalStudentTable
(
Id INT ,
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)

Execute above statement, so new global temporary table get created. Check below example.

create global temporary table in sql server

Create global temp table using insert into table SQL Server

We can insert data in global temporary table using INTO keyword, in SQL Server INTO keyword to insert bulk data in temporary table.


create global temporary table using INTO keyword in sql server

What is location of global temporary table in SQL Server

Location of global temporary table is similar of temp table location. that is Object Explorer → Databases → System Databases → tempdb → temporary table → respected temporary table name. Find the location in below image.


Location of global temporary table in sql server

Scope of global temporary table in SQL Server

Scope is not limited current session, and it allows accessing created global temporary table to all connected connections.
Insert data in Global temporary table and select record in different session or connection, SQL Server will not give an error message because this is a global table and accessible from all connected connections.


scope of global temporary table in sql server

Global temporary table accessible to all connection, but it gets remove from system when table created session get closed.
If you see below image we create Global temporary table from connection 1, and we access table from connection 2 and connection 3.
But when we close the connection 2 then Global temporary tables not get removed from system, but it gets removed when we close the main connection that is connection 1.
Once the connection 1 is get close then global table will not accessible in connection 3.


scope of global temporary table in sql server

Create index on global temporary table in SQL Server

SQL Server allows to create index on global temporary table. Below example show the how to create non-cluster index on global temp table in SQL Server.

create index on global temporary table in sql server

How to drop global temporary table in SQL Server ?

Global Temp table get removed from database automatically once all connection get over but we can drop global temp table in SQL Server manually.
DROP keyword is use to drop temp table in SQL Server. Find below example of how to drop global table.

drop global temporary table in sql server

Note : we can also user DROP TABLE IF EXIST < table name > in SQL server to drop table. It will check if table is available then it drops else it will not give an error.

Difference between local temp table and global temp table in SQL Server

or some time same question can ask in different way. That is

difference between # and ## temp tables in SQL Server

The main difference between temporary table and global temporary table is scope of tables accessibility.

Temporary Table
If temporary table get create from one connection, then it is not accessible from connection.
If we close a respected connection then temporary table get remove from database.


Global Temporary Table
If Global temporary table get create from one connection, then it is accessible from another connection or all connected connections.
If we close the first session, then only Global temporary table get removed.


To read more about temporary table in SQL Server refer Microsoft. Move Top