Non-persistent in-memory tables are not written to disk when the server shuts down. Therefore, any time that the server shuts down, whether normally or abnormally, the data in non-persistent tables is lost. Their data is not logged or checkpointed. That makes them unrecoverable but remarkably faster than persistent tables.

There are two different types of non-persistent in-memory tables: transient tables and temporary tables. The main difference between temporary tables and transient tables is that the data of a temporary table is visible to a single connection whereas data of a transient table is visible to all users.

Non-persistent tables are useful as scratchpads. For example, you can copy data from a persistent table, do a series of intensive operations on the data while it is in the temporary table, and then store the results back in a persistent table. This allows you to maximize performance, yet still keep part or all of the data when you are done. If, for some reason, your work is interrupted, the original data is still safe in the persistent table, and you can restart the processing.

Because transactions for non-persistent tables are not logged, they cannot be used with HotStandby.

Data in temporary tables is visible only to the connection that inserted the data, and the data is retained only for the duration of the connection. Temporary tables are like private scratchpads that no one else can see. 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).

Data in temporary tables has limited visibility because only the session (connection) that inserted the data can see it.

If your session creates a temporary table and inserts data into it, 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 its own data.

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, you 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. The behavior can be unexpected if you think that a temporary table means that the table (not just the data) disappears as soon as you disconnect.

Because the tables persist (even though the data does not), use the DROP TABLE command to drop the table definition after you no longer need it. Also, because the table persists, if you export a database schema definition, the output includes the commands 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 with a large amount of temporary table data.

With the exceptions of the limitations listed in this section, temporary tables behave like normal (persistent) in-memory tables.

For example,

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.

Transient tables last until the database server shuts down. Multiple users can use the same transient table, and each user sees the data of all other users.

In most regards, transient tables behave like standard (persistent) in-memory tables.

For example:

If you export a database with a transient table, the data in the transient tables and the structure of the tables are exported.

The server stores the definition of the transient table (but not the data) in the system tables and keeps that definition even after the server is shut down. If you restart the server later, the table still exists, but the data does not. Thus, you need to create the table only once. In fact, if you or another user try to create a transient table with the same name as an existing transient table, you get an error message, even if the server has been shut down and restarted since the time that the table with that name was originally created. This behavior can be unexpected if you think that a transient table disappears as soon as you shut down the server.

Also, since a transient table persists (even though the data does not), you can use the DROP TABLE command to drop the table after you no longer need it.

Transient tables have some limitations when compared to persistent in-memory tables.

Transient tables are not defined by the ANSI standard for SQL. Transient tables are a solidDB^® extension to the SQL standard.

The main differences between temporary tables and transient tables are: