Archives of the TeradataForum
Message Posted: Tue, 26 May 2004 @ 02:39:40 GMT
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:
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.
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,
|Copyright 2014 - All Rights Reserved|
|Last Modified: 10 Jun 2014|