15.00 - Columns - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Columns

A column is a structural component of a table and has a name and a declared type. Each row in a table has exactly one value for each column. Each value in a row is a value in the declared type of the column. The declared type includes nulls and values of the declared type.

Defining Columns

The column definition clause of the CREATE TABLE statement defines the table column elements.

A name and a data type must be specified for each column defined for a table.

Here is an example that creates a table called employee with three columns:

   CREATE TABLE employee
     (deptno INTEGER
     ,name CHARACTER(23)
     ,hiredate DATE);

Each column can be further defined with one or more optional attribute definitions. The following attributes are also elements of the SQL column definition clause:

  • Data type attribute declaration, such as NOT NULL, FORMAT, TITLE, and CHARACTER SET
  • COMPRESS column storage attributes clause
  • DEFAULT and WITH DEFAULT default value control clauses
  • PRIMARY KEY, UNIQUE, REFERENCES, and CHECK column constraint attributes clauses
  • Here is an example that defines attributes for the columns in the employee table:

       CREATE TABLE employee
         (deptno INTEGER NOT NULL
         ,name CHARACTER(23) CHARACTER SET LATIN
         ,hiredate DATE DEFAULT CURRENT_DATE);

    System-Derived and System-Generated Columns

    In addition to the table columns that you define, tables contain columns that Teradata Database generates or derives dynamically.

     

    Column

    Description

    Identity

    A column that was specified with the GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY option in the table definition.

    Object Identifier (OID)

    For a table that has LOB columns, OID columns store pointers to subtables that store the actual LOB data.

    PARTITION

    For a table that is defined with a partitioned primary index (PPI), the PARTITION column provides the partition number of the combined partitioning expression associated with a row, where the combined partitioning expression is derived from the partitioning expressions defined for each level of the PPI. This is zero for a table that does not have a PPI.

    For more information on PPIs, see “Partitioned and Nonpartitioned Primary Indexes” on page 31.

    PARTITION#L1 through PARTITION#L62

    For tables that are defined with a multilevel PPI, these columns provide the partition number associated with the corresponding level. These are zero for a table that does not have a PPI and zero if the level is greater than the number of partitions.

    ROWID

    Contains the row identifier value that uniquely identifies the row.

    For more information on row identifiers, see “Row Hash and RowID” on page 28.

    Restrictions apply to using the system-derived and system-generated columns in SQL statements. For example, you can use the keywords PARTITION and PARTITION#L1 through PARTITION#L15 in a query where a table column can be referenced, but you can only use the keyword ROWID in a CREATE JOIN INDEX statement.

    Related Topics

     

    For more information on …

    See …

    data types

    “Data Types” on page 24.

    CREATE TABLE and the column definition clause

    SQL Data Definition Language.

    system-derived and system-generated columns

    Database Design.