how to generate primary key in order if it is in disorder

Question

how to generate primary key in order if it is in disorder

seshireddy524

SSC Rookie

Points: 37
More actions
September 8, 2010 at 5:45 am

#237971

hi i am generating primary key with identity 10 and i am incrementing by 10.Unfortunatly the order takes place like 10,50,60.so now i want to place them in order like 10,20,30.please help me.
seshu

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

seshireddy524 (9/8/2010)
hi i am generating primary key with identity 10 and i am incrementing by 10.Unfortunatly the order takes place like 10,50,60.so now i want to place them in order like 10,20,30.please help me.
seshu

Funny! How can you tell that the "order is incorrect?"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

Have you maybe tried to insert the 2nd record and failed to do so because a non-nullable column was left unspecified or an fk-condition was not met? If the insert is rolled back, the identity value assigned will not be returned. i.e. even though the inserts of the records that originally got assigned the values 20, 30 and 40 were rolled back, the counter of the 'last assigned identity value' was left at 40 after 3 failed attempts. Thus the next record inserted got assigned the value of 50.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

crazy4sql SSCoach Points: 19590 More actions · Answer 3

remove primary key and auto increment value.

Rest them to 10 20 30 and assign back the primary key and auto increment

----------
Ashish

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

Why are you worrying about GAPs when using identity column?

Madhivanan

Failing to plan is Planning to fail