Temporary Tables in SQL - a Training Blog
Part two of a four-part series of blogs

If you want training in how to use temporary tables in SQL, read on! This blog shows how to create temporary tables and how to drop them safely whether or not they exist. Finally, the blog lists the pros and cons of using temporary tables in SQL.

  1. Temporary Tables in SQL Server - a Training Blog
  2. Where Temporary Tables are Stored - TEMPDB Database (this blog)
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY
  4. Pros and Cons of Temporary Tables

This article is part of a complete SQL tutorial.  Another way to learn SQL is to attend a Wise Owl SQL training course.

Posted by Andy Brown on 20 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Where Temporary Tables are Stored - TEMPDB Database

You don't need to know where temporary tables are stored, but it's all good background!  Consider this SQL:

USE MOVIES

GO

-- create a temporary table containing movies

CREATE TABLE #BestMoviesEver(

MovieId int IDENTITY(1,1) PRIMARY KEY,

MovieName varchar(50),

Rating int

)

-- add in 4 movies

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'Watchmen', 9 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Departed', 8 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Matrix', 10 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Sound of Music', 9 )

-- list them back out

SELECT * FROM #BestMoviesEver

Here's what you get when you run this:

Results of running SQL

When you show the records in the temporary table, this is what you get.

 

So where is the temporary table stored?  The answer is in a system database called tempdb:

Diagram showing TEMPDB database

The temporary table just created. The full table name is over 100 characters long, since it contains a unique number attaching it to this particular user of SQL.

 

Knowing this fact helps us make sure that we can drop the temporary table before we try to recreate it, as shown in the next part of this blog.

This blog has 0 threads Add post