Temporary Tables in Sql Server with Real Time example

In this article, we will learn about temporary tables, the types of temporary tables, and the difference between # and ## temp tables in SQL Server.

What are temporary tables?

Temporary tables are akin to regular tables but exist temporarily on the database server. You can perform database operations such as select, insert, and delete on temporary tables, just like on permanent tables.

Regular or permanent tables are created in the database and persist in the database permanently until we delete or drop them. However, temporary tables are created in the tempDB and are automatically deleted or dropped when they are no longer in use.

In SQL Server, there are two types of temporary tables,

1. Local temporary tables
2. Global temporary tables.

What are the permanent or regular tables?

Now, let’s say we are working with the Demo database. When we expand the tables folder, all the tables that you see here are permanent tables. We use the 'CREATE TABLE' command to create these permanent tables.

How do we create temporary tables?

To create a temporary table, we use the exact same 'CREATE TABLE' command that we use to create permanent tables. The only difference is that if you are creating a local temporary table, you prefix the name of the table with a single '#' symbol.

Create Table #TblUserDetail(Id int,Name varchar(100))

So, the single '#' symbol indicates that TblUserDetail is a temporary table. The rest of the syntax is the same as how we create a regular database table. Therefore, the above 'CREATE TABLE' statement creates a temporary table.

Below are the 'INSERT' statements to populate data into that temporary table. Finally, we select data from the temporary table.

insert into #TblUserDetail values(1,'john')
insert into #TblUserDetail values(2,'mike')
select *from #TblUserDetail

How do I check if the local temporary table is created successfully in our database or not?

Let’s consider I’m creating the #TblUserDetail table. Now, if I remove the '#' symbol from the name, it's as if I’m creating a permanent table. When we execute this query in the context of the Demo database, the table gets created in the database. Now, if we refresh the database and expand the tables folder, we find the TblUserDetail table.

But when I create the #TblUserDetail temporary table, where does this table get created?

We know that temporary tables are created in the tempDB, so we should be looking for a local temporary table in the tempDB database. Therefore, we expand system databases, then tempDB, and then the temporary tables folder within the tempDB.

After refreshing the folder, we see the table that we have just created, the local temporary table.

A local temporary table is available only for the connection that has created the table.

Now, who created the #TblUserDetail local temporary table? The window in the image below where you write the 'CREATE TABLE' command, let’s call this the fast connection window.

Now, when I click the 'New Query' button at the top of SSMS, I am opening a new connection to SQL Server. This is another connection. So, let’s call this the second connection window.

When I attempt to execute that select query (i.e., 'SELECT * FROM #TblUserDetail') in the second connection window, I will not be able to do so. I receive an error stating an invalid object name, '#TblUserDetail'. Therefore, the second connection is unable to find '#TblUserDetail'.

Why? Because local temporary tables are only available for the connection that has created them, which is very important.

Additionally, a local temporary table is automatically dropped when the connection that created it is closed.

However, if the user wants to explicitly drop the temporary table, they can do so at any time using the 'DROP TABLE' statement followed by the local temporary table name. This will automatically drop the table.

How Temp Table works in Stored Procedures?

Another important point to keep in mind is that if you create a temporary table as part of a stored procedure, then that temporary table gets dropped automatically upon the completion of the stored procedure execution.

So, if you look at this example here, we have a stored procedure called 'sp_CreateTempTable'.

Create Procedure sp_CreateTempTable
as
Begin
Create Table #TblLocalUserDetail(Id int, Name nvarchar(20))
insert into #TblLocalUserDetail values(1,'john')
insert into #TblLocalUserDetail values(2,'mike')
Select * from #TblLocalUserDetail
End

What is this procedure doing? It’s creating a table called #TblLocalUserDetail, which is a local temporary table. It then populates that table with some data and selects data back from it.

So, when I execute the above stored procedure, what’s going to happen is that it will create a temporary table, insert data into it, return the data back to you, and immediately drop the temporary table.

If the temporary table is created inside the stored procedure, it gets dropped automatically upon the completion of the stored procedure execution.

Let’s look at a practical example of that. Let’s execute the above query. So it creates the stored procedure.

Now, when I execute the stored procedure, I get the data.

exec sp_CreateTempTable

But immediately, if I just copy that select query and then execute that again. It says invalid object name, #TblLocalUserDetail.

Select * from #TblLocalUserDetail

So, upon the execution of that stored procedure, the temporary table which the stored procedure has created is no longer available. It’s immediately destroyed upon the completion of the execution of that stored procedure.

It is also possible for different connections to create a local temporary table with the same name.

For example, if there are two users, user one and user two, and each would create a local temporary table, let’s say #TblUserDetail, each user will get their own version of the temp table.

Let’s look at an example

Now click the 'New Query' button in SQL Server Management Studio two times. This means we are creating two connection windows.

Let’s copy the code below and paste it into both query windows.

insert into #TblUserDetail values(1,'john')
insert into #TblUserDetail values(2,'mike')
select *from #TblUserDetail

Now, obviously, when I execute the above query from both connection windows, we get two sets of person details stored in #TblUserDetail local temporary tables for both connections.

But if you look at the end, notice that the names have different random numbers appended.

So SQL Server uses these random numbers to differentiate between the temporary tables it creates for different users. If users accidentally create a temporary table with the same name, or a local temporary table with the same name as that of another user, there is still no problem. SQL Server appends a random number to the name, which is used to differentiate between the tables created across different connections.

What is a global temporary table?

So instead of using a single '#' sign as a prefix, you can use two '#' signs. Let’s prefix it with two '#' signs and name it ##TblGlobalUserDetails.

Create Table ##TblGolbalUserDetails(Id int, Name nvarchar(20))
insert into ##TblGolbalUserDetails values(1,'john')
insert into ##TblGolbalUserDetails values(2,'mike')
Select * from ##TblGolbalUserDetails

When we execute the above query, what happens? The TblGlobalUserDetails table is created as a global temporary table because we have two '#' symbols in its name.

• So, global temporary tables are prefixed with two '#' signs and they are visible for all connections. Global temporary tables are only destroyed when the last connection referencing the table is closed.
•