What are temporary tables for?

Question

What use can these so-called temporary tables offer?

Is there any advantage to using them?

There is a difference between the temporary tables and the "normal tables"?

Answer 1

What are the temporary tables for?

They are usually used to temporarily process or store data with variable size in memory, when simple variables are not sufficient.

What use can these so-called temporary tables offer?

• You can create and destroy temporary tables dynamically.
• They may have limited scope, for example, a different instance per connection or per Procedure. This allows you to process data in parallel without one user affecting another’s data or one process affecting another’s data.
• You can share data between procedures without affecting the overall state of the system. For example, you can have a batch processing where a Procedure extracts the data from the normal tables and passes the result to another Procedure that transforms the data.
• They may reflect the data structure of a "physical" table or may have a data structure for a particular case, such as data extracted for a report.
• You can temporarily store time-consuming data extraction results, such as a cache.
• In languages such as T-SQL (SQL Server), they play well the role of an array or dynamic vector, in the absence of such native data structures in the language. However, they are not always exactly like matrices, because some databases allow generating sequential numbers or even indexes in memory. Therefore, they are more "powerful" than matrices and occupy more memory.

Is there any advantage to using them?

• They are more efficient to process data, of course, as long as the amount of data does not exceed the available memory.
• Repeated, they can be created and destroyed dynamically and have a limited scope

There is a difference between the temporary tables and the "normal tables"?

Depends on the database.

Warning: this information lags quickly, because with each new version of a database, new features and data types arise and others are obsolete. Always check in the documentation current your supplier’s best practice for temporary data storage.

Oracle

Oracle, for example, allows you to create global temporary tables. Depending on the table configuration and its architecture there may be problems if the connections are reused from a Connection pool and the tables destroyed only at the end of the connection. To avoid this you can set the table cleaning at the end of a transaction.

Example:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

If you want to limit the scope to a precedent, for example, you can use TYPE to declare a table-like structure that can be used from a variable.

Example of a table variable:

DECLARE
   TYPE StockItem IS RECORD (
      item_no     INTEGER(3),
      description VARCHAR2(50),
      quantity    INTEGER,
      price       REAL(7,2));
   item_info StockItem;  
BEGIN
   ...
END;

Sql Server

There are several types, the main ones being:

• Local temporary variables whose name starts with a wire (#). They disappear when no connection uses them.
• Global temporary variables whose name starts with two barbels (##). They remain in memory, are added to the log and can be shared across different connections.
• Table variables whose name starts with an arroba (@). They are like matrices and are restricted to the local scope of a routine, as a common local variable. They are the fastest for simple processing in relatively small data sets.

Example of a table variable:

DECLARE @FirstTable TABLE (RandomInteger INT)

It is interesting to note that the temporary tables are created in a schema called tempdb, managed by SQL Server. This is not very intuitive if you do not have a knowledge about this database and potentially can lead to incompatibility problems of collation, when a database has a different encoding than the default SQL Server instance. When using temporary tables, always specify the collation when entering, retrieving and comparing data.

Postgresql

You can create temporary tables with the same command as a normal table. Just like SQL Server they live in a schema separate. Just like Oracle they can be destroyed at the end of the session or at the end of the current transaction.

You need to recreate the table every new session. Data is not shared between different sessions, but the table structure should be the same.

For use in routines, Postgresql allows you to create variables with the same table structure using %ROWTYPE.

Example:

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

Mysql

Allows using the CREATE TABLE to create temporary tables that are destroyed (content and definition) at the end of the session.

In addition, Mysql is known to have several types of Storage Engines, that is, storage mechanisms. One of them stores the table in memory. The recommendation is to use a read-only cache after some time-consuming data extraction.

Considerations

Temporary tables are different in each database and it is complicated to create an application compatible with multiple databases that makes good use of this resource.

The use of temporary tables, table variables and other database-specific features are important features that can help a lot when thinking about application performance. However, such features should always be used with caution in order to avoid data loss and excessive memory consumption. Basically, you need to study exactly each case to determine whether temporary tables can really help and this is nothing trivial when considering competition, memory, data volume and other features.

Answer 2

Temporary tables are tables that only exist for a certain period of time. I am talking here about SQL Server. So there is not quite a comparison with the normal tables of the database.

Temporary tables serve to manipulate data during a procedure, be it only in the database or application.

Imagine the following situation. Your table has 10 million and according to your query you will only need 10 thousand lines. However in its processing this filtered query is used 15 times.

Instead of performing the query 15 times you can simply create a temporary table with the query value and work with it.

Answer 3

Temporary tables are tables that exist only during connection to the database. That is, when the connection is terminated, the table is automatically eliminated.

Answer 4

What are Temporary Tables: Temporary tables are types of tables that exist only while the connection to the database is active, after the connection is terminated the table is automatically deleted, These types of tables are very useful when you need to do some kind of simple and daily task that requires tables and do not want to have all the work of deleting them so for that there are temporary tables that are self-destructed after the connection with the database is finished.

Perks: Ease, speed, Simplicity for simple tasks and that does not require permanent data storage, but temporary in connection time with the database.

There is a difference between the temporary tables and the "normal tables"? Yes There is. The temporary ones are self-destructed after the termination of the connection with the Database, While the Normal ones remain even after the termination of the connection with the Database.