Gaps in values for identity columns


Gaps in values for identity columns

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);
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,

Re: Gaps in values for identity columns

Hi Rajan,

There can be gaps in sequences.

In your case i think the system first generated value for the first column before checking the validity of second column value. (Which failed and resulted in this record not being added to the table)

So while generating Seq number for the third row it gave 3 instead of 2.

Annal T

Re: Gaps in values for identity columns

Hi Annal,

Thanks for the reply, the same problem(Gaps) occurs if you perform the Merge Into operation on a table with an identity column.

Senior Apprentice

Re: Gaps in values for identity columns

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