Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2007, 01:04 AM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to wackoyacky
Default Custom Generate IDs

Hi All!

I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.

I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.

Any inputs would be greatly appreciated.

Thanks!

Reply With Quote
  #2 (permalink)  
Old June 4th, 2007, 02:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Everytime you insert, depending on the DEPT, you should retrieve the last ID for the DEPT and Add one to it and insert, thus maintaining uniqueness. I am not sure if you are trying to do this from your database server or from the frontend application.

Since you use only three digits, this will restrict only to 999 rows per dept. You might consider increasing the digits based on the load you can think of for each dept.

Hope this helps.
Cheers
_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old June 4th, 2007, 08:03 AM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to wackoyacky
Default

Hi Vijay!

Actually that's what I have right now, but the thing is there could be a concurrency issue in that setup, because it is a two process 1.) Update the seed table, 2.) Insert a new record on the main table using the generated ID. Anything could happen between the two process.

I will not be restricting it to three digits, it's just that the minimum is 3 digits,

Thanks.
Reply With Quote
  #4 (permalink)  
Old June 4th, 2007, 08:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,

Is your code put under BEGIN TRAN... COMMIT TRAN Blocks? So that either it completes the entire two step transactions or rolls back in case of any issues. That should take care of concurrency.

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old June 4th, 2007, 08:53 AM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to wackoyacky
Default

I have, but someone told me that it will still cause deadlocks. I guess I really have to test it myself.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Generate Unique values for IDs mphare XSLT 7 November 6th, 2008 12:20 PM
ids dhoward VB.NET 2002/2003 Basics 4 December 17th, 2007 05:46 PM
Email IDs rohit_ghosh Excel VBA 2 July 16th, 2007 03:57 PM
Auto generate IDs in MS SQL... Lynn SQL Server 2000 8 November 28th, 2006 10:00 AM
Dialog Help Ids sumeetpm Visual C++ 0 October 31st, 2006 03:19 PM



All times are GMT -4. The time now is 03:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.