SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Unique id's
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lcharlton
Starting Member

1 Posts

Posted - 05/30/2007 :  20:11:42  Show Profile  Reply with Quote
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.

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 05/30/2007 :  22:10:17  Show Profile  Visit jezemine's Homepage  Reply with Quote
I am guessing you meant to respond to this thread?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82566


www.elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/30/2007 :  23:11:18  Show Profile  Reply with Quote
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 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.



CODO ERGO SUM
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000