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?
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.
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
You can also read this blog post http://weblogs.sqlteam.com/peterl/archive/2009/03/10/How-to-efficiently-reuse-gaps-in-identity-column.aspx
