Re-ordering table's ids

Question

Re-ordering table's ids

gady.male

Old Hand

Points: 334
More actions
April 22, 2010 at 9:38 am

#234139

Hi,
I have a question regarding re-ordering table's id.
I have the following table:
id (int)
data (varchar)
primary key(id)
Becuase of internal business reasons, the application requires that the set of id's must be sequential. I mean that the id's must be 1,2,3...... and we cannot allow having 1,2,5,6,19......
Once a week, the adminsitrator will add/remove data from the table.
In this case, we need to re-order the ids in order to apply to our obligation to the application. So we are going to run over all the ids and re-assigm them with new ids (a sequential id)
The table is going to have some thing like 100,000 records and many records are going to be added/removed each week
As far as i know, each time we change a value in a column that is an index (like primary key), the table needs to re-order itself
So, my questions are:
1. Does re-assigining new numbers to all ids in the table causes any issue?
2. Is there anything I should be aware of, from a preformance perspective, when doing some thing like this?
Thanks,
Gady

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

1. yes it can cause fragmentation if the id is an index column

Rather than physically changing the data in the table, since the ids seem to have no meaning (no FK relationships i hope), then you would be better off ceating a view that usesthe ROW_NUMBER function to provide the id column.

gady.male Old Hand Points: 334 More actions · Answer 2

Thank you all for your responses.

We decided not to re-order the ids on table update.

I mean once a data is deleted no re-assigning occurs and when a new data is added it gets the (max id number + 1).

So we could get something like that:

1,2,5,6,19......

So my questions are:

1. Since we are removing the rows physically from the table, will it leave emtpy rows in the table?

2. Will this cause any performance issue in any way?

3. Will this cause the table to re-fragment itself on every update?

4. Can I schedule the table's re-fragment?

Thanks,

Gady