Temporary Tables in SQL Server

In this article, I am giving a quick overview about temporary tables . Please give your valuable suggestions and feedback to improve this article.

Temporary tables are similar to Permanent tables but Temporary tables are stored in TEMPDB System Database. Click here to know more about SQL Server System Databases. Temporary tables supports all kinds of operation that one normal table supports. Temporary tables can be created at run time in Stored Procedure. Temporary tables can’t be used in User Defined Function. Temporary tables help the developer to improve performance tuning of query.You can’t use stored procedure SP_Rename to rename the Temporary Table name; it can be used to rename columns of Temporary table. Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

In SQL Server based on scope and behavior there are two types of temp tables.

1.       Local Temp Table

2.       Global Temp Table

Local Temp Table: Local temporary table name is stared with single hash ("#") sign. Local tempoarary Table scope is Local which means Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects the connection from instance of SQL Server. If User wants to drop the table then using DROP command it can be deleted.Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server. They cannot therefore be used in views and you cannot associate triggers with them.Constraints and Indexes can be created on Local Temporary table, but we can't create Foreign key reference. 

Syntax to create Local Temporary Table

Create Table #Table_Name (Column data_type [Width], ….n)

Let’s create one Local Scope Temporary Table and understands the above explained concept.

USE TEACHMESQLSERVER

GO

CREATE TABLE #LOCALEMPTEMPTABLE (EMPID INT, EMPNAME VARCHAR(100))

The above script will create a temporary local scope table in TEMPDB database. You can find it in Tempdb database using below query

GO

SELECT * FROM TEMPDB.SYS.TABLES

As I have already told, you can perform any operation on Temporary table that a Permanent table supports. Let’s insert few records in recently created temporary table. Insert query will be same to insert data in Temporary tables.

GO

INSERT INTO #LOCALEMPTEMPTABLE VALUES ( 1, 'GHANESH');

Let’s fetch the record from the #LocalEMPTempTable table using below query.

GO

SELECT * FROM #LOCALEMPTEMPTABLE

After execution of all these statements, if you close the query window and again open a new connection and then execute "Insert" or "Select" Command on #LocalEMPTempTable table, it will throw below error.

Msg 208, Level 16, State 0, Line 1

Invalid object name '#LOCALEMPTEMPTABLE'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user. You are using a new connection in which the Local Temporary Table was not created.

Global Temp Table: Global Temporary tables name starts with a double hash ("##"). Scope of Global temporary table is Global which means Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It will be automatically deleted once all connections have been closed from the instance of SQL Server. User can also drop the table using DROP command. It is suggested to Drop the tables manually using DROP statement if it is not required anymore. Constraints and Indexes can be created on Global Temporary table, but we can't create foreign key references.They cannot be used in views and you cannot associate triggers with them.

Syntax to create Global Temporary Table

Create Table ##Table_Name (Column data_type [Width], ….n)

Let’s create one Global Scope Temporary Table and understands the above explained concept.

USE TEACHMESQLSERVER

GO

CREATE TABLE ##GLOBALEMPTEMPTABLE (EMPID INT, EMPNAME VARCHAR(100))

The above script will create a temporary Global scope table in TEMPDB database. You can find the table in Tempdb database using below query.

GO

SELECT * FROM TEMPDB.SYS.TABLES

As I have already told, you can perform any operation on Temporary table that a Permanent table supports. Let’s insert few records in recently created temporary table. Insert query will be same to insert data in Temporary tables.

GO

INSERT INTO ##GlobalEMPTEMPTABLE VALUES ( 1, 'PRASAD'), ( 2, 'ANSHUL')

Let’s fetch the record from the ##GlobalEMPTempTable table using below query.

GO

SELECT * FROM ##GLOBALEMPTEMPTABLE

After execution of all these statements, if you open a new query window and then execute "Insert" or "Select" Command on ##GLOBALEMPTempTable table, it will execute successfully.

This is because the scope of Global Temporary table is not bound with the current connection of current user. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

In What scenarios Temp Tables should be used?

• When a large number of row manipulation is required in stored procedures.
• When we are having a complex join operation.
• Temporary tables can replace the cursor because we can store the result set data into a temp table, and then we can manipulate the data from there.

Key points to remember about Temp Tables.

• Temporary Tables can’t have foreign key constraint.
• We can’t create Views on Temporary Tables.
• We can’t create Triggers on Temporary Tables.
• Temporary Tables will be stored in Tempdb system database.
• Temporary Tables are automatically deleted based on their scope.
• Temporary Tables can’t be renamed using SP_Rename sytem stored prodecure.
• Temporary Tables can’t be used in User Defined Function.
• The best way to use a temporary table is to create it and then fill it with data. i.e., instead of using Select into temp table, create temp table 1st and populate with data later.
• Temporary Tables can have Indexes, Constraints (except Foreign key constraint).
• Temporary Tables Supports Transactions.
• Temporary Tables Supports Error Handling.
• Temporary Tables Supports DDL, DML commands.
• Tables need to be deleted when they are done with their work.

Click here to know about Differences Between Temporary Tables and Table Variable. Don’t forget to share on Facebook if you like the article.