Data in temporary tables is visible only to the session (connection) that inserted the data, and the data is retained only for the duration of the connection. Temporary tables are even faster than transient tables because they do not use logging or any type of concurrency control mechanism (such as record locking).
If your session creates a temporary table and inserts data into the table, no other user session can see your data, even if you grant privileges on that table. Multiple sessions can use the same table simultaneously, but each session can see only the data that it inserted.
Since each session can see only its own data, you do not need to coordinate with other sessions to make sure that you insert unique values into the table, even if the table has a unique constraint. For example, if you create a temporary table that has a unique constraint on the ID column, you and another session might both insert records that have the ID set to the value 1. Since each session sees only its own data, operations such as UPDATE and DELETE affect only the data in the session.
Data in temporary tables has limited duration because as soon as you exit your current session (disconnect from the server), the data is discarded. If you connect again, you cannot see your data.
The word temporary in the term temporary tables refers to the data, not the table itself. The server stores the definition of the temporary table (but not the data) in the system tables and keeps that definition even after you disconnect. Thus, if you reconnect to the server later, the table still exists, but it is empty. When you create the table, you do not need to create it again in future sessions. In fact, if you or another user try to create a temporary table with the same name as an existing temporary table, you get an error message.
Because the tables persist (even though the data does not), use the DROP TABLE statement to drop the table definition when you no longer need it. Also, if you export a database schema definition, the output includes the statements that are required to re-create the temporary tables.
Because temporary tables are cleared when the user disconnects, the processor usage can seem high for some time after a session that had a large amount of temporary table data.
Temporary tables have a number of other characteristics:
▪ If you use the HotStandby component, data in temporary tables is not replicated to the secondary server. However, temporary table definitions are replicated to the secondary server. Thus, if you want to fail over to your secondary server, you do not need to re-create any temporary tables that exist on your primary server, but you must re-create any data in the tables.
▪ Temporary tables can be used only as replica tables in Advanced Replication systems, not as master tables.
▪ Temporary tables have restrictions on how they can be used with referential constraints. A temporary table can reference another temporary table, but it cannot reference transient or persistent tables. No other type of table can reference a temporary table.
With the exceptions of the limitations listed in this topic, temporary tables behave like normal (persistent) M-tables.
For example,
▪ Temporary tables can have indexes on them.
▪ Temporary tables can be used in views.
▪ Temporary tables can have triggers on them.
▪ Temporary tables can contain BLOB columns.
▪ Temporary tables exist in a specific catalog and schema.
▪ Privileges apply to temporary tables; in other words, the creator of the temporary table can grant and revoke privileges on the table. The DBA can also grant and revoke privileges on the table. However, when a session puts data into a temporary table, the data cannot be seen by any other session, even if that session is for a DBA or a user that has SELECT privilege on the temporary table. Therefore, granting privileges on a table merely grants the other user the right to use your table, not your data. Default privileges on temporary tables are the same as the default privileges on persistent tables.
The solidDB implementation of temporary tables fully complies with the ANSI SQL:1999 standard for global temporary tables. All solidDB temporary tables are global. In the CREATE TABLE syntax, the keyword GLOBAL is supported for compatibility reasons. However, even if the keyword GLOBAL is not specified, all temporary tables are global.
The solidDB server does not support local temporary tables as defined by ANSI.