query

Temporary tables in SQL Server Part 34



Sharing buttons:

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