• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

sql server auto increment

I have an asp.net website.
Now user can insert his data from this site to my sql server database.
Each table in the database has Auto increment set to true.

The process of uploading user data into my sql database is user first has to delete his old data from my database and then insert new data.
Now becasue of auto increment set to true my sql server always take last Number and increment it by 1 and insert a new record.
but in there chances that older IDs are empty but becasue of auto increment it always get new id.

I am getting this felling that soon it will fail becasue all the primary key are of int type and auto increment may reach to max of int.

is there any solution that i can overcome this problem
1 Solution
Om PrakashCommented:
If you use autonumber, new id is assigned everytime a new record is inserted.

If you think data will be more if you use INT then you can use BIGINT datatype which can store value upto

If you want to reuse the number, remove auto-increment and just use Number. While deleting a record, copy its record ID to some table called EmptyIdList (you'll have to create this table). It will have simple structure as you just have to store a number.

While adding a new record, take the smallest/largest (you decide the criteria) number from EmptyIdList table and insert the record. Once the record is inserted successfully, don't forget to remove this entry from EmptyIdList table.

It will increase your coding effort but you can make best use of it...!!!

Hope this helps..!!!
ziorrinfotechAuthor Commented:
Thanks a ton
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now