1

I have a table like below:

+--------+--------+
| TestNo | Value  |
+--------+--------+
|      1 | Test1  |
|      2 | Test2  |
|      3 | Test3  |
|      6 | Test6  |
|      7 | Test7  |
+--------+--------+

in which TestNo is the Primary Key and have the following values. Notice that the 4, 5 TestNo values are missing here. What I need is to insert the new value to start with TestNo 4. So I did this:

dbcc checkident('tbl', reseed, 3)

It worked and after inserting 2 records: 

+--------+-------+
| TestNo | Value |
+--------+-------+
|      1 | Test1 |
|      2 | Test2 |
|      3 | Test3 |
|      4 | Test4 |
|      5 | Test5 |
|      6 | Test6 |
|      7 | Test7 |
+--------+-------+

but after inserting the 3rd record I get following error:

Violation of PRIMARY KEY constraint 'PK__QCUnitTe__554AD375D32C4684'. Cannot insert duplicate key in object 'dbo.QCUnitTest'. The duplicate key value is (6). The statement has been terminated.

So is there any way to overpass the value if it exists? Like in my case it should overpass it to 8.

Ahsan Ali
  • 4,951
  • 2
  • 17
  • 27
  • Don't worry about gaps in auto-incremented primary keys. That is the simplest method to deal with this. – Gordon Linoff Jul 31 '18 at 10:31
  • 1
    No, that's not how identities work. [See also](https://stackoverflow.com/q/4597183/4137916), [see also](https://stackoverflow.com/q/14642013/4137916). When people ask for this, they usually fail to realize that gap-free numbering isn't something they *actually* need, and while you can get it if you really want it, you'd usually not be willing to pay the price (the code that does this would inevitably introduce locking overhead, even if you write it carefully). – Jeroen Mostert Jul 31 '18 at 10:35
  • @Gordon This is just a sample. Actually, the gap is very bigger like more than 10 thousand. The gap occurred because the server got restarted unexpectedly and new values inserted with a big jump of 10,000. – Ahsan Ali Jul 31 '18 at 10:36
  • @AhsanAli If this is so big deal, maybe you can fix the values with one statement (once time) and then leave the `identity` to work (till the next server restart). Yes? – gotqn Jul 31 '18 at 10:38
  • 4
    But why does it matter still? If you need an incrementing value, use `ROW_NUMBER`. Perhaps create a view with `ROW_NUMBER() OVER (ORDER BY TestNo)`. The value of your `IDENTITY` doesn't matter. Don't try to use `IDENTITY` as an incrementing value. Otherwise you'll have a never ending cycle of updates every time you perform a `DELETE` and possible an `UPDATE` (depending on your requirement). – Thom A Jul 31 '18 at 10:39
  • As @Larnu Writes. Why not just use row_number() ? The increment value of your identity doesnt matter hence its always unique. So if it says 4 or 10004 it really doesnt matter? – SqlKindaGuy Jul 31 '18 at 11:34

1 Answers1

1

No, there is no way to have an IDENTITY column automatically skip values that are already present.

The only thing you can do to fill in gaps in an IDENTITY column (while keeping your existing values) is to temporarily turn IDENTITY off and manually fill in the gaps, then switch IDENTITY back on.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52