How to insert a row with lowest possible key?

Hi, this seems to be an easy question but I can't find a quick answer to it.

I have an asp page that communicates with a database and I want the following example to work...

Insert 5 records into the DB with primary keys 1-5.

Remove record with key number 2.

Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.

Does auto increment work like this?

If not, how do I do it?

Thanks for any replies!

Niklas

Auto-increment doesn't work like this.

I'm not sure that you reasons for doing this are sound. I don't think it'll save space really. An INT is an INT so you will only be storing 4bytes no matter what the size of the number. And while they will increase forever, its got to be a pretty big system to hit the limit of 2,147,483,647!!

However, i guess you could do it, but you'll need to run a check like SELECT MIN(Value) FROM Table and then subtracting 1 from that to get your value before running your INSERT statement.

|||

Auto numbers won't fill the gap. You have to write your own logic for this. It might degrade your query performance.

Because,

1. You need to find if there is any gap in existing data

2. You have to find the minimum gap to fill

3. If you already use the auto increment on the current column you have to disable it to fill the gap.

4. If step-1 & step-2 fails then you have to continue with auto increment

Finally,

5. If the concurrent users (let say 2 users) try to insert same id, one will be pass. Another user's transaction

will fail, you have to take care this failure and again you have to find the gap from step1. If n users try to

access this logic the Nth users may need to spend more time to insert his record.

Do you want to continue still?

|||This looks really strange, but it's working for me. The query in red is where the magic happens; everything else is just creating a test environment (run the whole thing in AdventureWorks/Northwind/pubs to see it in action).

Code Snippet

--Make a test table
IF object_id('junk') IS NOT NULL
DROP TABLE junk
GO
CREATE TABLE junk (
id int identity(1, 1),
data char(4)
)
GO

--Put in some test data
SET NOCOUNT ON
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 100
BEGIN
INSERT INTO junk (data) VALUES (@.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

--Remove some records
DELETE FROM junk WHERE id IN (4, 5, 56, 17, 9, 82)
GO

--Find the lowest available number
SELECT MIN(j1.id) + 1 AS [next]
FROM junk j1
LEFT OUTER JOIN junk j2
ON j1.id + 1 = j2.id
WHERE j2.id IS NULL

|||Another issue is that if your database does not have cascading deletes, the new records may have foreign key usage in other tables that is not intended for the current record.sql