You can use a variety of techniques to work with sets of records in SQL, but
I always find myself coming back to temporary tables!
What are Temporary Tables?
Suppose that you want to take a table of films (or movies for most
of the world) and extract ones which won more than 5 Oscars for subsequent
processing. Here's one way to do this:
SELECT
FilmName,
FilmOscarWins
INTO
#GoodFilms
FROM
tblFilm
WHERE
FilmOscarWins > 5
SELECT * FROM #GoodFilms
ORDER BY FilmOscarWins DESC
The SQL code puts the records into a table with the name #GoodFilms.
The # prefix shows that this is a temporary table.
A tempoary table is released at the end of the SQL which created it (in
theory). In practice, they seem to hang around for a bit longer than this,
and it's good practice to drop temporary tables first before recreating them
(see later in this blog for how to do this).
The rest of this blog looks at temporary tables in more detail, showing where
they are stored, how to drop them and the pros and cons of using them.
You don't need to know where temporary tables are stored, but it's all good
background! Consider this SQL:
USE MOVIES
GO
CREATE TABLE #BestMoviesEver(
MovieId int IDENTITY(1,1) PRIMARY KEY,
MovieName varchar(50),
Rating int
)
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 )
SELECT * FROM #BestMoviesEver
Here's what you get when you run this:
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:
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.
There are two ways to ensure that a temporary table doesn't exist before you
create it:
- Try dropping it, and catch any error which occurs.
- Look for any table in the TEMPDB database
with the right name, and if found, delete it.
Of the two methods, the second one is more difficult to understand but
probably better: with the first method, you run the risk of trapping the wrong
error!
Method 1 - Drop and Catch
You can delete a temporary table using standard SQL syntax for TRY
and CATCH:
BEGIN TRY
DROP TABLE #BestMoviesEver
Print 'Deleted table'
END TRY
BEGIN CATCH
PRINT 'No table to delete'
END CATCH
Here the PRINT statements are included just to show what
happens when you run the SQL. After deleting the temporary table, you
could then try to create it:
PRINT ''
SET NOCOUNT ON
CREATE TABLE #BestMoviesEver(
MovieId int IDENTITY(1,1) PRIMARY KEY,
MovieName varchar(30),
Rating int
)
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 )
SELECT * FROM #BestMoviesEver
Running this SQL twice would give the following output the second time:
Here we are running the SQL for a second time with the answers output to text. The
PRINT statement has reported that we deleted the previous incarnation of the temporary table successfully (ie it must have already existed).
Method 2 - Use Object_Id
A more complicated approach is to look in the system tables for a temporary
table called #BestMoviesEver, and delete it if found:
IF Object_id('tempdb.dbo.#BestMoviesEver', 'U') IS NOT NULL
BEGIN
PRINT 'Deleted table'
DROP TABLE #BestMoviesEver
END
If you want to know why the Object_id function works (and
you probably don't!) run the following SQL statement:
SELECT * from
tempdb.sys.objects
This will show a list of all SQL Server objects in the TEMPDB
database:
Part of the listing of objects in the TEMPDB database
All of the objects with type U are user-defined tables!
As our final part of this blog covering training on temporary tables, what are the advantages and disadvantages of using temporary tables?
If you've found this blog useful, consider booking a place on our advanced SQL course (online or classroom, depending on whether you're in the UK or not).
Advantages of Temporary Tables
There are 3 main advantages of using temporary tables in SQL:
Advantage | Notes |
|---|
Simplicity of coding | Temporary tables behave just like normal ones; you can sort, filter and join them as if they were permanent tables. |
Speed | Because SQL Server has less logging and locking overheads for temporary tables (after all, you're the only person who can see or use the temporary table you've created), they execute more quickly. |
Access rights/security | You can create a temporary table and insert, delete and update its records without worrying about whether you have sufficient rights to change data in permanent tables, or whether you might be accidentally doing so. |
Disdvantages of Temporary Tables
The disadvantages of using temporary tables include:
Advantage | Notes |
|---|
Not as fast as table variables | Although using temporary tables is quicker than using permanent ones, there is still an overhead involved compared to using table variables (an alternative technique, which I might blog about some day!). |
Can not update in functions | You can not use INSERT, UPDATE or DELETE statements against temporary tables in user-defined functions (you can with table variables). This is NOT a big disadvantage, however! |
