Skip to main content

Datometry Documentation

Specify if CREATE TABLE Uses SET or MULTISET

The default_table_type hint lets you specify if a table should be a SET or MULTISET table if the CREATE TABLE statement does not specify a table type. If the CREATE TABLE command specifies a table type, that definition is used when creating a new table.

Teradata classifies tables as SET or MULTISET based on how the duplicate records are handled. A table defined as SET table doesn't store the duplicate records, whereas a MULTISET table can store duplicate records. The CREATE TABLE command is used to create tables in Teradata. To learn more, see SET and MULTISET in the Teradata documentation.

You can specify default_table_type as a global setting in the Hyper-Q configuration file (dtm.ini ) or as a Hyper-Q hint within a SQL query at the query, request, and session level.

The syntax for default_table_type is:

default_table_type = set | multiset

where:

Parameter

Description

set

Specifies that a SET table is created when using the CREATE TABLE command. SET is the default setting, which is also the default Teradata table type.

multiset

Specifies that a MULTISET table is created when using the CREATE TABLE command.

Example 28. Specify if a Table is SET or MULTISET as a Global Setting

You can specify default_table_type as a global setting in the Hyper-Q configuration file (dtm.ini). The following example will create MULTISET type tables for each CREATE TABLE command that does not explicitly define the table type.

"gateway".default_table_type=multiset

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.Gateway Configuration Parameters



Example 29. Specify if a Table is SET or MULTISET in a Query Hint

This example specifies that the default_table_type hint be used at the for session level within a CREATE TABLE statement which omits the table type to create.

CREATE TABLE  
--@dtm_default_table_type=multiset for session;
database.table_name (
        key_1 integer ,
        key_2 char (1) CHARACTER SET LATIN NOT CASESPECIFIC
    )
    NO PRIMARY INDEX;

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.