MS SQLServer
SQL Server supports a temporary table. The temporary table is the table that is starting with a well number (#). If the temporary table is not removed when the user is disconnected, SQL Server will automatically remove the temporary table. The temporary table is not stored in the current database, but is stored in the system database Tempdb.
There are two types of temporary tables:
Local temporary table: The name of the local temporary table is headed in a single number symbol (#); they are only visible to the current user connection; when the user is disconnected from the Microsoft SQL Server 2000 instance, it is deleted.
Global Temporary Table: The name of the global temporary table is headed by mathematical symbols (##), and it is visible to any user after being created. If these tables are not explicitly removed before the connection to the global temporary table is disconnected, then all other tasks stopped to reference them, these tables are removed. When the connection to the global temporary table is created, the new task can no longer reference them. The current statement is completed, the association between tasks and tables is removed; it is usually, as long as the connection disconnection of the global temporary table is created, the global temporary table is removed.
For example, if you create a table named Employees, anyone can use the table as long as you have secure permissions using the table in the database unless it has been deleted. If you create a local temporary table named #Employees, you can remove the table when you execute the table and when it is disconnected. If you create a global temporary table named ## Employees, any of the users in the data table can perform operations. If the table does not use other users after you create, the table is deleted when you disconnect the connection. If the table uses other users after you create, SQL Server deletes the table after all users are disconnected.
Many of the traditional uses of the temporary table can be replaced by variables with Table data types.
Oracle
Oracle supports a temporary table. The temporary table is used to save the intermediate results during the transaction or session. The data saved in the temporary table is only visible to the current session, and any session cannot see the data of other sessions, even after the current session commit data is also visible. Multi-user parallel is not a problem, and a session never blocks another session to use a temporary table. Even if you lock the temporary table, a session does not block other sessions to use temporary tables. The temporary representation is much less than the REDO generated by the normal table, however, since the temporary table must generate UNDO information containing data, a certain number of REDO logs are generated.
The temporary table will allocate space from the user's temporary table space, or if access from the program with the definition right, use the program owner's temporary table space. The global temporary table actually only the template of the table itself. The behavior of creating a temporary table does not include allocation of the storage space, nor includes the assignment of initial. Therefore, when a session is first placed in a temporary table, this session will be created. Since each session gets your own temporal segment, each user may allocate space in different tablespaces. User1's default temporary table space is TEMP1, his temporary table will allocate space from TEMP1, and the USER2's default temporary table space is TEMP2, and his temporary table will allocate space from TEMP2.
The temporary table is only created in each database, and it is not necessary to create during each stored procedure. The temporary table always exists unless he manually deletes him. The temporary table is used as an object in the data dictionary and is always empty until the session is placed in the data. Oracle allows you to create a view and store procedure based on a temporary table.
The temporary table can be based on a session, or it can be based on a transaction. The on commit preserve ROWS clause makes the temporary table a session-based mode. The rows will be left in this table until these rows are deleted from physically from the session or via DELETE or TRUNCATE. The on commit delete rows clause makes the temporary table a transaction-based mode. The line disappears when the session is submitted. The automatic clearance process of this temporary table does not have additional overhead. In Oracle, the temporary table required by the application should be created when the program is installed instead of being created when the program is running. (This is different from MS SQLSERVER or SYBASE)
In any database, a disadvantage of a temporary table is that the fact that the optimizer does not have a real statistical function in the temporary table. However, in Oracle, a series of better statistical guess can be set in the temporary table via the dbms_stats package.
DB2
You can define a temporary table using the DECLARE GLOBAL TEMPORARY TABLE statement. The temporary table of DB2 is based on sessions and is isolated between sessions. At the end of the session, the data of the temporary table is deleted, and the temporary table is implicitly removed. The definition of the temporary table will not appear in syscat.tables
Below is an example of defining a temporary table:
Declare Global Temporary Table GBL_TEMP
Like EMPLTABL
On Commit Delete Rows
NOT Logged
IN USR_TBSP
This statement creates a temporary table named GBL_TEMP. Define the names of the columns used by this user temporary table and the names and descriptions of the columns of EMPLTABL are identical. Implicit definitions include only column names, data types, can be empty features and list default value attributes. All other column properties are not defined, including unique constraints, external keyword constraints, triggers, and indexes. When performing a CommIT operation, if the WITH HOLD cursor is not opened to the table, all the data in the table is deleted. The changes to the user temporary table are not recorded. The user temporary table is placed in the specified user temporary table space. This table space must exist, otherwise the declaration of this table will fail.
Household defined temporary table does not support:
Lob type column (or LOB-based single-value type column) User-defined type column Long Varchar column
Datalink column
