CODEWITHSUNDEEP
sqlsql-serverinsertprimary-key
mrK
mrK

Reputation: 2278

Insert missing values from table

I have a table with a PK that grows fairly quickly, but since rows are fairly consistently deleted, it becomes a very sparse table quickly as such:

   ID     VALUE
 ----------------
   1     'Test'
   5     'Test 2'
   24    'Test 3'
   67    'Test 4'

Is there a way that I can automatically insert the next value in the missing IDs so that I don't grow that ID extremely large? For example, I'd like to insert 'Test 5' with ID 2.

Upvotes: 1

Views: 2483

Answers (4)

SQLMason
SQLMason

Reputation: 3275

I'm not suggesting doing what you're trying to do, but if you want to do it, this is how. I am only answering the question, not solving the problem.

In your proc, you'd what to lock your table while doing this so that you don't get one the sneaks in. By using something link this:

EXEC @result = sp_getapplock @Resource = @LockResource, 
                                 @LockMode = 'Exclusive'

AND

EXEC sp_releaseapplock @Resource = @LockResource

TABLE

DECLARE @table TABLE ( id INT, val VARCHAR(20) )

DATA

INSERT  INTO @table
        (
          id,
          val
        )
        SELECT  1,
                'Test'
        UNION ALL
        SELECT  2,
                'Test'
        UNION ALL
        SELECT  5,
                'Test 2'
        UNION ALL
        SELECT  24,
                'Test 3'
        UNION ALL
        SELECT  67,
                'Test 4'

Queries

INSERT  INTO @table
        SELECT TOP 1
                id + 1,
                'TEST'
        FROM    @table t1
        WHERE   NOT EXISTS ( SELECT TOP 1
                                    1
                             FROM   @table
                             WHERE  id = t1.id + 1 )
        ORDER BY id

INSERT  INTO @table
        SELECT TOP 1
                id + 1,
                'TEST'
        FROM    @table t1
        WHERE   NOT EXISTS ( SELECT TOP 1
                                    1
                             FROM   @table
                             WHERE  id = t1.id + 1 )
        ORDER BY id

SELECT  *
FROM    @table

RESULT

id  val
1   Test
2   Test
5   Test 2
24  Test 3
67  Test 4
3   TEST
4   TEST

Upvotes: 2

Christian Specht
Christian Specht

Reputation: 36421

I wouldn't do that.

As already explained by others in the comments, you gain nothing by re-filling gaps in the numbers.

Plus, you might even unintentionally mess up your data if you refer to these IDs anywhere else:
Let's say that there once was a row with ID 2 and you deleted it.
Then you insert a complete new row and re-use ID 2.
Now if you have any data anywhere that references ID 2, it suddenly links to the new value instead of the old one.

(Note to nit-pickers: Yes, this should not happen if referential integrity is set up properly. But this is not the case everywhere, so who knows...)

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

I deleted my answer about identity since they are not involved. It would be interesting to see if you are using this as a clustered index key, since to fill in gaps would violate the rule of thumb of strictly increasing values.

To just fill in gaps is relatively simple with a self-join and since you have a primary key, this query should run quickly to find the first gap (but of course, how are you handling simultaneous inserts and locks?):

SELECT lhs.ID + 1 AS firstgap
FROM tablename AS lhs
LEFT JOIN tablename AS rhs
    ON rhs.ID = lhs.ID + 1
WHERE rhs.ID IS NULL

And inserting batches of records requires each insert to be done separately, while IDENTITY can handle that for you...

Upvotes: 1

kurt
kurt

Reputation: 254

As said before: don't worry about the unused ID's.

It is however good practise to optimize the table when a lot of deletes happen.

In MySQL you can do this with:

optimize table tablename

Upvotes: -2

Related Questions