Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Default Map for the User, Database, or Profile

The immediate owner of the table can be a user or a database. See the CREATE USER DEFAULT MAP option, the MODIFY USER DEFAULT MAP option, the CREATE DATABASE DEFAULT MAP option, the MODIFY DATABASE DEFAULT MAP option, the CREATE PROFILE DEFAULT MAP option, or the MODIFY PROFILE DEFAULT MAP option.

User, Database, or Profile DEFAULT MAP OVERRIDE ON ERROR Option

If the map you specify is not valid for any reason, (such as it does not exist, has not been granted to you, or is not in the same secure zone), Vantage either substitutes a default map or returns an error, subject to the values of the DEFAULT MAP settings for your PROFILE, USER, or DATABASE.

Default Map for a CREATE TABLE Statement

If you do not specify the MAP option when creating a table, the system determines a default map for the table in following order of precedence:
  • If the immediate owner is not the creator:
    • Default map, if defined, for the profile of the immediate owner.
    • Default map, if defined, for the immediate owner.
    • System-default map.
  • Default map, if defined, for the profile of the creator.
  • Default map, if defined, for the creator.
  • System-default map.

Default Map for a CREATE TABLE AS Statement

For a CREATE TABLE AS source_table statement, the map for the source table is used as the default map if you have been granted this map, or the map is one of the following as listed in order of precedence:
  • If the immediate owner is not the creator:
    • Default map, if defined, for the profile of the immediate owner.
    • Default map, if defined, for the immediate owner.
    • System-default map.
  • Default map, if defined, for the profile of the creator.
  • Default map, if defined, for the creator.
  • System-default map.
For a CREATE TABLE AS query statement, regardless of whether the query references 0, 1, 2, or more tables, the system determines a default map for table in the following order of precedence:
  • If the immediate owner is not the creator:
    • Default map, if defined, for the profile of the immediate owner.
    • Default map, if defined, for the immediate owner.
    • System-default map.
  • Default map, if defined, for the profile of the creator.
  • Default map, if defined, for the creator.
  • System-default map.

Secure Zones and Sparse Maps

For a sparse map, you must be in the same secure zone as the sparse map.

AMPs and Sparse Maps

For sparse maps, the system determines on which AMPs of the parent contiguous map the table resides based on the number of AMPs defined for the sparse map and the colocation name for the table. See CREATE MAP.

Renaming a Table and Colocation Name

Renaming a table does not change the colocation name for the table.

Error Tables and Sparse Maps

Error tables use the same sparse map and the same colocation name as the associated data table.

Map for Global Temporary Tables and Volatile Tables

You can specify an existing contiguous or sparse map for global temporary tables and volatile tables.

Volatile tables in DBC are always in TD_DataDictionaryMap.

Permanent Journaling and Maps

If permanent journaling is enabled for a table, the map for the table must be a contiguous map and the map must be the same as the contiguous map of the journal table for the table. A journal table must have a contiguous map.