A collection customers each might have an AccountNumber, so to be able to produce a new account, I'll increment the greatest existing account number by 1. How does one keep an eye on the final released number? Obviously you could loop through all accounts to check on, but can there be any reason you might possibly made a decision to have a separate table of either the final released number, or all amounts?

I am just searching in a couple of 1000 accounts, therefore it is not too i am concerned about optimisation.

You need to most likely use [cde] if you are using MS SQL, or equivalent if you are with a couple other DB.

Which will keep an eye on the ids correctly, even if a couple are attempting to create a free account simultaneously.

If you work with Oracle make use of a sequence. http://www.techonthenet.com/oracle/sequences.php
For MSSQL use something similar to this. http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

The question was initially labeled .Internet and C# and so i assumed MSSQL, so it is not. I'm not sure the amount of this response is valid when combined with Sqlite.

You will find several methods additional. I believe these two are most fascinating:

  1. Make use of an IDENTITY column, that will instantly take proper care of race conditions when two processes create accounts simultaneously, folded back transactions along with other things. The down-side by having an IDENTITY posts is you could get holes inside your number series. If this isn't acceptable, you should utilize the another approach.

  2. Handle the amounts yourself. I'd should you prefer a special table, that contains the following free number. Whenever you produce a new account you need to do this in the transaction, utilizing an isolation level that locks the free-number table immediately while you're reading it and keeps the lock before the up-to-date number is saved. This really is awkward to complete and can give less scalability, however, you can promise that you will get continous amounts.

In either case, you ought to have a distinctive key around the account number column being an extra safety.

I am afraid I am unfamiliar with SQLlite. But many SQL engines nowadays possess some kind of "autonumber" feature.

Failing that, produce a sequence. The DB engine should manage the succession for you personally which means you never get duplicate amounts.

If for whatever reason neither of individuals things can be done, you'll be able to produce a separate table to carry the final-used id. Make sure to see clearly using "choose for update" to prevent race conditions.

If you work with SQL Server you should use make use of the Max function, that will return the greatest worth of the row you appear at.

Update: Simple Example. IDENTITY

Ok, Maybe I had been wrong avoid using Max, as that will cause some problems. :( Sorry.