Chapter 8: Creating Databases and 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

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

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 is automatically 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

View this book as PDF