Hello friends, today
I am going to talk about one more popular topic in SQL Server i.e. difference
between #temporary table and ##temporary table.
First Difference: -
#temporary table is only available for the current session that created the
#temporary table while ##temporary table is available for all the sessions.
Lets take an example to understand it clearly.
I am going to create
a #temporary table by using below script and understand what is the meaning of
above statement.
Create table #temp(Eid int, Name varchar(100))
Insert into #temp values(1,'Neeraj')
Insert into #temp values(2,'Akshay')
Insert into #temp values(1,'Ankit')
select * from #temp
I have created a #temp table in database “ETL”
and inserted 3 rows into it and now I have written select query to fetch all
the rows from the #temp table and it shows us all the rows of the table as
shown in the below image.
In the below image, I have draw 3 circles and given
numbering to those circles.If you see circle 2, you will see value “54” that
indicates the SPID(session id), now lets create a new session and try to access
#temp table.
To create a new session we will click on “new query” button
which has been encircled by blue color and given no “4”. Let’s write a select
query and try to fetch all the records form the #temp table.
In the above image I have encircled the SPID with red color,
which is “56” and we are getting error message “Invalid object name #temp”, in
the left side of the image we can see that the table is already there in the
temp database.
Similarly if we create ##temp table in session “54” and try
to access same table in session “56” we will be able to access that table. Below is the screen shot for your reference.
We have created ##temp table in session (54) encircled by
red color and let’s try to access same table in session (56) encircled by blue
color.
In the above image we can see that we are able to access
##temp table in the different session.
Second Difference: -
We can create multiple #temporary tables with same name using different
sessions but we cannot create multiple # #temporary tables with same name using
different sessions.
Example:- If I
have created #temp table in session id(54) using below script then I can create
one more #temp table in session(56) with same name. lets see the below example.
We can see in the above image, two tables have been created
with same name in the tempdb. But if we try same example and try to create
##temp table in session (56) we will get error message “There is already an
object named '##temp' in the database.”
Below is the screenshot for your reference.
Third Difference :- #temporary table scope is limited within a
stored procedure while ##temporary table is not limited to stored procedure,
##temporary table created by a stored procedure can be accessed in different
sessions. Lets take an example.
I have created a stored procedure using below query in
session id(54).
Create procedure Usp_Createtable
as
Begin
Create table #temp(Eid int, Name varchar(100))
Insert into #temp values(1,'Neeraj')
Insert into #temp values(2,'Akshay')
Insert into #temp values(1,'Ankit')
select * from #temp
end
Lets execute this stored procedure and see the result.
I have created and executed the stored procedure in same
session id(54) , Now I am going to run select query numbered “2” in the same
session and will get error message “invalid object name #temp”. Because (single
hash or local temporary) table’s scope is limited to the stored procedure.
Lets create same stored procedure for creating ##temporary
table and see the result.
Create procedure Usp_Createtable
as
Begin
Create table ##temp(Eid int, Name varchar(100))
Insert into ##temp values(1,'Neeraj')
Insert into ##temp values(2,'Akshay')
Insert into ##temp values(1,'Ankit')
select * from ##temp
end
execute usp_Createtable
select * from
##temp
I
have executed the stored procedure in session id(54) and if I run the select
query numbered “2” it will also run successfully. If I try to execute stored
procedure once again it will throw an error message stating that “There is
already an object named '##temp' in the database.” And if we try to access same
table in different session it will be accessible.
Similarity:- There is one similarity between #temp and ##temp table
which is mentioned below.
Both type of tables automatically
drop when the connection or session which created the (#temp or ##temp) table
gets closed.
I hope you will like this article.
Please share your thoughts on this topic and feel free to post any question if
you have.
No comments:
Post a Comment