Re-align Identity Last Value to Actual Max Value

Introduction

Sometimes, when you have a table with an IDENTITY column, there could be scenarios in which weird "gaps" are created between different IDs.

There can be several possible causes for this:

1. The most obvious cause is when rows are deleted from the table. If many rows are deleted from a table with an IDENTITY column, it's obviously expected that nothing would "fill" up the "gaps" that these rows have left. IDENTITY values only go one way, they don't automatically re-fill deleted values retroactively.
2. When a ROLLBACK is performed on a transaction after inserting into a table with an IDENTITY column, the increase in the IDENTITY value is NOT rolled back. So even if the row wasn't actually inserted, the IDENTITY value is still increased. This can happen both with single-row INSERT commands, as well as BULK insertions. So if, for whatever reason, a lot of insertions are rolled-back in your database, you may see a lot of these "gaps".
3. There's a special mechanism, specifically in SQL Server 2012, which "pre-allocates" IDENTITY values for a table, and it does this in memory. So when the SQL service is restarted, next time you insert a value into the table, the IDENTITY value would "jump" by 1000 or 10000 (depending on the column data type). This happens in SQL 2012 only, and was reportedly fixed in later versions. More info about it in this blog post by Ahasan Habib.

Solution

While retroactively fixing your "gaps" may not be an easy (or even a recommended) task, it's far more preferable to fix such "gaps" the moment you identify them at the end of your table. Let's say, for example, that you know that you've just rolled back a huge insertion of new rows to your table. Or that you have SQL 2012 and have just restarted your instance, so you expect the 1000 or 10000 value jump. Or that maybe you've simply just deleted a whole bunch of rows from your table.

In either case, you expect the next inserted row to come with a significant IDENTITY gap.

Wouldn't you want to, let's call it, "re-align" your next IDENTITY value with the actual current maximum value you have in your table, and thus avoid that gap?

I personally have encountered this scenario several times. So, I wrote a script that automatically finds ALL tables with an IDENTITY column, which don't have their next value aligned with their actual max value, and then generates a DBCC CHECKIDENT command to fix that discrepancy.

You can see it here:

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

And you can also get it from my GitHub Gist here:

https://gist.github.com/EitanBlumin/2d84daacf883d9eb52f330a977f6e78a

Or from my TechNet Gallery here:

https://gallery.technet.microsoft.com/scriptcenter/Re-align-Identity-Last-b9f5fb6e

You can use it freely for your own benefit.

Remarks

The script I provided uses the BIGINT data type when checking the max value, so it should cover the smaller integer data types as well (INT, SMALLINT and TINYINT). But other, non-integer types might not work as well.