Archives of the TeradataForum

Message Posted: Wed, 12 Jan 2011 @ 20:50:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Is it guaranteed that the values of an Identity field -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) -- without recycling are strictly increasing? I am aware that sequential numbering gaps can occur as documented in the bulleted information below from a Teradata reference manual. Is it possible that an AMP on a later insert might use numbers previously allotted, but unused? For example, assume AMP1 is allotted 1 100 and AMP2 101 200, and IDs 1, 2, 3, 101, and 102 are set on the first insert. On a subsequent insert, would AMP1 ever start at 4 or is it allotted a new batch, say 201 300?

On a related note, I do not have control over the batch size (customer-driven) so I cannot set it to 1 to guarantee no gaps. I m assuming batch size is global for the instance and cannot be set to different values for different databases.

- Because the Teradata architecture is highly parallel, generated identity column numbers do not necessarily reflect the chronological order in which rows are inserted.

- Sequential numbering gaps can occur. Because the cached range of reserved numbers is not preserved across system restarts, exact increments cannot be guaranteed. For example, the identity column values for 1 000 rows inserted into a table with an INCREMENT BY value of 1 might not be numbered from 1 to 1 000 if a system restart occurs before the identity column number pool is exhausted.

Thanks,

Mike
Dr. Michael R. Kappel

	<Prev	Next>		<<First	<Prev	Next>	Last>>