Is there a "hack" that would allow you to reuse identity numbers that were orphaned by

I guess this is a fairly common topic but couldn't find the right words to find anything in a search.

What I'm getting at, is there any tsql functions or combination of commands for the following.

You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.

My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.

Thanks.

There is no built-in method to use gaps in identity values. You will have to write your own SQL code to do that. Please take a look at the script for a method to identity gaps in sequences.

CREATE TABLE #t ( seq int identity );
WHILE( SELECT COALESCE( MAX( seq ) , 0 ) FROM #t ) < 18
INSERT #t DEFAULT VALUES
DELETE #t WHERE seq%3 = 0
DELETE #t WHERE seq = 11
go
SELECT * FROM #t
-- Sample data:
/*
seq
--
1
2
4
5
7
8
10
13
14
16
17
*/

SELECT a.seq AS GapAfterSeq
FROM #t a
WHERE NOT EXISTS( SELECT * FROM #t b
WHERE b.seq = a.seq + 1 ) and
a.seq < ( SELECT MAX( seq ) FROM #t )
/*
GapAfterSeq
--
2
5
8
10
14
*/

-- Setup more sample data for boundary condition for test below:
DELETE #t WHERE seq = 1
SELECT * FROM #t
/*
seq
--
2
4
5
7
8
10
13
14
16
17
*/
GO

/*
This is a very generic form of query that can be used to
determine a gap given a range. The above query cannot
determine if a value is not present & is less than the existing
minimum value. Hence, this form of the query can handle that
boundary conditions too.
*/
DECLARE @.rangemin int , @.rangemax int
SELECT @.rangemin = 1 , @.rangemax = 20

SELECT MIN( seq ) + 1 AS NextSeq
FROM (
SELECT @.rangemin - 1 AS seq
WHERE NOT EXISTS( SELECT * FROM #t WHERE seq = @.rangemin )
UNION ALL
SELECT a.seq
FROM #t a
WHERE NOT EXISTS( SELECT * FROM #t b
WHERE b.seq = a.seq + 1 And
b.seq BETWEEN @.rangemin And @.rangemax ) And
a.seq >= @.rangemin And a.seq < @.rangemax
) AS t

/*
NextSeq
--
1
*/
GO
DROP TABLE #t;
GO

|||

In addition to what Umachandar said, it is a general best practice that if you care about the values that will be used, don't use identities. Because of the way the are implemented (basically to be a high performance, high concurrency method of creating a unique value) it is way too much to work with them in this way. They are best for a surrogate key that is usually not shown to the client (or they may want to change it :)

Louis