Post
Ask Question

What are Temporary Tables in SQL Server?

Introduction

Temporary tables are useful tools in SQL Server provided to allow for short-term use of data. SQL Server provides the concept of a temporary table, which helps the developer in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside the tempdb database.

Types of temporary table

  1. Local temp table: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. They are automatically deleted when the user disconnects from instances. A local temporary table name is started with a hash ("#") sign.
  2. Global temp table: Global temporary tables are available to any connection once created and are dropped when the last connection using it is closed. Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table, it is then available to any user by any connection. It can only be deleted after all connections have been closed.

Before we start creating a temp table, we need to keep the following points in mind:

  • A temporary table was created on the tempdb of the SQL Server. This is a separate database. So, this is an additional overhead and can cause performance issues.
  • The number of rows and columns needs to be as minimal as needed.
  • Tables need to be deleted when they are done with their work.

Create a Local temp table

  1. Using CREATE 
    CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))
  2. Using SELECT...INTO 
    SELECT age as DummField1
Lastname as DummyField2
INTO #myTempTable
FROM DummyTable

How do you insert it into a temp table?

insert into #LocalTempTable values ( 1, 'Vishal','India');

How do you select from the temp table?

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute an "Insert" or "Select" Command, it will throw the following error:

But if we choose to close the query window after executing the above commands and again execute an insert or select command, it will throw an error saying invalid object name #LocalTempTable.

Reason: This is because the scope of a Local Temporary table is only bounded by the current connection of the current user.

Create a Global temp table

  1. Using CREATE 
    CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))
  2. Using SELECT...INTO 
    SELECT age as DummyField1,
Lastname as DummyField2
INTO ##myTempTable
FROM DummyTable

The above script will create a temporary table in the tempdb database. We can insert or delete records in the temporary table similar to a general table like:

How do you insert it into the temp table?

insert into ##NewGlobalTempTable values ( 1, 'Vishal','India');

How do you select from the temp table?

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

When to use Temp tables?

  1. When we are doing a large number of row manipulations in a stored procedure
  2. When we have a complex join operation.
  3. This is useful to replace the cursor. We can store the result set data in a temp table; then, we can manipulate the data from there.

Deciding between Local and Global

When deciding which type of table to use, these two questions to ask:

  1. "Do I need this data to persist after I am done using it?" If so, I need a standard table, not a temporary table.
  2. Do I need the data to be accessed outside of my single process?" This question can sometimes be a little tougher to figure out, so I have a simple suggestion. Make it a local temporary table for now, and if you find that you need a larger scope, change it later.


Similar Articles