Post
Ask Question

Temporary Tables In SQL Server

What are temporary tables?

 
Temporary tables are very similar to permanent tables. Permanent tables get created in the database you specify and remain in the database permanently until you delete or drop them. On the other hand, temporary tables get created in the TempDB and are automatically deleted when they are no longer used.
 
Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases, instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use temporary data in a simple scenario.
 

Types of Temporary Tables

  • Local Temporary Tables
  • Global Temporary Tables
First, we need to understand what permanent tables are. Permanent tables are those tables that we create by using the "Create table tableName" Command.
 
We can see permanent tables by expanding the database >> tables.
 
Temporary Tables in SQL Server
 

How can we create temporary tables?

 
To create temporary tables, we use the exact same command that we use to create a new table except if you are creating a local table, you need to prefix the name of the table with the # symbol. # indicates that this is a temporary table. The rest of the syntax is very similar to how we create a permanent table. 
 
Syntax
  1. -- Create Local temporary table    
  2. Create Table #myTable (id Int , Name nvarchar(20))     
  3. --Insert data into Temporary Tables    
  4. Insert into   #myTable Values (1,'Saurabh');    
  5. Insert into #myTable Values (2,'Darshan');    
  6. Insert into #myTable Values (3,'Smiten');     
  7. -- Select Data from the Temporary Tables    
  8. Select * from #myTable    
Execute the above code that will create temporary tables and will insert some data into the tables. Then, at last, it selects data from the local temporary tables like following.
 
Temporary Tables in SQL Server
 
We can see that data is retrieved from the local table. 
 

How can we check our newly created local table into the database?

 
Well, the answer is - Temporary tables are created in the TEMPDB. So, firstly, we need to expand system databases, then expand tempdb, and then inside the Temporary Tables directory, we can see our newly-created tables. 
 
We can see our newly created table using the following query.
  1. select name from tempdb..sysobjects where name like '#myTable%'    
The above query will produce an output like below.
 
Temporary Tables in SQL Server
 
In the above query, we are querying the sysobjects system table that is present in the TEMPDB. So all the database objects that we create - like stored procedure, tables, functions, views, etc. - will be automatically inserted into the sysobjects tables.
 
We are querying the tables to find if there is a local temporary table with the provided name.
 
We can see in the above query that we used like operator to match our table name because when we create a Local temporary table, the SQL Server will add a unique id to that table in the end, and we need to use the like (Wild card Operator) keyword to see that table. If we are not using the like operator and trying to match the exact table name, then it will return null in the output. 
 
Important points about temporary tables
  • A local temporary table is available only for the connection that has created the table. For example - suppose we created a temporary table, #myTable, in the first query window and try to use that table inside a New Query window, then this will generate an error - "Invalid object name '#myTable'". 
  • A local temporary table is automatically dropped when the connection that has created it is closed. 
  • If the temporary table is created inside a Stored Procedure, it gets dropped automatically once the stored procedure is executed. Let us see this with an example.
Let us create a Stored Procedure like below.
  1. Create Procedure spCreateLocalTempTable    
  2. AS    
  3. BEGIN    
  4.        --Create Temp Table    
  5. Create Table #myTable (id Int , Name nvarchar(20))     
  6.        --Insert data into Temporary Tables    
  7.        Insert into   #myTable Values (1,'Saurabh');    
  8.        Insert into #myTable Values (2,'Darshan');    
  9.        Insert into #myTable Values (3,'Smiten');     
  10.        -- Select Data from the Temporary Tables    
  11.        Select * from #myTable    
  12. END    
Then, execute the above stored procedure by using the following command.
  1. EXECUTE spCreateLocalTempTable    
This will produce an output like below.
 
Temporary Tables in SQL Server
 
After executing, if we try to select the data from this table, this will throw an error.
  1. Select * from #myTable     
Temporary Tables in SQL Server
 
In short, once the execution of the stored procedure is completed, the table is dropped automatically.
 
It is also possible for different connections to create a local temporary table with the same name. For example, USER1 and USER2 both can create a local temporary table with the same name, #myTable. For avoiding this kind of problem, SQL Server adds a random number at the end of the table so as to differentiate the tables with the same names for different connections. 
 

Global Temporary Tables

 
As we have learned above, a temporary table is only accessible to the connection that has created that temporary table. It is not accessible to other connections. However, we can create temporary tables that are accessible to all the open connections. Such temporary tables are called Global Temporary Tables.
 
To create a Global Temporary Table, we prefix the name of the table with 2 Pound (##) symbols.
  1. CREATE TABLE ##EmployeeDetails(ID int , NAME NVARCHAR(20))    
Global temporary tables are visible to all the connections of the SQL Server and are only destroyed when the last connection referencing the table is closed.
 
Multiple users across multiple connections can have local temporary tables with the same name but a global temporary table name has to be unique and if you inspect the name of the global temp table in the Object Explorer, there will be no random numbers suffixed at the end of the table name.
 

Temporary Tables and Stored Procedures

 
Earlier, we learned that a temporary table can only be accessed locally inside the connection that has created it. There is one exception to that rule. When you are creating stored procedures, you can access temporary tables in other connections as well. Let’s create two stored procedures using two different connections. The first stored procedure will insert data into the #myTable table, while the second will select the data from the table.
 
Create a new connection. If you are using SQL Server Management Studio, you can do so by opening a New Query window. Execute the following SQL script there.
  1. Create Procedure spInsertData    
  2.     (@id int,  @Name Varchar(50))    
  3. As    
  4. Begin    
  5.     Insert Into #myTable    
  6.     Values (@id, @ Name)    
  7. End    
We’ve now created a stored procedure that inserts a record into the #myTable temporary table. Notice that this connection did not create the #myTable, yet we are accessing it by inserting a record into it. This is because when creating a stored procedure, you can access temporary tables from a connection other than the one that created the table. If you execute the above query, you will see that SQL Server will not throw any error.
 
Similarly, open a new connection and create the following stored procedure in it.
  1. CREATE PROCEDURE spListData    
  2. AS    
  3. BEGIN    
  4.                 SELECT * FROM #myTable ORDER BY name    
  5. END    
The above-mentioned stored procedure selects all the records from #myTable temporary table. Here again, we are accessing a temporary table inside a connection that did not create the table.
 
Now, here is the tricky part. Though you can access a temporary table inside another connection when creating a stored procedure, you cannot access a temporary table when “executing” a stored procedure inside another connection. To execute a stored procedure that accesses a temporary table, you have to be inside the connection that created the temporary table.
 
Therefore, execute the following queries inside the connection that created the #myTable table.
  1. EXECUTE spInsertData  4, ABC    
  2. Execute spListData     
Here, the first stored procedure inserts a new student record with the name: ABC, id:4 into #myTable table. The second stored procedure selects all the records from the #myTable table in the ascending order of name.


Similar Articles