Week 2

Day 10
Temporary Tables and Views

Today I’ll cover temporary tables and views. Temporary tables are just like regular tables, but they cleanly disappear after a certain period of time. Views provide an alternate way of viewing data.

Here is what I’ll discuss today:

•  What is a temporary table? How are they created?

•  What is the difference between a real and temporary table?

•  As strange as it sounds, “permanent” temporary tables.

•  Local and global temp tables.

•  Temporary tables don’t quite warrant a day all to themselves, so I’ll round out the day with a few pages on views.

•  The SQL UNION keyword will be discussed briefly.

What Is a Temporary Table?

Temporary tables persist, as their name implies, for a limited duration of time. There are three types of temporary tables: local, global, and permanent. All temporary tables are created in the tempdb database, no matter where you are when you create them.

Although you didn’t know it, behind the scenes the SQL Server has been creating intermediate work tables to process some of the queries that you have sent. When you use an ORDER BY, and the data you are ordering does not have an index that maintains it in the order you requested the data, the server uses a temporary table for sorting.

Today you will learn to use temporary tables directly.

Local Temp Tables

Local temporary tables last as long as your connection to the server. You log in, create a temporary table, work with it for an hour, and log out. When you log out, your temporary table goes away.

Local temporary tables are created by prefacing their name with a # sign:

create table #t1(id int null)

When you refer to this table, you must also use the pound sign:

select   id
from     #t1

Other users on the server cannot gain access to your table. There are two things preventing others from using your temporary table. First, only the creator’s connection has permissions to access his temporary table: you cannot access it using the same login on a different connection, and even the sa cannot select from it! Second, when you create the temporary table, a 10-digit, hexadecimal identifier is appended to the name in order to distinguish it from other temp tables that have the same name, but are created by different connections. This lets up to 16¹⁰ concurrent users create identically named temp tables—certainly more than enough.

Temp tables, when created, physically reside in tempdb. To see the true name of your table, try this exercise:

1.  Create a temporary table:

create table #MyTempTable(c1 int null)

2.  Using the system table sysobjects in the tempdb database, look at the names of all user tables. If you are on the system with other users, you may find lots of temp tables out there:

select   name
from     tempdb..sysobjects
where    type = ‘U’
/*
      If there are lots of tables,
     add this to the WHERE clause:
     and uid = user_id()
*/

name
------------------------------
#MyTempTable________0000000003

The temporary table you created has a full name 30 characters long, composed of the name you provided at the beginning, a 10-digit identifier on the end, and underscore character in the middle. You can refer to this table by select * from #MyTempTable, but if another user tries this, he will receive an Invalid Object Name error. If another user tries to execute select * from tempdb..#MyTempTable________0000000003, he will receive a SELECT permission denied error.

You cannot adjust permissions on local temporary tables.

---

Warning:  The maximum length of an object’s name is 30 characters. When you define a temporary table, you have only 19 characters available to you: one is used by the pound sign (#), and ten are used by the system to generate a random identifier. If you attempt to create a table 20 to 29 characters in length, you will receive this error message:

Msg 193, Level 15, State 1
The object or column name starting with ‘#ThisTableWontBeCreated’
      is too long.  The maximum length is 20 characters.

I can’t explain why this next example works, but I discovered, quite by accident, that a temporary table exactly 30 characters long will be accepted:

create   table    #ThisLongTableNameWillBeCreate(id    int    not null…)

There is no room left over for a 10-digit identifier, so if another user tries to create a 30-character temp table with the same name, she will receive a This object exists error. I believe this is an “unintended feature” of SQL Server 6.5 and will be fixed eventually. Until it is, be careful not to create temp tables that are 30 characters long.

---