Creating Tables in Teradata - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Creating Tables in Teradata

The following procedure shows how to create tables using BTEQ, but you can also use Teradata Administrator, SQL Assistant, or 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” on page 53.

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” on page 63.

Note: 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 “Optional Table-level and Column-level Elements” on page 75.

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

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. The reserved free space allows table data to expand on current table cylinders, preventing data migration operations which can impact 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” on page 65.

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 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” on page 86.

    Reference Information

     

    Information on...

    Is available in...

    an overview of tables, including global temporary and volatile tables
  • SQL Fundamentals
    		•
  • syntax, options, and required privileges for:
  • 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” on page 63
    		•

    using the FREESPACE option to manage performance, and guidelines for calculating a FREESPACE value

    “FreeSpacePercent” in Utilities

    using the DATABLOCKS option to manage performance and specifying a DataBlockSize value

    Utilities

    compressing column values

    Database Design

    column-level and table-level constraints

    Database Design