Reseeding a table’s identity column in SQL and fixing any gaps in identity column values

Oct 19, 2018 | Computer Programming | 0 comments

Identity columns can be very useful and also a source of serious frustration. If you’ve ever written a cursor or a while loop but forgotten to fetch the next record in your loop, you know that you can blow up an identity pretty quickly if your cursor is doing inserts. Even if you’ve put your entire cursor in a transaction and a try/catch, the identity counter will not rollback in the event of a failure. If you’ve had inserts performed AFTER your cursor blew up the identity, then you’d need to change the identity column back to the next value in the original series or just live with a giant gap in your identity values.

For example, let’s say you have an orders table with an Identity column named “ID”. The current identity value (ID for last Order created) is 50,000. Now let’s say you write a cursor or loop to insert a bunch of new orders from some data source that is outside the norm, but you forget the “FETCH NEXT FROM cursor INTO … ” line in your while loop and you execute the script. After a few seconds, you think to yourself – gee this script is taking a long time to run – let’s examine the code. After looking you realize what you’ve done and stop the script. You also have to rollback the transactions. Now you fix your script and run it again. All the orders create perfectly, except the ID is 150,001 and up. You have a gap of 100,001 IDs between the last one and current.

This really doesn’t matter all that much, but if you’re very anal retentive like me and have a dataset that isn’t all that important (I wouldn’t really recommend this for a mission critical application, unless you really have to), then here is how you can get back on track as if the cursor never blew up your identity in the first place.

The basic steps are as follows:

1. Set your table’s identity to the last good record (before you blew it up with your cursor or while loop). In the example, that would be ID 50,000
2. If you inserted into your table after you blew up Copy the data for each record inserted after your cursor blew up your identity (If not, then skip the following steps – step 1 is good enough), you need to insert copies of all the records that were inserted
3. After inserting these copies, you’ll need to update tables with foreign keys to the new order ID
4. Delete the “original” orders (the ones with ID over 150,000)

You’ll want to do all of this in a transaction so you don’t screw anything up even further. Following with our example, here is a script that would accomplish this task:

DECLARE @originalOrderID int, @newOrderID int
SET @originalOrderID = 150001

DECLARE @customerID int, @warehouseID int, @createDate datetime, @approvalDate datetime, @shipDate datetime, @dueDate datetime, @addressID int

BEGIN TRANSACTION tx
BEGIN TRY
	/* Step 1 - Set table's identity to last good record. In this case, that is 50000 */
	DBCC CHECKIDENT ('[Orders]', RESEED, 50000);
	GO 

	WHILE @originalOrderID <= 150150
	BEGIN
		/* Step 2a - Copy data from records inserted @originalOrderID variable. In this loop, that will run from ID 150001 to 150150 */
		SELECT @customerID = CustomerID, @warehouseID = WarehouseID, @createDate = CreateDate, @approvalDate = ApprovalDate, @shipDate = ShipDate, @dueDate = DueDate, @addressID = AddressID
		FROM Orders
		WHERE ID=@originalOrderID
		
		/* Step 2b Insert the copied data */
		INSERT INTO Orders (CustomerID, WarehouseID, CreateDate, ApprovalDate, ShipDate, DueDate, AddressID)
		VALUES (@customerID, @warehouseID, @createDate, @approvalDate, @shipDate, @dueDate, @addressID)

		/* Step 2c - Grab the new ID created by the insert */
		SET @newOrderID = SCOPE_IDENTITY()

		/* Step 3 - Update foreign key references by replacing the original order ID with the new one we just inserted */
		UPDATE OrderItems
		SET OrderID=@newOrderID
		WHERE OrderID=@originalOrderID

		UPDATE ShippingBoxes
		SET OrderID=@newOrderID
		WHERE OrderID=@originalOrderID

		/* Step 4 - Delete the original order */
		DELETE 
		FROM Orders
		WHERE ID=@originalOrderID

		/* Don't forget to increment your variable - that's how we got in this mess in the first place */
		SET @originalOrderID = @originalOrderID + 1
	END
COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	PRINT ERROR_MESSAGE()
END CATCH

One thing to note about this – the DBCC CHECKIDENT function (MSDN docs here: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017) simply resets the seed to whatever you supply. The next insert will be your supplied number + 1. So for our example, the first insert after calling DBCC CHECKIDENT will be ID 50,001. Note that if you reseed your table but don’t delete any inserts above, you will run into insert conflicts down the line. Identity does not check to see if a value exists before inserting, so you will get an error if don’t fix it.

Of course, you could just leave your identity value at the new number and “mind the gap,” as the British would say, but this is an idea for how to deal with it if you choose to.