SQL Server Performance Forum – Threads Archive

How to fill gaps after deleting rows?

( After reading the posting of Cesar I am posting this question.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8604&SearchTerms=identity ) After deleting the rows from table, which contains an Identity column, will create so many gaps I mean for example the primary column values like this: 1, 2, 3, 15, 16, 25, 26, 27, 50, 51, 75, 76, 105, 106, 107, 180, 181, 182, etc.
When I will insert new row in this table it will be (Max Value + 1).
My question are:
1. How we can fill the gaps in between while inserting the row?
2. For this whether we need to remove Identity column?
3. How will be the SQL statement to find out the gaps and how we can insert it?
Surendra Kalekar

I think one possible way is to drop that column and add identity column again Otherwise run this
DBCC CHECKIDENT (tablename, RESEED, 0)
It will reset id so that new record start with id 1 Madhivanan Failing to plan is Planning to fail

I think the question is not "how" you do this, but rather "why" you want to do this.
A "perfect" sequence is a presentional issue. Not something you should do on the server.
There are quite a few methods to determine gaps. See if these help:
CREATE TABLE #gap
(
Nbr INT
)
INSERT INTO #gap
SELECT 1
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 10 SELECT
s + 1 AS start_value
, e – 1 AS end_value
FROM
(
SELECT
t1.Nbr
, MIN(t2.Nbr)
FROM #gap t1, #gap t2
WHERE t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
) D ( s, e )
WHERE e – 1 > s — or SELECT
t1.Nbr + 1 AS start_value
, MIN(t2.Nbr) – 1 AS end_value
FROM #gap t1
INNER JOIN #gap t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN(t2.Nbr) – t1.Nbr > 1
DROP TABLE #gap But then again, I don’t think there is a need for maintaining the sequence in your data. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Thanks Madhivanan and Frank,
I am not going to implement this, but this will be last choice if required.
Surendra Kalekar

I’d have to also ask "why" you want to do this…but if it is something that you implement, the following code is a bit slimmer than the above and can also get you the first missing ID in a table. SELECT MIN(d.myid) + 1
FROM #data d LEFT JOIN #data c
ON d.myid + 1 = c.myid
WHERE c.myid IS NULL Where #data is the table containing the IDENTITY column and myid is that column.

]]>