Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Tables, views, and indexes


Temporary tables

Temporary tables are stored in the temporary file. Pages from the temporary file can be cached, just as pages from any other dbspace can. Operations on temporary tables are never written to the transaction log. There are two types of temporary tables: local temporary tables and global temporary tables.

  • Local temporary tables   A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

    Two local temporary tables within the same scope cannot have the same name. If you create a temporary table with the same name as a base table, the base table only becomes visible within the connection once the scope of the local temporary table ends. A connection cannot create a base table with the same name as an existing temporary table.

    When creating an index on a local temporary table, if the auto_commit_on_create_local_temp_index option is set to Off, there is no commit before creating an index on the table.

  • Global temporary tables   A global temporary table remains in the database until explicitly removed using a DROP TABLE statement. Multiple connections from the same or different applications can use a global temporary table at the same time. The characteristics of global temporary tables are as follows:

    • The definition of the table is recorded in the catalog and persists until the table is explicitly dropped.

    • Inserts, updates, and deletes on the table are not recorded in the transaction log.

    • Column statistics for the table are maintained in memory by the database server.

    There are two types of global temporary tables: non-shared and shared. Normally, a global temporary table is non-shared; that is, each connection sees only its own rows in the table. When a connection ends, rows for that connection are deleted from the table.

    When a global temporary table is shared, all the table's data is shared across all connections. To create a shared global temporary table, you specify the SHARE BY ALL clause at table creation. In addition to the general characteristics for global temporary tables, the following characteristics apply to shared global temporary tables:

    • The content of the table persists until explicitly deleted or until the database is shut down.

    • On database startup, the table is empty.

    • Row locking behavior on the table is the same as for a base table.

  • Non-transactional temporary tables   Temporary tables can be declared as non-transactional using the NOT TRANSACTIONAL clause of the CREATE TABLE statement. The NOT TRANSACTIONAL clause provides performance improvements in some circumstances because operations on non-transactional temporary tables do not cause entries to be made in the rollback log. For example, NOT TRANSACTIONAL may be useful if procedures that use the temporary table are called repeatedly with no intervening COMMIT or ROLLBACK, or if the table contains many rows. Changes to non-transactional temporary tables are not affected by COMMIT or ROLLBACK.

 See also

Creating a global temporary table
References to temporary tables within procedures