Does anyone know a neat trick for reusing identity values?

Question

Typically when you specify an identity column you get a convenient interface in SQL Server for asking for particular row.

SELECT * FROM $IDENTITY = @pID

You don't really need to concern yourself with the name if the identity column because there can only be one.

But what if I have a table which mostly consists of temporary data. Lots of inserts and lots of deletes. Is there a simple way for me to reuse the identity values.

Preferably I would want to be able to write a function that would return say NEXT_SMALLEST($IDENTITY) as next identity value and do so in a fail-safe manner.

Basically find the smallest value that's not in use. That's not entirely trivial to do, but what I want is to be able to tell SQL Server that this is my function that will generate the identity values. But what I know is that no such function exists...

I want to...

Implement global data base IDs, I need to provide a default value that I'm in control of.

My idea was based around that I should be able to have a table with all known IDs and then every row ID from some other table that needed a global ID would reference that table. The default value would be provided by something like

INSERT INTO GlobalID
RETURN SCOPE_IDENTITY()

Answer 1

No; it's not unique if it can be reused.

Why do you want to re-use them? Why do you concern yourself with this field? If you want to be in control of it, don't make it an identity; create your own scheme and use that.

Answer 2

If you really want to reset Identity value to the lowest,
here is the trick you can use through DBCC CHECKIDENT

Basically following sql statements resets identity value so that identity value restarts from the lowest possible number

create table TT (id int identity(1, 1))
GO
insert TT default values
GO 10
select * from TT
GO
delete TT where id between 5 and 10
GO
--; At this point, next ID will be 11, not 5
select * from TT
GO
insert TT default values
GO
--; as you can see here, next ID is indeed 11
select * from TT
GO
--; Now delete ID = 11 
--; so that we can reseed next highest ID to 5
delete TT where id = 11
GO

--; Now, let''s reseed identity value to the lowest possible identity number
declare @seedID int
select @seedID = max(id) from TT
print @seedID --; 4

--; We reseed identity column with "DBCC CheckIdent" and pass a new seed value
--; But we can't pass a seed number as argument, so let's use dynamic sql.
declare @sql nvarchar(200)
set @sql = 'dbcc checkident(TT, reseed, ' + cast(@seedID as varchar) + ')'
exec sp_sqlexec @sql
GO

--; Now the next 
insert TT default values
GO
--; as you can see here, next ID is indeed 5
select * from TT
GO

Answer 3

I guess we would really need to know why you want to reuse your identity column. The only reason I can think of is because of the temporary nature of your data you might exhaust the possible values for the identity. That is not really likely, but if that is your concern, you can use uniqueidentifiers (guids) as the primary key in your table instead.

The function newid() will create a new guid and can be used in insert statements (or other statements). Then when you delete the row, you don't have any "holes" in your key because guids are not created in that order anyway.

Answer 4

Don't reuse identities, you'll just shoot your self in the foot. Use a large enough value so that it never rolls over (64 bit big int).

To find missing gaps in a sequence of numbers join the table against itself with a +/- 1 difference:

SELECT a.id
FROM table AS a
LEFT OUTER JOIN table AS b ON a.id = b.id+1
WHERE b.id IS NULL;

This query will find the numbers in the id sequence for which id-1 is not in the table, ie. contiguous sequence start numbers. You can then use SET IDENTITY INSERT OFF to insert a specific id and reuse a number. The cost of doing so is overwhelming (both runtime and code complexity) compared with the an ordinary identity based insert.

Answer 5

[Syntax assumes SQL2008....]

Yes, it's possible. You need to two management tables, and two triggers on each participating table.

First, the management tables:

-- this table should only ever have one row
CREATE TABLE NextId (Id INT) 
INSERT NextId VALUES (1)
GO

CREATE TABLE RecoveredIds (Id INT NOT NULL PRIMARY KEY)
GO 

Then, the triggers, two on each table:

CREATE TRIGGER tr_TableName_RecoverId ON TableName 
FOR DELETE AS BEGIN
   IF @@ROWCOUNT = 0 RETURN
   INSERT RecoveredIds (Id) SELECT Id FROM deleted
END
GO

CREATE TRIGGER tr_TableName_AssignId ON TableName 
INSTEAD OF INSERT AS BEGIN
  DECLARE @rowcount INT = @@ROWCOUNT 
  IF @rowcount = 0 RETURN 
  DECLARE @required INT = @rowcount 
  DECLARE @new_ids TABLE (Id INT PRIMARY KEY)
  DELETE TOP (@required) OUTPUT DELETED.Id INTO @new_ids (Id) FROM RecoveredIds
  SET @rowcount = @@ROWCOUNT
  IF @rowcount < @required BEGIN 
    DECLARE @output TABLE (Id INT)
    UPDATE NextId SET Id = Id + (@required-@rowcount) 
    OUTPUT DELETED.Id INTO @output
    -- this assumes you have a numbers table around somewhere
    INSERT @new_ids (Id) 
    SELECT n.Number+o.Id-1 FROM Numbers n, @output o
    WHERE n.Number BETWEEN 1 AND @required-@rowcount
  END
  SET IDENTITY_INSERT TableName ON
  ;WITH inserted_CTE AS (SELECT _no = ROW_NUMBER() OVER (ORDER BY Id), * FROM inserted)
      , new_ids_CTE  AS (SELECT _no = ROW_NUMBER() OVER (ORDER BY Id), * FROM @new_ids)
  INSERT TableName (Id, Attr1, Attr2)
  SELECT n.Id, i.Attr1, i.Attr2 
  FROM inserted_CTE i JOIN new_ids_CTE n ON i._no = n._no
  SET IDENTITY_INSERT TableName OFF
END

You could script the triggers out easily enough from system tables.

You would want to test this for concurrency. It should work as is, syntax errors notwithstanding: The OUTPUT clause guarantees atomicity of id lookup->increment as one step, and the entire operation occurs within a transaction, thanks to the trigger.

TableName.Id is still an identity column. All the common idioms like $IDENTITY and SCOPE_IDENTITY() will still work.

There is no central table of ids by table, but you could create one easily enough.

Answer 6

I don't have any help for finding the values not in use but if you really want to find them and set them yourself, you can use

set identity_insert on ....

in your code to do so.

I'm with everyone else though. Why bother? Don't you have a business problem to solve?