SA0112A : Avoid IDENTITY columns unless you are aware of their limitations

Avoid IDENTITY columns unless you are aware of their limitations

The rule checks database schema and alerts for tables having identity column.

Identity columns are not bad, but they have some limitations which should be known and considered in advance especially in large environments.

Because the IDENTITY property provides an easy way to create a simple table row identifier, it is quite often blindly applied without understanding how it works or what its usage means in a particular case.

S

Consider limitations of identity columns.

Consider the following aspects that are not guaranteed by the IDENTITY property on a column:

• Uniqueness of the value – You should always place a unique index on an identity column if your system requires uniqueness.

• Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table.

• Consecutive values after server restart or other failures – In case of database failure or server restart, some identity values and some of the assigned values can be lost. This can result in gaps in the identity value upon insert.

• Reuse of values – If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.

Additional considerations:

• If a table with an identity column is published for replication, the identity column must be managed in a way that is appropriate for the type of replication used.

• Only one identity column can be created per table.

• The IDENTITY property cannot be added to existing column or removed form a column that is created with it.

• The TRUNCATE TABLE statement resets the IDENTITY property in the table to the seed. The DELETE statement can be used to retain the counter, but it is much slower than TRUNCATE TABLE because fully logged.

• You can’t update partitioned views that are based on tables that have an IDENTITY column.

• A value cannot be assigned to an identity column unless the SET IDENTITY_INSERT setting for the table is ON

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Design Rules, Bugs

IDENTITY (Property) (Transact-SQL)

Bad habits to kick : putting an IDENTITY column on every table

Replicate Identity Columns

Should I Use IDENTITY or Not?

Other Resources