Create Volatile Table in Teradata

Teradata Volatile table is one type of temporary table which comes in very handy if you are working with Teradata on regular basis.

Generally, the volatile tables are created by the user and used by users during the session and dropped automatically by the Teradata Manager when the user disconnects from the session. Dropping here means, deleting both data and table definition.

Why Teradata Volatile table is so popular?

Teradata volatile table is one of the most popular features because the table is totally controlled by the user. For the creation of a normal Teradata table, one needs permanent space (perm space), however for creating a volatile table parm space is not required, it will use the user’s spool space.

Create Teradata Volatile Table Syntax

The syntax for creating volatile in Teradata is as follows:

CREATE [SET | MULTISET] VOLATILE TABLE table_name 
(
column1 datatype,
column2 datatype,
.
.
columnN datatype)
<index_definition>
ON COMMIT [DELETE|PRESERVE] ROWS;

In this syntax,

  • [SET|MULTISET] – Like a normal Teradata table, a volatile table also can be created with SET and MULTISET options. A SET table can hold only unique rows while a MULTISET table can hold duplicate rows.
  • table_name – Next, Specify the name of the table after the VOLATILE TABLE keywords.
  • Third, specify the comma-separated column names along with the column definition.
  • <index-definition> – Index definition like primary index, secondary index etc. need to be declare after the column definition.
  • ON COMMIT [DELETE|PRESERVE] ROWS – Unlike a normal Teradata table, here we specify ON COMMIT PRESERVE ROWS to preserve data after insertion or ON COMMIT DELETE ROWS. The default behavior is ON COMMIT DELETE ROWS.

Teradata Create Volatile Table Example

The following example creates a volatile table named participants.

CREATE VOLATILE TABLE participants
(
id INT,
first_name varchar(50),
last_name varchar(50)
)
primary index (id)
ON COMMIT PRESERVE ROWS;

Insert Data into Volatile Table

Let’s insert some data into the above volatile table now.

INSERT INTO participants values (1,'Sagar','Sharma');
INSERT INTO participants values (2,'Shankar','Kumar');
INSERT INTO participants values (3,'Priyojit','Sen');
INSERT INTO participants values (3,'Ratan','Sarkar');

Select data from the Volatile Table

Now view the data from the volatile table using the SELECT statement.

SELECT * FROM participants ORDER BY ID;

Below is the output of the above query.

Teradata Volatile Table

Finally, if we disconnect from the current session and after re-logging, run the same select statement again, we will find that the table student does not exist anymore.

SELECT * FROM participants ORDER BY ID;
*** Failure 3807 Object 'participants' does not exist.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

Facts about Teradata Volatile Table

  1. You can create a max of 1000 volatile tables in an active session.
  2. No Join Index or Hash Index is allowed.
  3. You can run collect statistics on a volatile table.
  4. CHECK constraints, DEFAULT values are not allowed in the volatile table.
  5. No referential integrity can be defined.
  6. References to volatile tables are not permitted in FastLoad or MultiLoad.

Please get connected & share!

Advertisement