Auto increment column after delete and insert

Question

5.00/5 (1 vote)

See more:

After i delete a row and then insert a new row the auto increment row wont assign the correct next number. example if i have

1 last first
2 last first
3 last first

after delete

1 last first
2 last first

after insert

1 last first
2 last first
4 last first

I need some help fixing this, any help would be great thanks.

Posted 31-Oct-10 12:25pm

ajep

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nish Nishant · Accepted Answer · 2010-10-31T14:25:00

Solution 2

[Adding to John's response]

If you want to understand why, think of what happens if you had this:

1 last first
2 last first
3 last first

And then you deleted the 2nd row to get this:

1 last first
3 last first

Now when you do an insert what would you want the index to be? 3 would certainly be wrong (since it already exists). Do you want it to go find that 2 is missing and thus use 2? What if there are multiple such deleted rows?

Posted 31-Oct-10 14:25pm

Nish Nishant

Updated 31-Oct-10 14:26pm

v2

Comments

ajep 31-Oct-10 20:48pm

Thanks for the help, but i made that column edible because it was an identity with auto+
so i did this with help
INSERT INTO table1 (ID, .....) VALUES ((SELECT MAX(ID) + 1 FROM table1), .....)

AspDotNetDev 31-Oct-10 22:58pm

Ajep, that may get you into trouble if you have more than one of those commands hitting the database at once. You could end up with 2 (or more) rows that have the same value.

#realJSOP · Accepted Answer · 2010-10-31T13:30:00

It's not broken and you shouldn't try to "fix" it. SQL Server maintains the index of the last inserted item. It's supposed to be a unique value, and the only way that can be guaranteed is if you allow SQL Server to maintain that index. You *can* reset the value, but you'd have to reset it again when you've filled the "missing" index.

Take my word for it, you're worried about nothing.