There are different ways to create tables in Teradata. We can also define the tables in such a way that it will allow or not allow the 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.
CREATE TABLE DATABASENAME.TABLENAME( COLUMN1 DATATYPE, COLUMN2 DATATYPE, ... ... COLUMN10 DATATYPE );
Let us see a realtime 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 command “SHOW TABLE TEST_DB.TABLE_DEFAULT”
Table with the MULTISET option
Let us see the syntax for creating a multiset table by using the MULTISET keyword.
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 realtime 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 MULTISET table is that SET will not allow the 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.
CREATE SET TABLE DATABASENAME.TABLENAME( COLUMN1 DATATYPE, COLUMN2 DATATYPE, ... ... COLUMN10 DATATYPE );
Let us see a realtime 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 “Exporting table to file using BTEQ export in teradata“