quote:
Gee, that's a lot of budgets. You, know, the IDENTITY field was designed for this. It would be a LOT faster, easier, and simpler to use the tools that were designed for the job instead of re-invent the wheel. The max size limit of INT is over 2 trillion, so I don't think you're going to be coming anywhere near that, and if you do, your solution of finding available IDs would be so slow that you'd scrap the idea long before the ID value reached the millions...
well said!
I still don't think it's necessary to keep ongoing numbering stored in the db. I assume in addition to BudgetID there is also stored some client identification ID. So the numbering does not apply to this client but only to the table as a whole.
Anyway, just another thought. What about a delete trigger and a lookup table?
Every time a DELETE operation occurs, BudgetID is stored in the lookup table. Next time one needs to fill the holes, he just needs to query the MIN from this table. Not sure what happens when the INSERT transaction fails and how to catch this.
Just another monday morning, one cup of coffee thought 
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]