Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gady.hp
Starting Member
3 Posts |
 Posted - 2010-04-22 : 11:36:17
|
| 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 weekAs 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 |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-04-22 : 12:16:35
|
| First of all I should make one assumption that by primary key you mean that you did not specify that primary key is non-clustered so the primary key is clustered and the cluster index is on id column.After this introduction I can say that reassigning the values of id column will affect the clustered index causing fragmentation and page splitting which will affect the performance badly. So my recommendation is to keep your clustered index on id but to add new column lets call it num that will hold the sequential values, and keep the id values without modification. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 02:26:53
|
| also i dont think this will affect you in any way as you can always retrieve the records in order you want as their order is still correct. And just in case you need sequential values at any case, why not use window function like ROW_NUMBER() based on this id value to generate new sequential number on the fly while retrieving from table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gady.hp
Starting Member
3 Posts |
Posted - 2010-04-23 : 03:10:19
|
| 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 empty 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 04:00:05
|
| 1. nope. it wont2.you mean deleting data?3.nope. you need to do this as a part of maintenance4. you can create maintenance plan------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Re-ordering table's ids