Temporary Tables in sql server with real time example

Welcome to Appsloveworld Technologies, In this article will learn about what temporary tables are, the types of temporary tables, and the difference between # and ## temp tables in sql server.

what are temporary tables?

Temporary tables are very similar to the regular tables that exist temporarily on the database server. you can perform the database operations select, insert and delete in the temp table as for a Permanent table.

Regular or Permanent tables are created in the database, and they remain in the database permanently until we delete or drop them. But temporary tables are created in the tempDB and are automatically deleted or drop when they are no longer used.

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 if we are working with the Demo database and when we expand the tables folder, all the tables that you see here are permanent tables and we use the “create table” table name 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, except 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 TblUserDetailis a temporary table. The rest of the syntax is the same as how we create a regular database table. So the above create table statement creates a temporary table.

Below insert statements, populate data into that temporary table. and then finally, we are selecting data from the temp table.

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

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

Let’s say I’m creating #TblUserDetail table. Now, if I remove that # symbol from the name, then it is as  I’m creating this 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, you find the TblUserDetail table

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

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

Refresh the folder and look at this. 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? So this window that you see in below image, where you write the create table command, let’s call this as fast connection window.

Now, when I click new query button on the top of SSMS, I am opening a new connection to the SQL.This is another connection. so let’s call this second connection window.

when I try to execute that select query(i. e select *from #TblUserDetail) in the second connection window, I will not be able to do that. I get an error stating an invalid object name, #TblUserDetail. So the second connection is not able to find that #TblUserDetail.

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

And a local temporary table is automatically dropped when the connection that has been created is closed.

Now, if the user wants to explicitly drop the temporary table, he can also do that at any time using the drop table and local temporary table name. It will automatically drop that.

How Temp Table works in Stored Procedures?

Now another important point to keep in mind is that if you create a temporary table as part of a store  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, a local temporary table, populating that with some data and selecting data back from that

so when I execute above store procedure, what’s going to happen it will create temporary table, insert data in it and then returns the data back to you and immediately drops 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 Strood procedure, the temporary table which that 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 would create a local temporary table, let’s say #TblUserDetail, each user will get his own version of the temp table.

Let’s look at an example

Now click the “New Query” button in the SQL server management studio two times. that means we are creating two connection window.
Let’s copy the below code and paste it in both query window.

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 the connection window.
we get two-person details, #TblUserDetail, local temporary tables for both connections.

But if you look at the end, look at the name, they have got a different random number at the end.

So SQL server uses these random numbers to differentiate between the temporary tables that it creates for different users, if they accidentally happen to create a temporary table with the same name and a local temporary table with the same name as that of the other user, you still have no problem because SQL Server then upends a random number, which is basically used to differentiate between the different tables created by across different connections.

what is a global temporary table?

so instead of one prefix and one # sign, you can actually prefix that with two # signs. So let’s prefix that with two # signs and let’s give it a name called ##TblGolbalUserDetails.

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? TblGolbalUserDetails table is created as a global temporary table way because we have two # symbols in its name.

• So global temporary tables are prefixed with two # signs and they are visible for all the connections
• Global temporary tables are only destroyed when the last connection referencing the table is closed.
• Multiple users across multiple connections can help in a local temporary table with the same name. but a global temporary table name has to be unique.
• it’s not possible to duplicate the names of global temporary tables across different users and different connections. But that’s possible with the local template tables.

And finally, this is one of the common interview questions that is asked what is the difference between local temporary tables and global temporary tables?

1. if you have been following along until now, it’s very clear local temporary tables are prefixed with a single # symbol that has global temporary tables are prefixed with two # symbols.
2. SQL Server appends some random numbers at the end of the local temporary table name, whereas this is not done for the global temp tables.
3. local temporary tables are only visible to the session of the SQL which has created it. But as global temporary tables are visible across all sessions, all connections, all users.
4. local temporary tables are automatically dropped and the session that created the temporary table is closed, whereas global temporary tables are destroyed when the last connection that is referencing the referencing, the global temp table is closed.

So these are some of the common differences between local and global temporary tables.

The post Temporary Tables in sql server with real time example appeared first on Software Development | Programming Tutorials.