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.

Description

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:

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

How to fix

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:

  1. Ensure uniqueness by placing a unique index on the identity column if required. Use CREATE UNIQUE INDEX on the identity column to enforce uniqueness.

  2. 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.

  3. 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.

  4. 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.

  5. For replication scenarios, manage identity columns according to the type of replication to prevent conflicts and ensure consistency.

  6. Be aware of limitations, such as only one IDENTITY column per table, and that IDENTITY properties can’t be added or removed once set.

  7. Remember that TRUNCATE TABLE resets the identity value. Opt to use DELETE if preserving sequence is critical, despite its slower performance.

  8. Set IDENTITY_INSERT ON to assign specific values to identity columns when necessary, allowing explicit values during inserts.

For example:

SQL
Copy
 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);

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