Finding & Using Unused IDs

Finding & Using Unused IDs
If you want to fill in the gaps in IDENTITY columns left by SQL Server when you delete rows from a table, you can quickly get a list of unused identities using the following piece of simple code. Assuming your table is called Customers and the IDENTITY column CustomerID...

DECLARE @LastID BIGINT
SET @LastID = (SELECT TOP 1 CustomerID FROM Customers ORDER BY CustomerID DESC)
WHILE @LastID > 0
BEGIN
   IF (SELECT COUNT(*) FROM Customers WHERE CustomerID = @LastID) = 0
      PRINT @LastID
   SET @LastID = @LastID - 1
END
To insert a specific value into an identiy column, temporarily turn the IDENTITY INSERT property on for that table of the database (you can only have it on for one table at a time).

SET IDENTITY_INSERT Customers ON
INSERT INTO Customers (
      CustomerID,
      CustomerName
   )
   VALUES (
      27,
      'Somebody'
   )
SET IDENTITY_INSERT Customers OFF
© Copyright Quantum Software Solutions 2003, All rights reserved.