DWHPro Insights

Teradata Volatile Tables: CREATE Syntax and Session Rules

By Roland Wenzlofsky ·

Teradata Volatile Tables: CREATE Syntax and Session Rules

What is a Teradata Volatile Table?

A Teradata volatile table is a temporary table available only in the session that creates it. Its definition and data disappear when the session ends, and its rows use the user’s spool space. Use ON COMMIT PRESERVE ROWS when rows must remain available after a transaction commits.

Watch: why a volatile table is empty after the INSERT

This video walks through the lab example above: one row inserted, zero rows counted. It shows when the commit happens in each session mode, how ON COMMIT PRESERVE ROWS keeps the rows, and why the table still ends with the session.

To create a volatile table with explicit MULTISET semantics and keep its rows across commits, use:

CREATE MULTISET VOLATILE TABLE Customer
(
   CustomerId INTEGER NOT NULL,
   LastName VARCHAR(255)
) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

Run subsequent queries in the same database session. Reconnecting creates a new session in which the table no longer exists. Use HELP VOLATILE TABLE; to list volatile tables in the current session, and DROP TABLE Customer; when you no longer need this example table. For related choices, see:

Comparison of temporary table types

Verified example: why volatile-table rows disappear after INSERT

Lab verification: 22 October 2026. The example below was executed on Teradata Database 20.00.28.81 using BTEQ 20.00.00.11 in Teradata (BTET) transaction mode. Each SQL statement was submitted as a separate request. These are small functional checks, not performance benchmarks; results involving transaction boundaries can differ in ANSI mode or an explicit transaction.

CREATE MULTISET VOLATILE TABLE dwhpro_ai_delete
(id INTEGER) PRIMARY INDEX (id) ON COMMIT DELETE ROWS;

INSERT INTO dwhpro_ai_delete VALUES (1);
SELECT COUNT(*) AS delete_rows_after_request
FROM dwhpro_ai_delete;

DROP TABLE dwhpro_ai_delete;

Observed result: 0 rows. In this BTET session, the standalone INSERT committed before the SELECT. ON COMMIT DELETE ROWS emptied the table at that transaction boundary; the table definition still existed. This is different from losing the whole table when the session ends.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

In the same lab session, an ON COMMIT PRESERVE ROWS table retained all three inserted rows across subsequent requests. PRESERVE ROWS is usually the appropriate choice for staging data across multiple statements, but it does not preserve the table across reconnects. See SET versus MULTISET duplicate handling and copying a table with CREATE TABLE AS for complete staging examples.

Official reference: Teradata volatile tables and session lifetime.

Teradata Volatile Table Features

What kind of space does a Volatile Table use?

Volatile Tables are materialized in the spool space of the user.

Who can access a Volatile Table?

We can only access volatile tables in the user’s session in which we materialize them.

Is there a DBC table where I can see which Volatile Tables I have materialized?

No, but all materialized tables are listed with the command HELP VOLATILE TABLE.

Where is the DDL of a volatile table stored if not in the Data Dictionary (DBC)?

The table definition is kept in the AMP cache.

How long is a volatile table available?

Volatile tables are automatically removed when a session is terminated or a DROP TABLE statement is executed.

What happens if Teradata is restarted while I have materialized a volatile table?

Unfortunately, the table is lost along with its contents.

Can an ALTER TABLE be executed on a volatile table?

We can’t change the DDL of volatile tables.

Can I create a secondary index on a volatile table?

This is possible when the table is created. Thus, you cannot first create the table and then execute a CREATE INDEX statement.

Can I create a Join Index or Hash Index on a volatile table?

A volatile table can’t carry a join or hash index.

Who is the owner of a Volatile Table?

A Volatile Table belongs neither to a database nor to a user but to a user’s session.

Can I create the same volatile table in different sessions?

Yes, but the content is session-specific, of course.

Can I use FALLBACK protection?

Volatile tables do not provide durable recovery across session loss or a system restart. Consult release-specific restrictions before choosing protection options; durability and transaction rollback are separate concerns.

Can a volatile table be used for performance tuning?

A volatile table can materialize intermediate results with a suitable primary index and statistics. It still uses spool and execution resources and can encounter contention; measure the total cost of materialization and later requests.

Can I create statistics on a volatile table?

Statistics can be collected on volatile tables. HELPSTATS recommendations are not guaranteed to repeat after collection: in a controlled current-release test, the recommendation disappeared and confidence became high. Inspect the current session and plan.

Can a Volatile Table be created as a PPI table?

Yes, volatile tables can be row-level partitioned.

Can volatile tables be called the same as existing permanent tables?

This is possible, but you must reference the volatile table using USERNAME.TABLENAME; otherwise, you may get an error message if the parsing engine does not know which table you refer to.

When should I use the NO LOG option for Teradata Volatile Tables?

NO LOG changes rollback protection for volatile-table changes. Use it only when the workflow can tolerate its documented failure behavior and can rebuild the intermediate data; do not assume rollback is unnecessary because the table is temporary.

Are volatile tables block-level compressed?

If the optimizer keeps the table in the request cache, it is not compressed. Otherwise, it is.

Teradata Volatile Table Restrictions

  • No referential integrity can be defined
  • No CHECK constraints are allowed
  • DEFAULT values are not allowed
  • We can define no TITLE for columns
  • No Join Index or Hash Index is allowed
  • We can’t name indexes
  • We can’t use permanent Journals
  • Column Partitioning is not allowed
  • No privileges can be set with a GRANT or REVOKE. That wouldn’t make any sense, either.

Snowflake solves the same problem with a different object – see how Teradata volatile tables compare with Snowflake temporary tables.

Continue with spool-space troubleshooting or return to the Teradata SQL learning path.

Reproduce the lab checks

Download the SQL used for these functional checks. Run it in a separate BTET lab session with unused dwhpro_ai_* table names. It creates and drops only its own volatile tables and reads DBC metadata; it contains no login credentials. Execute the statements separately, as in the test. The file covers date formatting, duplicate rows, commit behaviour, table copying and spool counters.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Teradata Volatile Tables: CREATE Syntax and Session Rules”

  1. I am having issues with accessing a volatile table within the same session. I can successfully CREATE a multiset volatile table, but when I try to import a text table using an INSERT statement, I am told the volatile table doesn’t exist. My task is run in three operations. First, the table definition is created using CREATE MULTISET VOLATILE TABLE cdwhist. Then, I turn on IMPORT DATA and run the INSERT INTO cdwhist process. Finally, the last step utilizes the cdwhist volatile table and joins it with a table on the database. It is the second step that’s failing.

    Code is available, if needed.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam