Skip to main content

Datometry Documentation

Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation

When using Teradata CREATE TABLE to create a new table, if the type of table is not specified explicitly you can use the default_table_type hint to create either a Teradata MULTISET or SET table type. If you create a table without explicitly specifying a table type, the default behavior is to create a MULTISET table.

To learn more, see SET and MULTISET in the Teradata documentation.

The syntax for default_table_type is:

default_table_type = multiset | set

where:

Parameter

Description

multiset

Specifies that CREATE TABLE create a MULTISET table type. By default, if you do not specify a table type Teradata creates a MULTISET table.

set

Specifies that CREATE TABLE create a SET table type.

Hint Scope

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.

Example 13. Specify default_table_type for an Individual Request

You can specify that CREATE TABLE create a MULTISET or SET table in a Query Hint. When specified in this manner, the hint applies only to the statement in the request where it is embedded.

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


Example 14. Specify default_table_type for the Duration of a Session

Using the for session modifier, you can specify that default_table_type be used for the duration of a session, as opposed to for a single query. To learn more about using a Hyper-Q hint for an entire session, see Use Hyper-Q Hints for an Entire Session.Use Hyper-Q Hints for an Entire Session

CREATE TABLE table_name
-- @dtm_default_table_type=set for session
(a int, b int);


Example 15. Specify default_table_type as a Global Setting

You can specify default_table_type as a global setting in the Hyper-Q configuration file (dtm.ini). When set as a global setting, the option you specify is applied to all sessions. This example specifies that all instances of CREATE TABLE create a SET table type unless otherwise specified.

"gateway".default_table_type = set