YT YouTube2Text

YouTube2Text → Transcripts → Temporary tables in SQL Server Part 34

Temporary tables in SQL Server Part 34 — Transcript

by kudvenkat · 2,521 words · 388 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to presume Technologies I
0:02am wanker this is part 34 of SQL server
0:05in this session we'll learn about what
0:07temporary tables are the types of
0:09temporary tables and the difference
0:11between them
0:12so what are temporary tables temporary
0:14tables are very similar to the permanent
0:16tables permanent tables get created in
0:19the database we specify and they remain
0:21in that database permanently until we
0:23delete or drop them on the other hand
0:26temporary tables get created in the
0:28tempdb and are automatically deleted
0:30when they are no longer used in SQL
0:33Server there are two types of temporary
0:35tables local temporary tables and Global
0:38temporary tables
0:39now what are permanent tables now let's
0:42say if we are working with the sample
0:44database and when we expand tables
0:46folder all the tables that you see here
0:48are permanent tables
0:51and we use create table table name
0:53command to create this permanent tables
0:55so how do we create temporary tables now
0:59to create a temporary table we use the
1:01exact same create table command that we
1:04use to create permanent tables except
1:06that if you are creating a local
1:08temporary table you prefix the name of
1:11the table with a single hash symbol so
1:14the single hash symbol indicates that
1:16this person details is a temporary table
1:18the rest of the syntax is very much
1:20similar to how we create a permanent
1:23table
1:24all right so this create table statement
1:27creates this temporary table this insert
1:30statements populate data into the
1:32temporary table and finally we are
1:34selecting data from the temporary table
1:36so let's look at an example of that so
1:39create table let's execute this create
1:42statement so obviously it should create
1:44that temporary table let's insert some
1:47data into that
1:48press F5
1:50and let's select the data from it so
1:52look at this this temporary table got
1:54created we are able to insert data into
1:56it and retrieve data from that fine
2:02now how do I check if the local
2:04temporary table is created successfully
2:06now look at this when we create a table
2:08for example let's say I'm creating this
2:10person details table now if I remove
2:12that hash symbol from the name then it
2:15is as if I'm creating this permanent
2:17table okay and I'm executing this query
2:20in the context of the sample database so
2:22when we execute this the table gets
2:25created okay so so the table got created
2:29now if we refresh the sample database
2:31here
2:32and expand the tables folder look at
2:34this the person details table is there
2:37and this table stays there until we
2:40permanently delete that delete it okay
2:42but when I created this hash person
2:45details temporary table where did this
2:49table get created okay we know that from
2:52the first slide we understood temp
2:54tables are created in the tempdb so we
2:57should be looking for local temporary
2:58tables in the tempdb database so we
3:02expand system databases and then temp DB
3:05and then the temporary tables folder
3:07within the tempdb refresh the folder and
3:10look at this we see the table that we
3:13have just created the local temporary
3:15table
3:16okay so that's one way graphically using
3:19the object Explorer window the other way
3:21is you can actually write this query if
3:23you look at this there is a query here
3:25we are querying the sys object system
3:28table that's present in the tempdb so
3:30all the database objects that we create
3:32like stored procedures tables functions
3:34views Etc all this will be automatically
3:37inserted into this objects table
3:40so so we are querying the table to find
3:43is there a local temporary table with
3:46this name called person details okay so
3:49let's execute that query to kill to
3:51select the name of the table I mean that
3:54we have created the temporary table so
3:56when I press F5 look at this we get that
3:58temporary table and look at the way I'm
4:00writing this query I'm saying select
4:02name column from tempdb dot dot
4:06sysobjects table
4:07where name like I'm using the like
4:11keyword I'm doing a comparison here
4:13because when I created this person
4:15details table using this create
4:17statement you know person details is the
4:20name of the table so hash person details
4:22but then look at that SQL server has
4:25actually appended some random numbers at
4:28the end of the table name and there is a
4:30reason behind why SQL Server is doing
4:32that which we understand just in a bit
4:34but always keep in mind when you are
4:36writing a query to retrieve the local
4:39table I mean the local temporary table
4:41from the sys objects
4:43system table then you have to use the
4:45like keyword if you do an exact match
4:48using equals and if you remove the wild
4:51card at the end look at what's going to
4:53happen it wouldn't return the result
4:55because there is no table in this is
4:58object stable with person details alone
5:00you have some random numbers at the end
5:02of the name of the temporary table
5:04that's why you will have to use the like
5:07keyword
5:10okay and that Returns the name of the
5:12temporary table so there are two ways to
5:14check if the local temporary table is
5:16created one is graphically using the
5:18object Explorer vendor but you will have
5:20to go into the tempdb database and then
5:22expand temporary tables folder and the
5:24other way is to obviously you write the
5:26query against the sys object's temporary
5:29I mean system table okay now another
5:32very important point to keep in mind a
5:34local temporary table is available only
5:36for the connection that has created the
5:38table okay look at this now who created
5:41this local temporary table this person
5:44details this query editor window so this
5:47window that you see here okay let's call
5:50this as first connection window
5:55now when I click this new query button
5:58here on the top it's as if I'm opening a
6:00new connection to the SQL this is
6:02another connection okay so let's call
6:05this second connection window
6:08so now if you look at these two windows
6:11this is first connection this is second
6:12connection who created this person
6:14details temporary table first connection
6:16created it and it's a local temporary
6:19table how do we know it's a local
6:20temporary table because it has got
6:22single hash symbol in its name okay and
6:24if you remember from the slide a local
6:27temporary table is available only for
6:28the connection that has created the
6:30table so since
6:31connection 1 has created this table I am
6:34able to retrieve the I'm able to access
6:36that table that table is available for
6:39connection one but second connection
6:41when I try to copy this query and try to
6:44execute that in the second connection
6:45window I will not be able to do that
6:48look at this I get an error stating
6:49invalid object name hash person details
6:52so the second connection is not able to
6:55find that hash person details why
6:57because local temporary tables are only
6:59available for the connection that has
7:01created that local temporary table which
7:04is very important and a local temporary
7:06table is automatically dropped when the
7:09connection that has created it is closed
7:11so here connection one created the local
7:14temporary table so obviously when this
7:16connection is closed okay when I close
7:19this
7:21this temporary table will be
7:23automatically dropped now let me refresh
7:25this I close the first connection window
7:27let's refresh the temporary tables
7:29folder look at this the table is gone
7:31it's automatically dropped
7:34okay so that's another important point
7:36to keep in mind the local temporary
7:37table is automatically drop when the
7:39connection that has created it is closed
7:42okay now if the user wants to explicitly
7:45drop the temporary table he can also do
7:47that at any time using the drop table
7:50and temporary local temporary table name
7:53it will automatically drop that
7:56okay
7:58now another important point to keep in
8:00mind is that
8:02if you create a temporary table as part
8:06of a stored procedure then that
8:08temporary table gets dropped
8:10automatically upon the completion of
8:12stored procedure execution so if you
8:14look at this example here we have a
8:16stored procedure called SP create local
8:17temp table and what is this procedure
8:19doing it's creating a table called hash
8:21person details local temporary table
8:24populating that with some data and
8:26selecting data back from that okay so
8:28when I execute this table what's going
8:30to happen creates this temporary table
8:32populace that Returns the data back to
8:34me and immediately drops the temporary
8:36table so if the temporary table is
8:39created in the
8:40inside the stored procedure it gets
8:42dropped automatically upon the
8:44completion of the stored procedure
8:45execution so let's look at a practical
8:47example of that so we have here the
8:50stored procedure which is exactly what
8:51we have seen in the slide let's execute
8:54this
8:55so it creates the stored procedure now
8:59when I execute the stored procedure so
9:02let's say execute
9:03copy the name of the stored procedure so
9:06when I execute the stored procedure
9:07what's going to happen it's going to
9:09create this temporary table populate
9:12that and then retrieve data from that
9:14and immediately destroy the temporary
9:16table called hash percent details so
9:18when I execute this I get the data but
9:21immediately if I just copy that query
9:26and then execute that again look at this
9:28it says invalid object name hash person
9:30details so outside the context of that
9:33upon the completion of execution of that
9:36stored procedure the the temporary table
9:38which that stored procedure has created
9:40is no longer available it's immediately
9:42destroyed upon the completion of
9:44execution of that stored procedure okay
9:47now it is also possible for different
9:50connections to create a local temporary
9:51table with the same name for example if
9:53there are two users user one and user
9:55two and both create a local temporary
9:57table let's say hash percent details
10:00each user will get his own version of
10:03you know hash person details temporary
10:06table let's look at an example
10:08okay let's copy this code once again
10:15let's copy this
10:16okay and let's say this is first
10:18connection window
10:21and let's say this is second connection
10:22now obviously when I execute let's copy
10:25this
10:27and when I execute this so obviously one
10:30person details table is created so when
10:32I refresh this
10:35person details table is created for you
10:37first connection and when I go into the
10:40second connection
10:42and when I execute this code again I'm
10:44creating another local temporary table
10:46for the second connection window for the
10:48second user Maybe
10:49and now I refresh this temporary tables
10:52look at this I get two percent details
10:54uh hash person details local temporary
10:57tables but if you look at the end look
10:59at the name they have got a different
11:01you know random number at the end
11:04okay so SQL Server uses these random
11:06numbers to differentiate between the
11:08temporary tables that it creates for
11:10different users if they accidentally
11:12happen to create a temporary table with
11:14the same name a local temporary table
11:16with the same name as that of the other
11:17user you still have no problem because
11:20SQL Server ran you know appends a random
11:23number which is basically used to
11:25differentiate between the different
11:27tables created by you know created
11:29across different connections
11:33all right so Global temporary tables to
11:37create a Google I mean a global
11:38temporary table basically is very much
11:39similar to a local temporary table
11:41except that it has got two pound symbols
11:44in its name okay so instead of one
11:48prefix you know one hash sign you can
11:52actually prefix that with two hash signs
11:54so let's prefix that with two hash signs
11:57and let's give it a name called employee
11:59details for example
12:02okay so when I create this table F5 what
12:05happens this employee details table is
12:07created as a global temporary table why
12:10because we have two pound uh two hash
12:13symbols in its name and if you refresh
12:15this temporary tables vendor look at
12:17that I get hash employee details table
12:20hash employee details and now when I say
12:25select star from that particular table
12:27which is Hash employee details
12:32I am able to select that here in the
12:34second connection window
12:36and if I do the same thing in the first
12:38connection window I will still be able
12:40to do that because look at this ID name
12:43there's no data so we don't see any rows
12:45there but still I'm able to access that
12:47table so Global temporary tables are
12:49prefixed with two pound signs and they
12:52are visible for all the connections
12:54okay and these Global temporary tables
12:57are only destroyed when the last
12:59connection referencing the table is
13:01closed okay and multiple users across
13:04multiple connections we have just seen
13:06that multiple users across multiple
13:08connections can have you know local
13:10temporary tables with the same name okay
13:12but a global temporary table name has to
13:15be windy and if you inspect the name of
13:17the global temporary table in the object
13:19Explorer window look at this this hasn't
13:21got that random number which local
13:23temporary tables has got
13:25okay so who created this Global
13:27temporary table This Global temporary
13:29table is created by the second
13:31connection window now let me try to
13:33create the same Global temporary table
13:34from the first connection and let's see
13:37what's going to happen so when we
13:39execute this query look at this I get an
13:41error message saying there is already an
13:43object named with hash employee details
13:46so obviously it's not possible to
13:48duplicate the names of global temporary
13:51tables across different users and
13:52different connections but that's
13:53possible with local temporary tables
14:00okay and finally this is one of the
14:02common interview question that is asked
14:04what is the difference between local
14:06temporary tables and Global temporary
14:07tables and if you have been following
14:09along until now it's very clear local
14:12temporary tables are prefixed with
14:14single pound symbol whereas Global
14:16temporary tables are prefixed with two
14:18hash symbols okay and SQL Server happens
14:21some random numbers at the end of the
14:24local temporary table name whereas this
14:26is not done for the Global Temp tables
14:29okay and local temporary tables are only
14:32visible to the session of the SQL Server
14:34which has created it whereas Global
14:36temporary tables are visible across all
14:39sessions all connections all users
14:41and local temporary tables are
14:43automatically dropped when the session
14:45that created the temporary table is
14:46closed whereas Global temporary tables
14:49are destroyed when the last connection
14:50that is referencing the referencing the
14:53Global Temp table is closed so these are
14:56some of the common differences between
14:57local and Global temporary tables
15:02on this slide you can find resources for
15:04asp.net C sharp interview questions if
15:07you want to receive email alerts when I
15:09upload new videos please subscribe my to
15:12my channel that's it for today thank you
15:14for listening have a great day

About this transcript

This page contains the full transcript of Temporary tables in SQL Server Part 34 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,521 words across 388 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.