2

Is there any way to have a primary key with a feature that increments it but fills in gaps? Assuming I have the following table:

____________________
| ID  |    Value    |
|  1  |      A      |
|  2  |      B      |
|  3  |      C      |
^^^^^^^^^^^^^^^^^^^^^

Notice that the value is only an example, the order has nothing to do with the question.

Once I remove the row with the ID of 2 (the table will look like this):

____________________
| ID  |    Value    |
|  1  |      A      |
|  3  |      C      |
^^^^^^^^^^^^^^^^^^^^^

And I add another row, with regular auto-increment feature it will look like this:

____________________
| ID  |    Value    |
|  1  |      A      |
|  3  |      C      |
|  4  |      D      |
^^^^^^^^^^^^^^^^^^^^^

As expected.

The output I'd want would be:

____________________
| ID  |    Value    |
|  1  |      A      |
|  2  |      D      |
|  3  |      C      |
^^^^^^^^^^^^^^^^^^^^^

Where the gap is filled with the new row. Also note that maybe, in memory, it would look different. But the point is that the primary key would fill the gaps.

When having the primary keys (for instance) 1, 2, 3, 6, 7, 10, 11, 4 should be first filled in, then 5, 8 and so on... When the table is empty (even if it had a million of rows before) it should start over from 1.

How do I accomplish that? Is there any built-in feature similar to that? Can I implement it?

EDIT: If it's not possible, why not?

Jim
  • 125
  • 1
  • 8
  • 1
    No. And don't do it yourself either. What do you need it for? – juergen d Jun 17 '15 at 20:24
  • 1
    There's just not really a good reason to do this. The only purpose of an auto-generated ID is to be unique, and bigints (or whatever you're using) can rise to such huge values you never really have any chance of reaching the limit, if that's what you're worried about. – Curmudgeon Jun 17 '15 at 20:29
  • @juergend It could ease many actions such as generating a random row using the id (with built-in random functions such as `Random.Next()` or `rand()`). – Jim Jun 17 '15 at 20:29
  • [You can select random rows without using the ID.](http://stackoverflow.com/a/1209946/4999096) – Curmudgeon Jun 17 '15 at 20:32
  • 1
    A random generator for a PK is not a good idea, it would cause page splits and reordering, impacting performance and storage. PKs are most optimal when they are inserted ordered. – Greg Jun 17 '15 at 20:47
  • @Curmudgeon Notice that I wrote 'ease' and not 'enable'. – Jim Jun 17 '15 at 20:55
  • If you are worried about gaps in an identity then you shouldn't be using an identity. It can and will have gaps. This is perfectly natural. Also, when the service is restarted with 2012 or later you will see large gaps. http://sqlblog.com/blogs/kalen_delaney/archive/2014/06/17/lost-identity.aspx – Sean Lange Jun 17 '15 at 21:05

1 Answers1

3

No, you don't want to do that, as juergen-d said. It's unlikely to do what you think it is doing, and it will do it even less in a multi-user environment. In a multiuser environment you are likely to get voids even when there are no deletes, just from aborted inserts.

Seeds
  • 266
  • 1
  • 7
  • Exactly. If I'm not mistaken, keeping track of "holes" like this also becomes incredibly inefficient/cumbersome to do as the database grows, especially in an environment with frequent deletion. – Curmudgeon Jun 17 '15 at 20:37
  • I suppose if one were truly married to the idea one could simply delete the value in column 2, and write a custom insert to run a full table scan and insert "new" values into blank positions, that wouldn't be much more than O(n) except that multiple users would make it more complex. Still not seeing the benefit. – Seeds Jun 17 '15 at 20:41
  • 1
    That is sure to break in a concurrency scenario. – Greg Jun 17 '15 at 20:48