System-Derived and System-Generated Columns | Teradata Vantage - System-Derived and System-Generated Columns - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

About System-Derived Columns

As the name implies, system-derived columns are columns whose values are not created by users, but instead are derived dynamically by the system. Some system-derived columns contain values that Teradata Database creates and maintains for internal use, while others contain values that are critical for user applications.

The following system-derived column types are supported by Teradata Database:
  • ROWID columns
  • PARTITION and PARTITION#L n columns

ROWID, PARTITION, and PARTITION#L n columns are always system-defined and their values are always system-generated.

About System-Generated Columns

In some cases of system-generated columns the DBA specifies the name of the column and whether or not it is to be created for a table and in other cases, the existence, name, and contents of the column are all system-controlled.

The following system-generated column types are supported by Teradata Database:
  • Identity columns

    Identity columns are user-defined, but Teradata Database defines the values inserted into them (in the case of GENERATED ALWAYS AS IDENTITY columns, all column values are system-generated. In the case of GENERATED BY DEFAULT AS IDENTITY columns, inserted column values can be user-generated or system-generated.).

  • Object Identifier columns (see Object Identifier Columns)

    Similarly, BLOB, CLOB, and XML columns are user-defined, but Teradata Database defines the OID values that point to them.

ROWID Columns

Every Teradata Database base table, join index, and hash index has a system-generated column named ROWID. The fields in this column contain the RowID value for their rows.

FOR a partitioned table or join index that has … The data type for ROWID values is …
65,535 or fewer combined partitions BYTE(10)
> 65,535 combined partitions BYTE(16)

The system-derived column ROWID contains the internal row identifier associated with a row of a base table or join index.

With one exception, there is nothing different for a column-partitioned table or join index. The exception is that the column partition is always 1 for the internal partition number in the ROWID of a column-partitioned table or join index. If you only specify column partitioning when you create a column-partitioned table or join index and do not specify the ADD option, the table or index always uses 2-byte partitioning.

As a user, you can only specify the ROWID keyword in a CREATE JOIN INDEX request to enable non-covering join indexes to join with base table columns to optimize query processing (see Partial Query Coverage, Restrictions on Partial Covering by Join Indexes, and “CREATE JOIN INDEX” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. You cannot specify ROWID in any other context at any time.

The rules for using the ROWID keyword in a CREATE JOIN INDEX request are as follows.
  • You can optionally specify the ROWID for a base table in the select list of an nonpartitioned or PPI join index definition.

    The select list for a column-partitioned join index must include the system-derived column ROWID of the base table, and it must be specified with an alias.

    If you reference multiple tables in the join index definition, then you must fully qualify each ROWID specification.

  • You can reference an alias for ROWID, or the keyword ROWID itself if no alias name has been specified for it, in the primary index definition or in a secondary index defined for the join index in its index clause.

    This does not mean that you can reference a ROWID or its alias in the DDL you use to create a secondary index defined separately from CREATE TABLE using a CREATE INDEX request (see “CREATE INDEX” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144) after the join index has been created.

  • If you reference a ROWID alias in the select list of a join index definition, then you can also reference that correlation name in a CREATE INDEX request that creates a secondary index on the join index.
  • Aliases are required to resolve any column name or ROWID ambiguities in the select list of a join index definition. An example is the situation where you specify ROWID for more than one base table in the index definition.
  • Aliases are mandatory for a ROWID specification in a column-partitioned join index.

If you attempt to use the ROWID keyword in any other context, such as selecting or deleting from, updating, or inserting rows into base tables, views, or derived tables Teradata Database aborts the request and returns an error to the requestor.

These rules apply equally to a join index defined with a partitioned primary index and to a column-partitioned join index whether the partitioning is single-level or multilevel.

Object Identifier Columns

For each BLOB, CLOB, or XML column created for a table, Teradata Database automatically stores a 40-byte or 45-byte multiple field pointer in the row to the subtable that stores the actual BLOB, CLOB, or XML data for that column (see Sizing a LOB or XML Subtable). This value, referred to as an OID, is stored in VARBYTE format.

If a BLOB, CLOB, or XML column is null, then so is its OID.

The column sizes reported here are for OIDs stored on disk in rows. They do not include, and are not the same as, the sizes of OIDs passed as inline or deferred host parameters as part of a USING request modifier row.

System-Derived and System-Generated Column Data Types

The following table lists the data types for several system-derived and system-generated column data types:

Derived or Generated Column Data Type Default Title
OID VARBYTE None
ROWID
  • BYTE(10) for 2-byte partitioning
  • BYTE(16) for 8-byte partitioning
None.

There is no default title for the ROWID keyword because you cannot specify it in the select list of any DML request. You can only specify ROWID in a CREATE JOIN INDEX DDL request.

Identity column Any of the following.
  • BYTEINT
  • DECIMAL(n,0)

    The scale for a DECIMAL identity column must be 0.

  • INTEGER
  • NUMBER(n,0)

    Only a fixed NUMBER type is permitted for identity columns, and its scale must be 0.

  • NUMERIC(n,0)
  • SMALLINT
  • BIGINT

The upper limits for DECIMAL and NUMERIC types are the following.

  • DECIMAL(18,0)
  • NUMERIC(18,0)

This is true even when the DBS Control flag MaxDecimal is set to 38 (see Teradata Vantage™ - Data Types and Literals, B035-1143).

You can define an identity column with more than 18 digits of precision, or even as a BIGINT or NUMBER(n,0) type, without the CREATE TABLE or ALTER TABLE request aborting, but the values generated by Teradata Database for the identity column remain limited to the DECIMAL(18,0) type and size.

A table that is managed by Teradata® Unity™ cannot have an identity column. Teradata® Unity™ instead uses its own mechanism to generate “identity column” values. For more information, see Teradata® Unity™ User Guide, B035-2520.

Default title for the column designated as an identity column.
PARTITION INTEGER PARTITION
PARTITION#L n
  • INTEGER for the 2-byte form of PPI.
  • BIGINT for the 8-byte form of PPI.
PARTITION#L n