Increase number by 1

Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.

I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.

Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.

I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?

Thanks.
Richard M.hi richard,
i guess you need to do this by coding urself. u can use the feature in Sql server to increment the number by one but i don't think its possible to replace the deleted number.

so the better solution will be to add the incrementing number programatically. first declare int data type in sql server and assign 1 for the first record. for new records, check whether any number is missing and try to add new into that.

for eg, if you have 10 records, then no of 10th record should be 10 else some record is deleted. so u can use loop to check which number is missing.

i hope u can do the coding.