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

Identity columns in SQL Server are often used to create simple, auto-incrementing row identifiers. While they offer an easy and convenient way to manage row IDs, there are important limitations and behaviors developers and database administrators should be aware of to prevent issues in large or complex environments.

For example:

1-- Example of a table with an identity column
2CREATE TABLE Customers (
3    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
4    Name NVARCHAR(100)
5);

This example uses an IDENTITY column for CustomerID. While this approach simplifies row ID creation, it can lead to problems such as running out of identity values or encountering gaps in identity sequences due to transactions that fail or roll back.

• Identity columns can exhaust their range of values, especially in systems with heavy data insertion.

• Rollback operations or deletions do not reset identity values, potentially causing gaps or unexpected values in sequences.

This section provides guidelines to fix issues related to the use of identity columns in SQL Server.

Follow these steps to address the issue:

1. Ensure uniqueness by placing a unique index on an identity column if your system requires it. Use CREATE UNIQUE INDEX to enforce uniqueness.

2. Understand that transactions inserting multiple rows are not guaranteed to get consecutive identity values. Implement logic to handle possible gaps if your application requires consecutive numbers.

3. Be aware that server restarts or failures can introduce gaps in identity sequences. Plan for mechanisms to manage or mitigate these gaps if continuity of numbers is important.

4. Adjust application logic to handle gaps caused by failed or rolled back transactions, as identity values consumed are not reused.

5. For tables with identity columns used in replication, ensure that identity values are managed according to the replication type. Refer to SQL Server documentation for guidance.

6. Remember only one identity column is allowed per table; plan your table design accordingly to prevent schema changes later.

7. Avoid using TRUNCATE TABLE if retaining identity values is necessary; prefer DELETE for such requirements, despite its slower performance due to full logging.

8. In partitioned views, avoid using identity columns in base tables, as updates will be restricted.

9. If setting specific identity values is necessary for a table, turn on SET IDENTITY_INSERT for that table.

For example:

1-- Example to create a unique index on an identity column
2CREATE UNIQUE INDEX IX_Customers_CustomerID ON Customers (CustomerID);

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