Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lcharlton
Starting Member
1 Post |
 Posted - 2007-05-30 : 20:11:42
|
| Another common way to generate id's is to create a table that contains your sequences like prefix, lastid. Prefix LastidCC 1AB 5000etcYou 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. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-30 : 23:11:18
|
quote:
Originally posted by lcharlton
Another common way to generate id's is to create a table that contains your sequences like prefix, lastid. Prefix LastidCC 1AB 5000etcYou 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.CODO ERGO SUM |
 |
|
|
|
|
|
Unique id's