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.