CREATE TABLE in Teradata with examples -

Contents hide

There are different ways to create tables in Teradata. We can also define the tables so that they will allow or not allow duplicates. We can also create tables with data from another table on the fly.

Create table – Default

We can create a table in Teradata with the default options By default, if you don’t specify anything, it will create a multiset table only.

Syntax

CREATE TABLE DATABASENAME.TABLENAME(
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
...
...
COLUMN10 DATATYPE
);

Let us see a real-time example with a CREATE statement.

CREATE TABLE TEST_DB.TABLE_DEFAULT
(
ID INTEGER,
UNAME VARCHAR(50),
CRE_TS TIMESTAMP(0)
);

The above query will create the TABLE_DEFAULT as MULTISET. You can also verify this by running the command “SHOW TABLE TEST_DB.TABLE_DEFAULT”

Table with the MULTISET option

The following syntax will help us to create a multiset table as select in Teradata.

Syntax

CREATE MULTISET TABLE DATABASENAME.TABLENAME(
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
...
...
COLUMN10 DATATYPE
);

In the above syntax, if you notice, the keyword “MULTISET” is used in between the CREATE and TABLE. Let us also see a real-time example.

CREATE MULTISET TABLE TEST_DB.TABLE_MULTISET
(
ID INTEGER,
UNAME VARCHAR(50),
CRE_TS TIMESTAMP(0)
);

CREATE TABLE with the SET option

The difference between a SET and a MULTISET table is that SET will not allow row-level duplicates. Whereas, the MULTISET table will. So when there is a large volume of data, the MULTISET performs better as it does not have to check for duplicates. The following syntax will help us to create a table in Teradata with the SET option.

Syntax

CREATE SET TABLE DATABASENAME.TABLENAME(
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
...
...
COLUMN10 DATATYPE
);

Let us see a real-time example using SET. The need for SET and MULTISET tables is depending on the use case. If you need to avoid duplicates for calculating any aggregate values, you must go for a SET table.

CREATE SET TABLE TEST_DB.TABLE_SET
(
ID INTEGER,
UNAME VARCHAR(50),
CRE_TS TIMESTAMP(0)
)
PRIMARY INDEX(ID);

If you have noticed carefully, there is a primary index created for the SET table. It is mandatory for a SET table to have a PI column. The index creation deals with data distribution which we will see in detail in another post. If you don’t specify the Primary Index in the above query, you will get the below error.

Also check,