15.10 - Column Definitions - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The format to be used for the storage of a column partition. A column partition consists either of a series of containers or a series of subrows.

You can only specify this option for a column-partitioned table.

If you do not specify either COLUMN or ROW, then Teradata Database determines which format to use based on the width of the column partition value.

As a general rule, Teradata Database assigns COLUMN format to narrow column-valued partitions and ROW format to wide column-valued partitions.

The system autocompresses data as physical rows that are inserted into a column partition of a column-partitioned table unless there are no applicable autocompression methods that reduce the size of the physical row or you specify NO AUTO COMPRESS. .

COLUMN
If you precede a column grouping that is specified in the column list of the table definition with the keyword COLUMN, then Teradata Database stores the column partition, which consists of the columns in the specified grouping, using COLUMN format.
ROW
If you precede a column grouping that is specified in the column list of the table definition with the keyword ROW, then Teradata Database stores the column partition, which consists of the columns in the grouping, using ROW format.
column_name
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.
For information about naming database objects, see SQL Fundamentals, B035-1141.
(column_name)
Group the values from the specified column set of 1 or more columns into the same partition of a column-partitioned table. You can specify a single column in a partition.
This option is only valid for column-partitioned tables.
You should consider grouping columns into a column partition when either of the following conditions occur.
  • The columns are frequently accessed together by queries.
  • The columns are infrequently accessed or autocompression on the individual columns or subsets of columns is not effective.
You cannot specify a column to be in more than one partition.
If you do not specify a column grouping for a COLUMN specification in the PARTITION BY clause, Teradata Database defines a column partition for each individual column and column group specified in the column list for table_name.