Temporary Tables Chapter 19 Chapter 19 ­ Temporary Tables Temporary Tables There may be times when an existing production database table does not provide precisely what you need. Sometimes, a particular query might need summarized or aggregated data. At other times, a small number of rows, from a very large table or data for a specific organization, are required to find an answer. In a data warehouse with millions of rows, it might take too long to locate, derive or mathematically calculate the data needed. This is especially true when it is needed more than once per day. So, a view might not be the best solution or a view does not exist and you do not have the privilege to create one and both a view and derived table take too long. Any of these conditions prevent the ability to complete the request. In the past, temporary tables have been created and used to help SQL run faster or be more efficient. They are extremely useful for solving problems that require stored "temporary" results or which require multiple SQL steps. They are also great for holding aggregated or summarized data. Most databases lose speed when they have to: Read every row in a very large table (full table scan) Perform several aggregations Perform several data type conversions Join rows together from multiple tables Sort data Temporary tables are often useful in a de-normalization effort. This might be done to make certain queries execute faster. Other times it is done to make the SQL easier to write, especially when using tools that generate SQL. However, these temporary tables are real tables and require manual operations to create, populate, and maintain them. As a result, better name for these temporary tables might be interim or temporal tables. They exist for a specific period of time and when no longer needed, they are dropped to free up the disk space. During the interim time, they provide a valuable service. However, if the data in the original tables changes, the interim tables must be repopulated to reflect that change. This adds a level of difficulty or complexity regarding their use. The Modern Guide to Teradata SQL Page 437