SA0112B : Avoid IDENTITY columns unless you are aware of their limitations |
![]() |
Be aware of the limitations and implications of using identity columns in SQL Server, such as gaps in values and scalability challenges.

An identity column is a common method for generating unique row identifiers in SQL Server tables. While convenient, it has specific limitations that can affect database efficiency and scalability if not carefully managed. For instance, identity columns generate sequential numbers, which may lead to issues like concurrency and lock escalation, especially in high-volume transactional environments.
For example:
1-- Example of using an identity column 2CREATE TABLE Users ( 3 UserID INT IDENTITY(1,1) PRIMARY KEY, 4 UserName NVARCHAR(50) 5);
While this provides a simple, automatic user ID generation, it could lead to:
-
Concurrency issues due to lock escalation when many transactions attempt to insert rows simultaneously.
-
Potentially running out of identity values with a large amount of data, impacting the scalability of the application.

To address potential issues with identity columns in SQL Server, consider the following strategies to minimize impact on performance and scalability.
Follow these steps to address the issue:
-
Ensure uniqueness by placing a unique index on the identity column if required. Use CREATE UNIQUE INDEX on the identity column to enforce uniqueness.
-
Acknowledge non-consecutive values within transactions due to parallel inserts. Design transactions and application logic with the understanding that IDENTITY values may not be consecutive.
-
Mitigate gaps post-server restart or failure by considering the use of SEQUENCE objects, which provide restartability without loss of numbers, in place of identity columns.
-
Avoid value reuse by understanding that failed or rolled-back transactions will lose those IDENTITY values. Consider implementing a strategy to handle gaps if necessary.
-
For replication scenarios, manage identity columns according to the type of replication to prevent conflicts and ensure consistency.
-
Be aware of limitations, such as only one IDENTITY column per table, and that IDENTITY properties can’t be added or removed once set.
-
Remember that TRUNCATE TABLE resets the identity value. Opt to use DELETE if preserving sequence is critical, despite its slower performance.
-
Set IDENTITY_INSERT ON to assign specific values to identity columns when necessary, allowing explicit values during inserts.
For example:
1-- Create a unique index on an identity column 2CREATE UNIQUE INDEX idx_UserID ON Users(UserID); 3 4-- Use SEQUENCE as an alternative to IDENTITY 5CREATE SEQUENCE UserSeq 6 START WITH 1 7 INCREMENT BY 1; 8 9-- Example usage of sequence in the Users table 10CREATE TABLE Users ( 11 UserID INT DEFAULT NEXT VALUE FOR UserSeq PRIMARY KEY, 12 UserName NVARCHAR(50) 13);

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


SQL
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 |

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 |
