15.00 - Disadvantages of Identity Columns - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Disadvantages of Identity Columns

One disadvantage of an identity column is that the generated values have identity gaps whenever an INSERT request into a table having an identity column is aborted or rows are deleted from the table.

The sequence of identity column values is not guaranteed, and identity column values necessarily reflect the approximate chronological order of the rows inserted, though you cannot rely on a strict chronological order for the identity column values in a table because of the massive parallelism of Teradata Database.

Also keep in mind that once a table with an identity column is populated with data, deleting all of the rows in the table and reinserting new rows does not necessarily cause the numbering to restart from 1. Numbering continues from the last generated number of the table.

To restart numbering from 1, drop the table and recreate it before reloading the rows. Do not use identity columns for tables that are accessed by applications that do not tolerate gaps in their numbering. Identity gaps are more of an issue with applications using identity column for auto-numbering employees, orders, and so on.

Note: Unity Director does not support tables that have identity columns. To deal with this restriction for existing identity column tables, you can use an ALTER TABLE request to remove the identity column attribute from a table column without having to drop that column.