All Forums Database
rajan-3797 14 posts Joined 01/07
15 Nov 2007
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 fineinsert into sv_curr(,9999999999);--error Failure 3520 A constant value in a query is not valid for column num.insert into sv_curr(,99999999);--finesel * from sv_curr order by id; id num----------- --------------------- 1 100,000.000000000 3 99,999,999.000000000Can anybody explain why there is a gap. actually second insert failed.Thanks in advance,Rajan

15 Nov 2007

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.Regards,Annal T

rajan-3797 14 posts Joined 01/07
22 Nov 2007

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.ThanksRaj

dnoeth 4628 posts Joined 11/04
22 Nov 2007

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 ColumnsDieter

Dieter

You must sign in to leave a comment.