Temporary Tables Usage

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

There are two kinds of temporary tables:

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 SAP ASE so model is copied to tempdb.