June 25, 2008 at 1:03 am
How can i re-arrange the auto-increment column..?
For Example : i hv a table with 2000 records, If i delete 100 records from 200 to 299 then is it possible to rearrange the auto-increment column.?
Plz help!!!
June 25, 2008 at 7:01 am
Why would you do that? (I assume you use a primary key field for something like an order number) It's better to separate your data with a primary key, used by SQL and a unique key used by your application. In that case, changing your unique key is a peace of cake (without cascading actions)
Before doing this consider referential integrity. Either by constraints or managed by your application. If you change an id value, make sure all related tables are also updated.
I would suggest a loop. start at the lower value and increase by 1. If the loopnumber doesn't exist, that's a hole. you could replace this id with the highest id value in that table.
this replaces values like 1,2,4,5,7,8,9 with 1,2,3(previous 9),4,5,6 (previous 8),7
Wilfred
The best things in life are the simple things
June 25, 2008 at 8:25 am
Wilfred is correct.
But incase you are not worried about the REf int. and Just want to rest the count, Turn the identity off-> save the table--> turn the identity back on.
all this using Table->Design
Maninder
www.dbanation.com
June 25, 2008 at 8:47 am
Yes, it is possible, but in most cases, it's a bad idea.
What is the reason to change these numbers?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 12:43 pm
Hi,
Why do you want to do that? If something is available in SQL server to generate the numbers then why do you want to build your own? Is there any business logic on the numbers? If you have then thats not the right design.
Please try to use IDENTITY column,otherwise you will get into somany issues later on.
Thanks -- Vj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply