Create Tables | VantageCloud Lake - Creating Tables in Teradata - Teradata Vantage

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

1. Log 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 MULTISET.

   database_name

   The name of the database in which to create the table if different from the current database.

   Recommendation: Make the database 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 automatically creates a duplicate copy of the current image of every row in this table to provide backup in case of a failure.

   For Object File System tables, NO FALLBACK is ignored.

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

   FALLBACK is not supported on VantageCloud Lake.

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

   [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, Analytics Database chooses 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 Analytics Database may not be optimal for the table.

   primary_index_column

   Specifies the column or columns 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 options later using the ALTER TABLE statement.

   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 and the data structures for subsequent collection of statistics and demographics.

Optional Table-Level and Column-Level Elements

Example: 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