Another common way to generate id's is to create a table that contains your sequences like prefix, lastid. Prefix Lastid CC 1 AB 5000 etc
You can then use an index on prefix with update statements in a transaction to guarantee you'll be the only one getting an id and that if your insert fails for some reason, you won't use up an id (unlike auto incrementing numbers). If you wrap the next number extraction into a stored procedure it becomes very easy to use and portable. The biggest downside to all of these methods though for a high activity database is hot spots.
Another common way to generate id's is to create a table that contains your sequences like prefix, lastid. Prefix Lastid CC 1 AB 5000 etc
You can then use an index on prefix with update statements in a transaction to guarantee you'll be the only one getting an id and that if your insert fails for some reason, you won't use up an id (unlike auto incrementing numbers). If you wrap the next number extraction into a stored procedure it becomes very easy to use and portable. The biggest downside to all of these methods though for a high activity database is hot spots.
There are serious disadvantages to this method. 1. It creates a single table that participates in virtually every transaction in the database. One poorly handled error in a transaction will leave an update lock on this table, and block every user in your database. 2. It makes it much harder to insert more than one row at a time, especially with multiple users doing inserts at the same time.
Unique id's
Posted - 05/30/2007 : 20:11:42
