Temporary tables

Informix^® temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

Remark: BDL reports create a temporary table when the rows are not sorted externally (by the source SQL statement).

Informix allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

Microsoft™ SQL SERVER provides local (SQL session wide) or global (database wide) temporary tables by using the '#' or '##' characters as table name prefix. No 'TEMP' keyword is required in CREATE TABLE, and the INTO clause can be used within a SELECT statement to create and fill a temporary table in one step:

CREATE TABLE #temp1 ( kcol INTEGER, .... )
SELECT * INTO #temp2 FROM customers WHERE ...

Unfortunately, SQL Server temporary tables are created by default with the collation of the tempdb database, instead of inheriting the collation of the current database you are connected to.