Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Creating tables

Using temporary tables

Temporary tables are created in the tempdb database. To create a temporary table, you must have create table permission in tempdb. create table permission defaults to the Database Owner.

To make a table temporary, use the pound sign (#) or "tempdb.." before the table name in the create table statement.

There are two kinds of temporary tables:

Tables that can be shared among Adaptive Server sessions
You create a shareable temporary table by specifying tempdb as part of the table name in the create table statement. For example, the following statement creates a temporary table that can be shared among Adaptive Server sessions:
```
create table tempdb..authors
(au_id char(11))
```
```
drop table tempdb..authors
```
Adaptive Server does not change the names of temporary tables created this way. The table exists until the current session ends or until its owner drops it using drop table.
Tables that are accessible only by the current Adaptive Server session or procedure
You create a nonshareable temporary table by specifying a pound sign (#) before the table name in the create table statement. For example:
```
create table #authors 
(au_id char (11)) 
```
The table exists until the current session or procedure ends, or until its owner drops it using drop table.

If you do not use the pound sign or "tempdb.." before the table name, and you are not currently using tempdb, the table is created as a permanent table. A permanent table stays in the database until it is explicitly dropped by its owner.

This statement creates a nonshareable temporary table:

create table #myjobs 
(task char(30), 
start datetime, 
stop datetime, 
notes varchar(200))

You can use this table to keep a list of today's chores and errands, along with a record of when you start and finish, and any comments you may have. This table and its data will automatically be deleted at the end of the current work session. Temporary tables are not recoverable.

You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them. You can use a user-defined datatype when creating a temporary table only if the datatype exists in tempdb..systypes.

To add an object to tempdb for the current session only, execute sp_addtype while using tempdb. To add an object permanently, execute sp_addtype in model, then restart Adaptive Server so model is copied to tempdb.

Ensuring that the temporary table name is unique

To ensure that a temporary table name is unique for the current session, Adaptive Server:

Truncates the table name to 13 characters, including the pound sign (#)
Pads shorter names to 13 characters, using underscores (_)
Appends a 17-digit numeric suffix that is unique for an Adaptive Server session

The following example shows a table created as #temptable and stored as #temptable___00000050010721973:

use pubs2 
go 
create table #temptable (task char(30)) 
go 
use tempdb 
go 
select name from sysobjects where name like
    "#temptable%" 
go

name 
------------------------------ 
#temptable___00000050010721973 
 
(1 row affected)

Manipulating temporary tables in stored procedures

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

Temporary tables with names beginning with "#"

Temporary tables with names beginning with "#" that are created within stored procedures disappear when the procedure exits. A single procedure can:

Create a temporary table
Insert data into the table
Run queries on the table
Call other procedures that reference the table

Since the temporary table must exist in order to create procedures that reference it, here are the steps to follow:

Use create table to create the temporary table.
Create the procedures that access the temporary table, but do not create the procedure that creates the table.
Drop the temporary table.
Create the procedure that creates the table and calls the procedures created in step 2.

tables with names beginning with tempdb..

You can create temporary tables without the # prefix, using create table tempdb..tablename from inside a stored procedure. These tables do not disappear when the procedure completes, so they can be referenced by independent procedures. Follow the steps above to create these tables.

Warning!

Create temporary tables with the "tempdb.." prefix from inside a stored procedure only if you intend to share the table among users and sessions. Stored procedures that create and drop a temporary table should use the # prefix to avoid inadvertent sharing.

General rules on temporary tables

Temporary tables with names that begin with # are subject to the following restrictions:

You cannot create views on these tables.
You cannot associate triggers with these tables.
You cannot tell which session or procedure has created these tables.

These restrictions do not apply to shareable, temporary tables created in tempdb.

Rules that apply to both types of temporary tables:

You can associate rules, defaults, and indexes with temporary tables. Indexes created on a temporary table disappear when the temporary table disappears.
System procedures such as sp_help work on temporary tables only if you invoke them from tempdb.
You cannot use user-defined datatypes in temporary tables unless the datatypes exist in tempdb; that is, unless the datatypes have been explicitly created in tempdb since the last time Adaptive Server was restarted.
You do not have to set the select into/bulkcopy option on to select into a temporary table.


	Expand Search