Permanent VS Temporary Tables

Permanent and Temporary Tables

• Permanent storage of tables is necessary when different sessions and users must share table contents.
• When tables are required for only a single session, we can request the system to create temporary tables.
• Using this type of table, we can save query results for use in subsequent queries within the same session.
• We can break down complex queries into smaller queries by storing results in a temporary table for use during the same session. When the session ends, the system automatically drops the temporary table.

 Global Temporary Tables

• They exist only for the duration of the SQL session in which they are used.
• The contents of these tables are private to the session, and System Automatically drops the table at the end of that session.
• System saves the Global Temporary Table Definition Permanently in the Data Dictionary.
• The Saved Definition may be Shared by Multiple Users and Sessions with Each Session getting its Own Instance of the Table.

 Volatile Tables

• If you need a temporary table for a single use only, you can define a volatile table.
• The definition of a volatile table resides in memory (RAM) but does not survive across a system restart.
• It improves performance even more than using global temporary tables because the system does not store the definitions of volatile tables in the Data Dictionary.
• Access-rights checking is not necessary because only the creator can access the volatile table.

Derived Tables

• A special type of temporary table is the derived table. It is specified in SQL SELECT statement.
• A Derived Table is Obtained from One or More Other Tables as the Result of a Sub-Query.
• Scope of A Derived Table is only Visible to the Level of the SELECT statement calling the Sub-Query.
• Using Derived Tables avoids having to use the CREATE and DROP TABLE Statements for Storing Retrieved Information and Assists in Coding More Sophisticated, Complex Queries.