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.
Tables that can be shared among SAP ASE 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 SAP ASE sessions:
create table tempdb..authors (au_id char(11)) drop table tempdb..authors
SAP ASE 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 SAP ASE 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))
While hash temporary tables exist until the current session or scope is exited, shared temporary tables exist until they are explicitly dropped.
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.