Versioning of Temporary Tables

A temporary table that is created in the database is called a global temporary table.

A global temporary table is accessible to all users with the appropriate permissions. Each user has his or her own instance of the table, however; only one user ever sees a given set of rows. By default, the rows of a global temporary table are deleted on COMMIT. You can override this default, by specifying ON COMMIT PRESERVE ROWS when you create the temporary table.

A local temporary table is declared rather than created in the database. Only one user sees any of the rows in a local temporary table. The table is dropped when that user disconnects. When you declare a local temporary table, Sybase IQ issues a savepoint instead of committing the transaction automatically, as it would for a data definition operation on any other type of table. Be sure to commit the data in the local temporary table before creating an index. If you attempt to create an index using uncommitted data, you may get the following error message: “Local temporary table, <tablename>, must be committed in order to create an index.”

For purposes of versioning, Sybase IQ makes no distinction between base tables (main database tables) and global temporary tables. Because the data in any temporary table is accessible to only one user, there will never be more than one write transaction open for a temporary table.

Related concepts
Data Storage
Dbspace Management Example
Disk Space
Locks for DDL Operations
Related tasks
Disconnecting All Connections from a Database in Interactive SQL