Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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!

 
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
 
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.
 
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
 
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.





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.