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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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..!!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ziorrinfotechAuthor Commented:
Thanks a ton
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.