Monday 13 May 2013

Teradata - Generated Identity Columns Basic Part 1


Generated Identity Columns are used for automatic generation of column value depending upon prescribed sequencing and intervals.

Following are the Options that can be used with this feature:

GENERATED ALWAYS
Will always generate a value irrespective of whether the value is being specified or  not.


GENERATED BY DEFAULT
Will generate a value only if defaulting or NULL is specified for the column value.


START WITH
Value used to start the generated sequence. Default is 1.

INCREMENT BY
Interval between generated values. Default is 1.

MINVALUE
Smallest value that can be placed in this column. Default is the smallest value supported by the data type. (Ex: For integer data type it would be-2147483647)

MAXVALUE
Largest value that can be placed in this column. Default is the largest value supported by the data type.

CYCLE
After the max value has been reached restart using the min value. Note that it does not go back to the 'START WITH' value. Instead it goes to the min value. If no min value is explicitly specified then it goes the system default min value supported by that data type.
 


Imp Note : Only Numeric data types can be used for identity columns and they may be only whole  numbers:
 
  1. INTEGER
  1. SMALLINT
  1. BYTEINT
  1. DECIMAL
  1. NUMERIC

No comments:

Post a Comment