I was working with the identity attribute and for my surprise I got gaps in my inserted values. here is what i did: [color=#001133]create table sv_curr( id integer generated always as identity(minvalue 1 maxvalue 100000 cycle), num decimal(17,9)) unique primary index(id);[/color]
insert into sv_curr(,100000); --above query ran fine insert into sv_curr(,9999999999); --error Failure 3520 A constant value in a query is not valid for column num. insert into sv_curr(,99999999); --fine sel * from sv_curr order by id;
id num ----------- --------------------- 1 100,000.000000000 3 99,999,999.000000000
Can anybody explain why there is a gap. actually second insert failed. Thanks in advance, Rajan
Hi Rajan, identity values might be used for unique values without UNIQUE constraint (if defined using NO CYCLE and ALWAYS) but might have gaps and don't reflect the chronological order of inserted rows, because Teradata is a parallel DBMS. It's like several sequence generators (1 per AMP/PE) for a single column, an Insert/Select is processed by all AMPs and each AMP uses a batch of values.
You'll find all the details within the DDL manual: Chapter 3: SQL Data Definition Language Statement Syntax (CREATE TABLE - CREATE VIEW) CREATE TABLE (Column Definition Clause) Identity Columns