Teradata Tables

Tables in the Relational model are defined as a collection of data. They are represented in the rows and columns.

Types of Table

In the Teradata, tables support the following types of tables, such as:

• Permanent Table: This is the default table, and it contains data inserted by the user and permanently stores the data.
• Volatile Table: The data inserted into a volatile table is retained only during the user session. The table and data are dropped at the end of the session. These tables are mainly used to hold the intermediate data during data transformation.
• Global Temporary Table: The definition of the Global Temporary table is persistent, but the data in the table is deleted at the end of the user session.
• Derived Table: Derived table holds the intermediate results in a query. Their lifetime is within the question in which they are created, used, and dropped.

Set vs Multiset

Teradata classifies the tables as Set or Multiset tables based on how the duplicate files are handled.

A table defined as the SET table doesn't store the duplicate files, whereas the MULTISET table can store duplicate files.

1. Create Table

CREATE Table command is used to create tables in Teradata.

Syntax

Following is the generic syntax of the CREATE TABLE statement.

• Table Options: It specifies the physical attributes of the table, such as Journal and Fallback.
• Column Definition: It specifies the list of columns, data types, and attributes.
• Index Definition: It is an additional indexing option such as Primary Index, Secondary Index, and Partitioned Primary Index.

Example

In the following example, we create a table called student with a FALLBACK option. The table contains four columns with rollno as the Unique Primary Index.

Once the table is created, we can use the SHOW TABLE command to view the Definition of the table.

2. Alter Table

ALTER table command is used to add or drop columns from an existing table. We can also use an ALTER table command to modify the attributes of the existing columns.

Syntax

Following is the generic syntax for ALTER table.

Example

In the following example, we drop the column DOB and add a new column BirthDate.

We can run the SHOW table command to view the changes to the table. In the following output, we can see that the column student_dob is removed, and BirthDate is added.

3. Drop Table

DROP TABLE command is used to drop a table. When the DROP TABLE is issued, data in the table is deleted, and the table is dropped.

Syntax

Following is the generic syntax for DROP TABLE.

Example

In the following example, we drop the "student" table.

If we run the SHOW TABLE command after this, we will get an error message stating that the table doesn't exist.