Archives of the TeradataForum

Message Posted: Tue, 26 May 2004 @ 02:39:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Joe,

This will help u to know how Identity col works

Also known as a DBS Generated Unique Primary Index: A table-level unique number system-generated for every row as it is inserted in the table.

Identity Columns may be used to ...

Guarantee row uniqueness in a table

Guarantee even row distribution for a table

Optimize and simplify initial port from other databases that use generated keys

Identity Columns are valid for:

Single inserts

Multi-session concurrent insert requests (e.g., TPump)

INSERT - SELECT

Identity Columns Save Overhead/Maintenance Costs:

Reduce need for uniqueness constraints

Reduce manual coding tasks

Generate unique PK values

Comply with the ANSI Standard

Characteristics of the IDENTITY Column feature are ...

Implemented at column level in a CREATE TABLE statement

Data type may be any exact numeric type

GENERATED ALWAYS always generates a value

GENERATED BY DEFAULT generates a value only when no value is specified

GENERATED ALWAYS + NO CYCLE implies uniqueness

CYCLE restarts numbering after the maximum/minimum number is generated

DBSControl setting indicates the number pool size to reserve for generating numbers

Each Vproc may reserve 1 - 1,000,000 numbers; default is 100000.

Numbering gaps can occur

Generated numbers do not reflect row insertion sequence

Exact incrementing is not guaranteed

Scalability and performance are favored over enforced sequential numbering

Generated Always Identity Columns

Typically define the Primary Index.

Define as the Primary Index only if it is the primary path.

If it is also used as an access path, consider it as a Secondary Index.

Generated By Default Identity Columns

Facilitate copying data from one table into another.

Use a numeric type large enough to hold all the values that will ever be required.

Never use as a substitute for a good logical database design.

May not optimally utilize Teradata join and access capabilities.

Restrictions

A table can only have 1 Identity column.

FastLoad and MultiLoad do not support Identity columns with Teradata V2R5.0.

ALTER TABLE statement can not add an Identity Column to an existing table.

Cannot be part of a composite primary or a composite secondary index.

Cannot be used with Global Temporary or volatile tables.

Cannot be used in a join index, hash index, PPI or value-ordered index.

Atomic UPSERTs are not supported on a table with an Identity Column as its PI.

GENERATED ALWAYS Identity Column value updates are not supported.

Note: With Teradata V2R5.1, Identity columns are supported with the FastLoad, MultiLoad, and Teradata Warehouse Builder (TWB) utilities.

Thanks & Regards,

Vidya Sagar.N

	<Prev	Next>		<<First	<Prev	Next>	Last>>