Avatar of 3636
3636
 asked on

SQL row will automatically generate a row # but will not revert to it after deletion of that row.

When selecting “Edit Top 200 Rows” and adding another line of info to the table it works fine and automatically added a consecutive # to the “Ithgschool_Ext_ID” column.
But when I delete the newly added row, the next time I insert another new row, the consecutive # is skipped. In this case there are rows 1, 2, and jumps straight to 5.
Now, if I delete row 5, the next time I insert another row, that column will automatically generate the number 6, so it will show 1, 2, and 6.
How can I have the consecutive order show up again?


(see screen shot)
Rows-in-SQL.docx
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Member_2_276102

8/22/2022 - Mon
SOLUTION
Rich Weissler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
3636

ASKER
1 -

I tried to manually change the number to the consecutive order but it will not change,
make sure Num Lock is on.

2-

What advantage do you get having "SET IDENTITY_INSERT ON"?


3-

Will NOT having consecutive numbers in that column reflect any behavior in the .net Application this database is supporting?

Thanks.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
3636

ASKER
1 - Table definition - see attachment.
3-  why do you think you need consecutive numbers without gaps... - because it done not make sense to have row #  1,2, and 6, without 3,4, and 5, when 3,4, and 5 have been deleted.

I mean, why should the unused row #s not be able to appear again in the table?
Table-definition.docx
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes