Re-assign the identity of all the tables.

Managing Identity Values in SQL: Script and Considerations

When the alignment of identity is crucial for your business logic, this script can be quite helpful. However, it's essential to understand the potential issues related to Lost Identity, particularly when frequently switching between clustered or replication environments.

SQL Script for Checking Identity Columns

The following SQL script helps manage identity columns by identifying when the last_value of an identity column has exceeded its seed_value, which is a common issue in replication or clustered environments.

DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(int, c.last_value)
FROM sys.identity_columns AS c
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
WHERE c.last_value > c.seed_value

OPEN Cur
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMD = N'
        SELECT @pResult = N''DBCC CHECKIDENT(''''' + @CurrTable + N''''', RESEED, '' + CONVERT(nvarchar(max), MAX(' + QUOTENAME(@CurrCol) + N')) + N'') -- ' + CONVERT(nvarchar(max), @LastValue) + N''''
        FROM ' + @CurrTable + N'
        HAVING MAX(' + QUOTENAME(@CurrCol) + N') <> @LastValue'
    EXEC sp_executesql @CMD, N'@pResult NVARCHAR(MAX) OUTPUT, @LastValue BIGINT', @Result OUTPUT, @LastValue;
    IF @Result IS NOT NULL
        PRINT @Result;
    FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
END

CLOSE Cur
DEALLOCATE Cur

Key Considerations

• Identity columns are often used for automatically incrementing values in tables. However, in environments where data is replicated or clustered, the identity value might get out of sync.
• This script checks for instances where the last_value is greater than the seed_value, indicating that there might be gaps or inconsistencies in the identity values. It ensures the identity column's consistency by reseeding it to the correct value.

Additional References

1. How to Reset Identity Value - Himanshu Patel
2. Identity Columns - Himanshu Patel
3. Microsoft Documentation: sys.identity_columns

This script and guidelines help you maintain the integrity of identity columns, particularly in environments prone to data replication or clustering challenges.