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

The topic describes the SA0112B analysis rule.

Message

Avoid IDENTITY columns unless you are aware of their limitations

Description

The rule check all table CREATE and ALTER scripts and alerts for creating a table with identity column or adding identity column to a table.

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.

How to fix

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

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE TABLE dbo.doc_exe
 2(
 3 column_a INT CONSTRAINT column_a_un UNIQUE,
 4 column_a_identity INT IDENTITY,
 5);
 6
 7
 8ALTER TABLE dbo.doc_exe
 9ADD 
10column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
11column_b1 INT IDENTITY,  
12column_c INT NULL
13
14
15-- The SA0112 rule suppress comment applies for the whole statement.
16
17CREATE TABLE dbo.doc_exe1
18(
19 column_a INT CONSTRAINT column_a_un UNIQUE,
20 column_a_identity INT IDENTITY /*IGNORE:SA0112*/
21);
22
23ALTER TABLE dbo.doc_exe ADD column_b1 INT IDENTITY
Analysis Results
  Message Line Column
1 SA0112B : Avoid IDENTITY columns unless you are aware of their limitations. 4 1
2 SA0112B : Avoid IDENTITY columns unless you are aware of their limitations. 20 1
3 SA0112B : Avoid IDENTITY columns unless you are aware of their limitations. 10 0
4 SA0112B : Avoid IDENTITY columns unless you are aware of their limitations. 11 0
5 SA0112B : Avoid IDENTITY columns unless you are aware of their limitations. 23 28
See Also

Other Resources

Analysis Rules