An Oracle application may have to create tables that exist for short periods. The application must ensure that all of the tables that are created for this purpose are dropped at some point. If the application fails to do this, tablespaces can become cluttered and unmanageable.
This is not the case in Microsoft® SQL Server™. SQL Server allows temporary tables to be created. Regardless of the user, these tables are created in the tempdb database. The naming convention used with these tables controls how long they reside within the tempdb database.
| Table name | Description |
|---|---|
| #table_name | Local temporary table, which exists only for the duration of a user session or the procedure that created it. It is dropped automatically when the user logs off or when the procedure that created the table completes. This table cannot be shared among multiple users. No other database users can access this table. Permissions cannot be granted or revoked on this table. |
| ##table_name | Global temporary table, which exists for the duration of a user session or procedure that created it. It is dropped automatically when the last user session referencing it disconnects. This table can be shared among multiple users. All other database users can access this table. Permissions cannot be granted or revoked on this table. |
| tempdb..table_name | This table continues to exist until it is dropped or until SQL Server is restarted. Permissions can be granted and revoked on this table. To create this table, the database user must have CREATE TABLE permission in the tempdb database. |
Indexes can be defined for temporary tables. Views can be defined only on tables created explicitly 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 dropped automatically:
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)
To ensure complete compatibility with an Oracle application, you may not want to use temporary tables. However, you may find that the benefits associated with using them justify a slight revision in program code.