16.10 - Creating Tables in Teradata - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Published
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
Last Update
2018-04-26

The following procedure shows how to create tables using BTEQ, but you can also use Teradata Studio.

  1. Logon on to Teradata Database as user DBADMIN using BTEQ.
  2. Create one or more tables using the CREATE TABLE statement. For example:
    CREATE SET TABLE  database_name.table_name, FALLBACK
       (column_name   data_type,
        column_name   data_type,
        column_name   data_type)
    UNIQUE PRIMARY INDEX (primary_index_column);

    where:

    Syntax Element Explanation
    MULTISET

    SET

    A MULTISET table allows duplicate rows in compliance with the ANSI/ISO SQL 2011 standard. A SET table does not allow duplicate rows.

    If there are uniqueness constraints on any column or set of columns in the table definition, then the table cannot have duplicate rows even if it is declared as MULTISET.

    database_name The name of the database where the table will be created if it is different from the current database.

    Recommendation: The database should be the Tables_Database or a database located within the Tables_Database. See About Database Creation.

    If database_name is not specified, the system creates the table in the default database for the current session.

    table_name The name of the table to be created.
    [NO] FALLBACK Specifies that the system will automatically create a duplicate copy of the current image of every row in this table to provide backup in case of a failure.

    NO FALLBACK is the default.

    To specify FALLBACK for all tables in a database, use FALLBACK in the CREATE DATABASE statement.

    Recommendation: Specify FALLBACK for mission-critical tables or tables so large that their size prevents timely backup to tape or external disk.

    column_name Specifies the name of one or more columns, in the order in which they and their attributes are to be defined for the table.

    Up to 2,048 columns can be defined for a table. Note that other limits or feature restrictions may apply before you reach the column limit. For example, you can have no more than 32 large object (LOB) columns per table.

    data_type Specifies a single data type for each column.

    Some data types have different names in Teradata than in other databases. For more information, see SQL Data Types and Literals.

    [UNIQUE|NO] PRIMARY INDEX Specifies the primary index. A table can have no more than one primary index. If you do not explicitly assign a primary index, Teradata Database will choose a default primary index (unless you specify NO PRIMARY INDEX).

    Recommendation: Explicitly define either PRIMARY INDEX, UNIQUE PRIMARY INDEX, or NO PRIMARY INDEX because the default selected by Teradata Database may not be optimal for the table.

    primary_index_
column Specifies the column(s) that define a primary index. If you specify more than one column, the index is created on the combined values of each column. You can specify up to 64 columns.

    Recommendation: Choose columns that primarily have unique values, are frequently accessed, and have values that do not change. See Guidelines for Choosing Primary Index Columns.

    The example is the minimum recommended specification for creating a table. You can specify additional options, or add them later using the ALTER TABLE statement, see SQL Data Definition Language Syntax and Examples.

    To automate the creation of data tables by using BTEQ scripts, see Using BTEQ Scripts to Create Database Objects.

  3. Set privileges on the table so that only designated administrative users can access the table directly.
  4. Collect statistics on the newly created, empty table. This defines the columns, indexes, and partitions for a PPI table, as well as the data structures for subsequent collection of statistics and demographics.

Optional Table-level and Column-level Elements

The following are common optional table-level elements you can specify in the CREATE/ALTER TABLE statement. For details about these table options and for a complete description of all table options, see SQL Data Definition Language Syntax and Examples.

Syntax Element Explanation
GLOBAL TEMPORARY Creates a global temporary table. Global temporary tables have a persistent definition but do not have persistent contents across sessions. Each user session can materialize up to 2,000 global temporary tables at a time.

To use global temporary tables, create or modify the user or profile to have a temporary space limit by using the TEMPORARY = n BYTES option.

If no temporary space limit is defined for a profile, Teradata Databases uses the temporary space limit defined for the individual user-creator. If no temporary space is defined for a user, then the space allocated for any materialized global temporary tables referenced by that user is set to the maximum temporary space allocated for the immediate owner.

JOURNAL Specifies a Permanent Journal (PJ) for the table, which stores an image of each data row that has been changed with an INSERT, UPDATE, or DELETE statement. PJ images can reflect row values as they appeared before the change, after the change, or both.

To specify a PJ for all tables in a database, use the JOURNAL clauses in the CREATE DATABASE statement.

Recommendation:Specify journaling for tables that need an additional level of data protection after you have analyzed data protection needs.

FREESPACE Specifies the percent of free space that remains on a cylinder during loading operations. Reserved free space allows tables to expand within their currently allocated cylinders. This can prevent or delay the need for additional cylinders to be allocated, which incurs the overhead of moving data to the new cylinders. Avoiding new cylinder allocations can improve overall system performance. You can control this space at the global and table level.

Recommendation: If little or no table expansion is expected (the table is read-only), set FREESPACE to 0. Otherwise, choose a percentage that reflects the growth rate of your table (INSERTs minus DELETEs). Common settings are 5 to 15%. The maximum allowed is 75%.

DATABLOCKSIZE Specifies the maximum data block size for blocks that contain multiple rows. Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed. You can control the data block size at the global and table level.

Recommendation: Use a larger value if the database is used primarily for strategic work (decision support/complex queries). Use a smaller value if the database is used primarily for tactical work (OLTP).

[UNIQUE] INDEX Specifies secondary indexes for the table. Secondary indexes (SI) allow quick access to table data using an alternate, less frequently used path than the primary index. SIs improve performance by avoiding full table scans.
PARTITION BY Specifies that the primary index is partitioned based on the value of the specified partitioning expression. A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions, enabling enhanced performance for range queries while providing efficient PI join strategies. See Row Partitioning.

The following are common optional column-level elements you can specify in the CREATE/ALTER TABLE statement. For details about these column-level options and for a complete description of all table options, see SQL Data Definition Language Syntax and Examples.

Syntax Element Explanation
data_type_attributes If you specify attributes for a column, you must define its data type before you define its attributes. The following attributes are supported:
  • NOT NULL
  • UPPERCASE
  • [NOT] CASESPECIFIC
  • FORMAT
  • TITLE
  • NAMED
  • DEFAULT or WITH DEFAULT
  • CHARACTER SET
  • WITH TIME ZONE

Recommendation: Teradata highly recommends that you specify the NOT NULL attribute for columns that will never be null.

COMPRESS Specifies a set of distinct values in a column that is to be compressed to zero space. Column compression can enhance storage capacity, improve response time for table scans, and reduce disk I/O traffic.

Recommendation: For best results, use multivalue compression where:

  • Enough rows contain a compressible value (null, zero, blank, or constant value) in the compressed field to exceed the break even point.
  • The shortened row size eliminates one or more data blocks.
CONSTRAINT Specifies column-level contraints. The following constraints are supported:
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • REFERENCES

Recommendation:Specify a name for the constraint specification.

Example of the CREATE TABLE Statement

This table definition creates the Employee table in the database named Tables_Database:

CREATE SET TABLE Tables_Database.Employee, FALLBACK
   (Associate_Id     INTEGER,
    Associate_Name   CHAR(25),
    Salary           DECIMAL(8,2),
    DOB              DATE,
    Job_Title        VARCHAR(25),
    Dept_No          SMALLINT,
    Marital_Status   CHAR,
    No_Of_Dependents BYTEINT)
UNIQUE PRIMARY INDEX (Associate_Id);

Using BTEQ script, see Using BTEQ Scripts to Create Database Objects.

Reference Information

Topic Resources for Further Information
An overview of tables, including global temporary and volatile tables SQL Fundamentals
Syntax, options, and required privileges for the following:
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • RENAME TABLE
SQL Data Definition Language Syntax and Examples
The default database SQL Fundamentals
Data types and data type attributes SQL Data Types and Literals
  • Primary indexes
  • Partitioned primary indexes
  • Secondary indexes
  • Join indexes (materialized views)
Choosing a Primary Index
Using the FREESPACE option to manage performance, and guidelines for calculating a FREESPACE value FREESPACE table option in SQL Data Definition Language Syntax and Examples and “FreeSpacePercent” DBS Control field in Utilities
Using the DATABLOCKSIZE option to manage performance and specifying a DataBlockSize value DATALBLOCKSIZE table option in SQL Data Definition Language Syntax and Examples adn "DataBlockSize" DBS Control field Utilities
Compressing column values Database Design
Column-level and table-level constraints Database Design