Table ID (PrimaryKey) does not increment in a proper sequence when a new record is inserted

Question

When I delete a record in a table using SQL Server 2008 and its Management Studio, and then insert a new record, sequence of primary key column is not in order.

Suppose I delete Record5 with record_id = 5. Table is left with 4 records i.e., Record1,Record2,Record3,Record4.

Now when I insert a new record .. its ID (primary key) is automatically set to 6. I need SQL Server to set it as 5.

Because it looks weird when I display this table in a gridView in Asp.Net (c#), my table's record_id column sequence is something like 1, 2, 3, 17, 18, 29 etc.,

It looks very bad. Please help.

Thanks

You shouldn't be exposing these ID values to users - if you need a number for display purposes, look into calling `ROW_NUMBER()` in your `SELECT` statement. You should treat identity values as opaque blobs that you just happen to be able to store in an `int` - you shouldn't in any way rely on their actual numeric value. — Damien_The_Unbeliever, Jul 11 '13 at 09:42
@Damien_The_Unbeliever : Kindly help me to get out of this weird situation I am in .. your piece of advice is sure valuable but cannot be entertained at the moment. I need to get out of the situation. Please help. — , Jul 11 '13 at 09:48
I'd assume that, if record 1 was deleted, you want all existing rows shuffled up by 1 also? If so, it can't be done whilst the column is marked as an `IDENTITY` column - so *any* fix is going to require re-work somewhere - why not fix things properly? — Damien_The_Unbeliever, Jul 11 '13 at 09:51
Well, I simply don't think you are in any situation and there are any problems here. If record 5 is deleted, it stays deleted. You can't have 5 again, 6 is next. I would disagree that it looks bad. — Nenad Zivkovic, Jul 11 '13 at 09:58
@RemusRusanu: The original asks why identity columns are not strictly increasing. This question asks how you can get a strictly increasing number. How is that duplicate? — Andomar, Jul 11 '13 at 10:10

score 3 · Answer 1 · answered Jul 11 '13 at 09:50

There is no guarantee that the IDENTITY values will not have gaps. In fact is very likely to encounter such gaps. You must design your application in a way that accommodates and expects such gaps in identity values. To display a row number in a grid the best option, by far, is to use a local client-side (ASP.Net or even browser side JS) counter. You can generate the counter server side using ROW_NUMBER() but is not the best options compared to client side.

score 2 · Answer 2 · edited May 23 '17 at 11:52

2

You need to order the diplay of your data separately from the identity column in your database. The identity column is not for "display order", in fact, it does not have any discernable order - it will always have holes.

Consider a table with 4 rows. Now you add 5, then delete 5 rows. The next column you enter with have its ID as 10, even though you only have 5 rows in the table.

So, stop relying on that. Instead use Container.ItemIndex and use that to display the appropriate count of the number of rows being shown.

edited May 23 '17 at 11:52

Community

1
1

answered Jul 11 '13 at 10:15

Burhan Khalid

169,990
18
245
284

+1 because of the practical solution for client-side numbering – Remus Rusanu Jul 11 '13 at 11:25

score 1 · Accepted Answer · edited Jul 11 '13 at 10:03

1

This may sound unbelievable, but if you want an incrementing record number, SQL Server has no support for you. A transaction that is rolled back or a server restart can leave holes in the numbers.

For a strictly increasing order, you have to roll your own implementation. One common solution is to create a table with a list of most recently handed out numbers. If you retrieve and increase the number in an atomic manner, that is thread-safe. For example:

update  NumbersTable
set     Nr = Nr + 1
output  deleted.Nr
where   Type = 'OrderNumber'

Another option is to dynamically retrieve the highest order number. With the appropriate locking hints, that can be done in a thread-safe way:

insert  OrdersTable
        (OrderNr, col1, col2, col3)
select  isnull((
        select  max(OrderNr) + 1
        from    OrdersTable with (tablock, holdlock)
        ), 1)
,       'value1'
,       'value2'
,       'value3'

If you delete a row, you'll have to implement that manually. Say that records 1, 2 and 3 exist. You delete record 2. What number should the new order get? If you say 2, remember that means order 2 is created after order 3, which would confuse a lot of people.

edited Jul 11 '13 at 10:03

Koterpillar

7,883
2
25
41

answered Jul 11 '13 at 09:49

Andomar

232,371
49
380
404

@ Andomar : Thats true. But how it works in MS Excel ... you delete a row .. row below it takes over its index. I think if somehow i get that implemented, it will hopefully solve my situation. (meet my requirements) – Jul 11 '13 at 09:59
You could run a `update YourTable set NrColumn = NrColumn - 1 where NrColumn > @deletedNr` after your delete statement. Make sure that the column in question is not used as a foreign key or relied on by any user. – Andomar Jul 11 '13 at 10:03
@RehanMehdi Excel is not a database server with relational constraints. – Burhan Khalid Jul 11 '13 at 10:06
@RehanMehdi - Excel shows you row numbers that it's computed for display purposes. It doesn't show you an internal pointer's value. If you're exposing an `IDENTITY` value to users, you're doing the moral equivalent of showing them an internal pointer. – Damien_The_Unbeliever Jul 11 '13 at 10:09
@Damien_The_Unbeliever Haha. True that. Have started hiding it now. haha . Thanks for the tips. – Jul 11 '13 at 10:38
@Andomar Thanks for the tip. I am trying to hide the primarykey column from my users. I think better to end the story by killing the culprit. – Jul 11 '13 at 10:41

score 0 · Answer 4 · answered Jul 11 '13 at 09:48

0

After your Delete statement run this Queries.

declare @a int

set @a =(select max(identity_column_name) from table_name)
--print @a

dbcc checkident(table_name,reseed,@a)

answered Jul 11 '13 at 09:48

Prahalad Gaggar

11,389
16
53
71

@downvoter please help me in finding my mistake. – Prahalad Gaggar Jul 11 '13 at 09:49
3

Not my downvote, but this is clearly not thread safe – Andomar Jul 11 '13 at 09:49
@Andomar What is thread safe? – Prahalad Gaggar Jul 11 '13 at 09:51
Thread safe means concurrent, i.e. that it will work if called by many clients simultaneously. You can simulate concurrency with while loops running in multiple tabs in SSMS. – Andomar Jul 11 '13 at 09:52
@Luv Is there anyway I can automate this query whenever a deletion process takes place on my table? – Jul 11 '13 at 10:02
@RehanMehdi I can't think of any automated Query. – Prahalad Gaggar Jul 11 '13 at 10:03

Table ID (PrimaryKey) does not increment in a proper sequence when a new record is inserted

4 Answers4