A Closer Look At Declared Temporary Tables

In Chapter 4, "Working with Databases and Database Objects," we saw that another type of table that is commonly used is a declared temporary table. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can be used only by the application that creates them-and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name.

or

Tip 

Before a declared global temporary table can be defined and used, a user temporary table space must exist for the database the application that will be defining the declared global temporary table will be working with.

Whereas base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement. The basic syntax for this statement is:

 DECLARE GLOBAL TEMPORARY TABLE [TableName] ([ColumnDefinition] ,...) <ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS> <NOT LOGGED <ON ROLLBACK DELETE ROWS |   ON ROLLBACK PRESERVE ROWS>> <WITH REPLACE> <IN [TablespaceName]> 

or

 DECLARE GLOBAL TEMPORARY TABLE [TableName] LIKE [SourceTable | SourceView] <[INCLUDING | EXCLUDING] COLUMN DEFAULTS> <[INCLUDING | EXCLUDING] IDENTITY COLUMN ATTRIBUTES> <ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS> <NOT LOGGED <ON ROLLBACK DELETE ROWS |   ON ROLLBACK PRESERVE ROWS>> <WITH REPLACE> <IN [TablespaceName]> 

or

 DECLARE GLOBAL TEMPORARY TABLE [TableName] AS ([SELECTStatement]) DEFINITION ONLY <ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS> <NOT LOGGED <ON ROLLBACK DELETE ROWS |   ON ROLLBACK PRESERVE ROWS>> <WITH REPLACE> <IN [TablespaceName]> 

where:

TableName	Identifies the name that is to be assigned to the global temporary table to be created.
ColumnDefinition	Identifies one or more columns to be included in the global temporary table definition.
SourceTable	Identifies the name of an existing table whose structure is to be used to define the global temporary table to be created.
SourceView	Identifies the name of an existing view whose structure is to be used to define the global temporary table to be created.
SELECTStatement	Identifies a SELECT SQL statement that, when executed, will produce column definition data that will be used to define the global temporary table to be created.
TablespaceName	Identifies the user temporary table space in which the global temporary table is to be created.

The basic syntax used to define a column is:

 [ColumnName][DataType] <NOT NULL> <WITH DEFAULT <[DefaultValue] | CURRENT DATE | CURRENT TIME | CURRENT TIMESTAMP | NULL>> 

or

 [ColumnName][DataType] GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY <(   <START WITH [1 | StartingValue]>   <INCREMENT BY [1 | IncrementValue]>   <NO MINVALUE | MINVALUE [MinValue]>   <NO MAXVALUE | MAXVALUE [MaxValue]>   <NO CYCLE | CYCLE>   <CACHE 20 | NO CACHE | CACHE [CacheSize]>   <NO ORDER | ORDER> )> 

or

 [ColumnName][DataType] GENERATED [ALWAYS | BY DEFAULT] AS (Expression) 

where:

ColumnName	Identifies the unique name to be assigned to the column that is to be created.
DataType	Identifies the data type (built-in or user-defined) that is to be assigned to the column to be created. The data type specified determines the kind of data values that can be stored in the column. Table 6-4 contains a list of the data type definitions that are valid.
DefaultValue	Identifies the value that is to be provided for the column in the event no value is supplied when an insert or update operation is performed against the global temporary table.
StartingValue	Identifies the first value that is to be assigned to the identity column to be created.
IncrementValue	Identifies the interval that is to be used to calculate each consecutive value that is to be assigned to the identity column to be created.
MinValue	Identifies the smallest value that can be assigned to the identity column to be created.
MaxValue	Identifies the largest value that can be assigned to the identity column to be created.
CacheSize	Identifies the number of values of the identity sequence that are to be generated at one time and kept in memory.
Expression	Identifies an expression or user-defined external function that is to be used to generate values for the identity column to be created.

Thus, if an application needed to create a global temporary table named TEMP_EMP that had three columns in it, two of which use an integer data type and another that uses a fixed-length character string data type, it could do so by executing a DECLARE GLOBAL TEMPORARY TABLE SQL statement that looks something like this:

 DECLARE GLOBAL TEMPORARY TABLE temp_emp   (empid INTEGER,   name CHAR(50)   dept INTEGER) 

On the other hand, if an application wanted to create a global temporary table named TEMP_EMP that had a simple identity column in it, it could do so by executing a DECLARE GLOBAL TEMPORARY TABLE SQL statement that looks something like this:

 DECLARE GLOBAL TEMPORARY TABLE temp_emp   (empid INTEGER GENERATED ALWAYS AS IDENTITY,   name CHAR(50)   dept INTEGER) 

Or, if an application wanted to create a global temporary table named TEMP_EMP that has the same structure as a base table named EMPLOYEE and that, once populated, will retain its contents after transactions are committed, it could do so by executing a DECLARE GLOBAL TEMPORARY TABLE SQL statement that looks something like this:

 DECLARE GLOBAL TEMPORARY TABLE temp_emp LIKE employee    ON COMMIT PRESERVE ROWS 

And finally, if an application wanted to create a global temporary table named TEMP_EMP and define its structure using two columns from a view named EMPLOYEES in such a way that, once populated, it will retain its contents after transactions are committed, it could do so by executing a DECLARE GLOBAL TEMPORARY TABLE SQL statement that looks something like this:

 DECLARE GLOBAL TEMPORARY TABLE temp_emp AS (SELECT empid, lastname FROM employees) DEFINITION ONLY   ON COMMIT PRESERVE ROWS 

All of these examples have one thing in common: Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.