An Oracle application might have to create tables that exist for short periods. The application must ensure that all tables created for this purpose are dropped at some point. If the application fails to do this, tablespaces can quickly become cluttered and unmanageable.
Microsoft SQL Server provides temporary table database objects, which are created for just such a purpose. These tables are always created in the tempdb database. The table name determines how long they reside within the tempdb database.
| Table name | Description | |
|---|---|---|
| #table_name | This local temporary table only exists for the duration of a user session or the procedure that created it. It is automatically dropped when the user logs off or the procedure that created the table completes. These tables cannot be shared between multiple users. No other database users can access this table. Permissions cannot be granted or revoked on this table. | |
| ##table_name | This global temporary table also typically exists for the duration of a user session or procedure that created it. This table can be shared among multiple users. It is automatically dropped when the last user session referencing it disconnects. All other database users can access this table. Permissions cannot be granted or revoked on this table. | |
Indexes can be defined for temporary tables. Views can be defined only on tables explicitly created in tempdb without the # or ## prefix. The following example shows the creation of a temporary table and its associated index. When the user exits, the table and index are automatically dropped.
SELECT SUM(ISNULL(TUITION_PAID,0)) SUM_PAID, MAJOR INTO #SUM_STUDENT
FROM USER_DB.STUDENT_ADMIN.STUDENT GROUP BY MAJOR
CREATE UNIQUE INDEX SUM STUDENT IDX ON #SUM STUDENT (MAJOR)
You may find that the benefits associated with using temporary tables justify a revision in your program code.