Create Tables | Teradata Vantage - Creating Tables in Teradata - Analytics Database - Teradata Vantage

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

1. Logon on to Vantage 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,
      (column_name   data_type,
       column_name   data_type,
       column_name   data_type)
   UNIQUE PRIMARY INDEX (primary_index_column);

   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 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.

   You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.

   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 Teradata Vantage™ - Data Types and Literals, B035-1143.

   [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, Vantage 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 Vantage 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

   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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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, 
   (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.

Related Information