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 |
---|---|
| Specifies that CREATE TABLE create a MULTISET table type. By default, if you do not specify a table type Teradata creates a MULTISET table. |
| 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.
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;
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.
CREATE TABLE table_name
-- @dtm_default_table_type=set for session
(a int, b int);
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