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
SETtable can hold only unique rows while a
MULTISETtable can hold duplicate rows.
- table_name – Next, Specify the name of the table after the
- 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 ROWSto 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
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 * FROM participants ORDER BY ID;
Below is the output of the above query.
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
- You can create a max of 1000 volatile tables in an active session.
- No Join Index or Hash Index is allowed.
- You can run collect statistics on a volatile table.
DEFAULTvalues are not allowed in the volatile table.
- No referential integrity can be defined.
- References to volatile tables are not permitted in FastLoad or MultiLoad.