SA0112B : Avoid IDENTITY columns unless you are aware of their limitations
The topic describes the SA0112B analysis rule.
Avoid IDENTITY columns unless you are aware of their limitations
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.
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.
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 Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Bugs
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
|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|