Temporary tables in SQL Server Part 34 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to premium technologies I
am banker despot 34 of signal server in
this session we'll learn about what
temporary tables are the types of
temporary tables and the difference
between them so what are temporary
tables temporary tables are very similar
to the permanent tables permanent tables
get created in the database we specify
and they remain in that database
permanently until we delete or drop them
on the other hand temporary tables get
created in the temte B and are
automatically deleted when they are no
longer used in sequel server there are
two types of temporary tables local
temporary tables and global temporary
tables now what are permanent tables and
let's say if we are working with the
sample database and when we expand
tables folder all the tables that you
see here are permanent tables and we use
Create table table name command to
create these permanent tables so how do
we create temporary tables now to create
a temporary table we use the exact same
create table command that we used to
create permanent tables except that if
you are creating a local temporary table
you prefix the name of the table with a
single hash symbol so the single hash
symbol indicates that this person
details is a temporary table the rest of
the syntax is very much similar to how
we create a permanent table alright
so this create table statement creates
this temporary table this insert
statements populate data into the
temporary table and finally we are
selecting data from the temporary table
so let's look at an example of that so
create table let's execute this create
statement so obviously it should create
that temporary table let's insert some
data into that plus f5 and let's select
the data from it so look at this this
temporary table got created we are able
to insert data into it and retrieve data
from that fine
now how do I check if the local
temporary table is created successfully
now look at this when we create a table
for example let's say I'm creating this
person details table now if I remove
that hash symbol from the name then it
is as if I'm creating this permanent
table okay and I am executing this query
in the context of the sample database so
when we execute this the table gets
created okay so so the table got created
now if we refresh the sample database
here and expand the tables folder look
at this the person details table is
there and this table stays there until
we permanently delete them delete it
okay but when I created this hash person
details temporary table where did this
table get created okay we know that from
the first slide we understood temp
tables are created in the temp dB so we
should be looking for local temporary
tables in the tempie beii database so we
expand system databases and then temp DB
and then the temporary tables folder
within the temp TB refresh the folder
and look at this we see the table that
we have just created the local temporary
table okay so that's one way graphically
using the object Explorer window the
other way is you can actually write this
query if you look at this there is a
query here we are querying this is
object system table that's present in
the temp dB
so all the database objects that we
create like stored procedures tables
functions views etc all this will be
automatically inserted into this objects
table so so we are querying that table
to find is there a local temporary table
with this name called person details
okay so let's execute that query to kill
to select the name of the table I mean
that we have created the temporary table
so when I plus f5 look at this we get
that temporary table and look at the way
I am writing this query I am saying
select name column from temme DB dot dot
says objects table where name like I'm
using the like keyword I am doing it
parish in here because when I created
this person details table using this
create statement you know person details
is the name of the table so hash person
details but then look at that sequel
server has actually appended some random
numbers at the end of the table name and
there is a reason behind why sequel
server is doing that which we understand
just in a bit but always keep in mind
when you are writing a query to retrieve
the local table I mean the local
temporary table from the sis objects
system table then you have to use the
like keyword if you do an exact match
using equals and if you remove the
wild-card at the end look at what's
going to happen it wouldn't return the
result because there is no table in this
is objects table with person details
alone you have some random numbers at
the end of the name of that temporary
table that's why you will have to use
the like keyword okay and that returns
the name of the temporary table so there
are two ways to check if the local
temporary table is created one as
graphically using the object Explorer
window but you will have to go into the
Tempe B database and then expand
temporary tables folder and the other
way is to obviously you write the query
against the Caesar objects temporary I
mean system table okay now another very
important point to keep in mind a local
temporary table is available only for
the connection that has created the
table okay look at this
now who created this local temporary
table this person details this query
editor window so this window that you
see here okay let's call this as first
connection window now when I click this
new query button here on the top it's as
if I'm opening a new connection to the
sequel this is another connection okay
so let's call this second connection
window so now if you look at these two
windows this is first connection this is
second connection who created this
person details temporary table first
connection created it and it's a local
temporary table how do we know it's a
local temporary table because it has got
single hash symbol in its name okay and
if you remember from the slide a look
temporary table is available only for
the connection that has created the
table
so since connection one has created this
table I am able to retrieve the I am
able to access that table that table is
available for connection one but second
connection when I try to copy this query
and try to execute that in the second
connection window I will not be able to
do that look at this I get an error
stating invalid object name hash person
details
so the second connection is not able to
find that hash person details why
because local temporary tables are only
available for the connection that has
created that local temporary table which
is very important and the local
temporary table is automatically dropped
when the connection that has created it
is closed
so here connection one created the local
temporary table so obviously when this
connection is closed okay when I close
this this temporary table will be
automatically dropped now let me refresh
this I close the first connection window
let's refresh the temporary tables
folder look at this the table is gone
it's automatically dropped okay so
that's another important point to keep
in mind the local temporary table is
automatically drop when the connection
that has created it is closed okay now
if the user wants to explicitly drop the
temporary table he can also do that at
any time using the drop table and
temporary local temporary table name it
will automatically drop that okay now
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
stored procedure execution so if you
look at this example here we have a
stored procedure called SP create local
temp table and what is this procedure
doing it's creating a table called hash
percent details local temporary table
populating that with some data and
selecting data back from that okay so
when I execute this table what's going
to happen creates this temporary table
populace that returns the data back to
me and immediately drops the temporary
table so if the temporary table is
created in the
inside the stored procedure it gets
dropped automatically upon the
completion of the stored procedure
execution so let's look at a practical
example of that so we have here the
stored procedure which is exactly what
we have seen in the slide let's execute
this so it creates the stored procedure
now when I execute the stored procedure
so let's say execute and copy the name
of the stored procedure so when I
execute the stored procedure what's
going to happen it's going to create
this temporary table populate that and
then retrieve data from that and
immediately destroy the temporary table
called hash percent details so when I
execute this I get the data but
immediately if I just copy that query
and then execute that again look at this
it says invalid object named hash
percent details so outside the context
of that stew upon the completion of
execution of that stored procedure the
the temporary table which that stood
procedure has created is no longer
available it's immediately destroyed
upon the completion of execution of that
stored procedure okay now 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 1 and user 2 and both create
a local temporary table let's say hash
percent details each user will get his
own version of you know hash percent
details temporary table let's look at an
example okay let's copy this code once
again let's copy this okay and let's say
this is first connection window and
let's say this is second connection now
obviously when I execute let's copy this
and when I execute this so obviously one
person details table is created so when
i refresh this person details table is
created for first connection and when I
go into the second connection and when I
execute this code again and creating
another local temporary table for the
second connection window for the second
user maybe and now i refresh this
temporary tables look at this I get two
percent
details hash person details local
temporary tables but if you look at the
end look at the name they have caught a
different you know random number at the
end
okay so sequel 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 local temporary table
with the same name as that of the other
user you still have no problem because
single server
ran a no appends random number which is
basically used to differentiate between
the different tables created by you know
created across different connections
alright so Global temporary tables to
create a Google I mean a global
temporary table basically is very much
similar to a lake local temporary table
except that it has got two-pound symbols
in its name okay so instead of one
prefix and on one hash sign you can
actually prefix that with two hash signs
so let's prefix that with two hash signs
and let's give it a name called employee
details for example okay so when I
create this table f5 what happens this
employee details table is created as a
global temporary table why because we
have to pound two hash symbols in its
name and if you reference this temporary
tables window look at that
I get hash employee details table hash
hash employee details and now when I say
select star from that particular table
which is hash hash employee details I am
able to select that here in the second
connection window and if I do the same
thing in the first connection window I
will still be able to do that because
look at this ID name there is no data so
we don't see any rows there but still
I'm able to access that table so global
temporary tables are prefixed with two
pound signs and they are visible for all
the connections okay and these global
temporary tables are only destroyed when
the last connection referencing the
table is closed okay and multiple users
across multiple connections we have just
seen that
multiple users across multiple
connections can help you know local
temporary tables with the same name okay
but a global temporary table name has to
be unique and if you inspect the name of
the global temporary table in the object
Explorer window look at this this hasn't
got that random number which local
temporary tables has gone okay so who
created this global temporary table this
global temporary table is created by the
second connection window now let me try
to create the same global temporary
table from the first connection and
let's see what's going to happen so when
we execute this query look at this I get
an error message saying there is already
an object named with hash has employee
details so obviously it's not possible
to duplicate the names of global
temporary tables across different users
in different connections but that's
possible with local temporary tables
okay and finally this is one of the
common interview question that is asked
what is the difference between local
temporary tables and global temporary
tables and if you have been following
along until now it's very clear local
temporary tables are prefixed with
single pound symbol whereas Global
temporary Buhl's are prefixed with two
hash symbols okay and sequel server
happens some random numbers at the end
of the local temporary table name
whereas this is not done for the global
temp tables okay and local temporary
tables are only visible to the session
of the sequel server which has created
it
whereas Global temporary tables are
visible across all sessions all
connections all users and 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 then referencing the
global tempt a below the
so these are some of the common
differences between local and global
temporary tables on this slide you can
find resources for asp.net c-sharp
interview questions if you want to
receive email alerts when I upload new
videos please subscribe to my channel
that's it for today thank you for
listening have a great day