question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

How do I fill in identity gaps?

I have a table with an identity field, but I have gaps in it. My data looks like

CustomerID  customer
----------  ---------
1           Acme
2           Bill's Sports
4           Sears
7           Last Chance Saloon
8           On the Border

If I insert a new row, how can I have it automatically reuse one of my missing values, like #5?

identity
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image
dave ballantyne answered

If you know which particular value you wish to use , you can SET IDENTITY INSERT ON, and simply insert the row. In a wider response though , why would you need to do such a thing? Vary rarely should you attach a 'real world' meaning to an identity value.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Superminister avatar image
Superminister answered

Well, not that simple. You must not reseed the identity value, e.g. to 3, because the attempt to insert data after the next attempt would create another record with CustomerID=4, which leads to the INSERT failing when you have an UNIQUE constraint or you PRIMARY KEY on that column.

What you could do is writing a stored procedure that enables IDENTITY_INSERT for that table and retrieves missing ID values like the following SQL:

SELECT 1+MIN(CustomerID) 
FROM Customers C1 
WHERE NOT EXISTS (SELECT 1 FROM Customers C2 WHERE C2.CustomerID = C1.CustomerID + 1)

... but that will potentially cause new issues. In concurrency situations you should be aware taht you cannot have IDENTITY_INSERT enabled for more than one table in your database simultaneously.

So, if you really depend on having no identity gaps, I'd suggest disabling IDENTITY for that column at all and replacing all INSERTs with calls to a procedure using a code fragment like the above one.

An additional concern I'd have with your idea is: How did the gaps appear? Did you delete customers? Is that feasible? Even when a customer "dies" or let's say stops running his business, you might need its data for regulatory requirements, e.g. showing all shippings for any timespan within the last ten years.

At least our government demands for that, so I'd never delete such data...

Hope that helps. Cheers Christian

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.