Custom Generate IDs - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Custom Generate IDs

Author

Topic

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 03:03:50

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 03:36:32

http://www.sqlteam.com/item.asp?ItemID=26939

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 06:29:49

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 09:40:16

Thanks for all the replies.

@Peso
I already came across that article, but it generates a-z & number combination but not allowing to define a predefine prefix.

@madhivanan
I'll try if I will be able to make it flexible and not to fix the number of digits after the prefix

What I'm trying to achieve is something like:
HR001
HR555
.
.
HR1234
.
.
HR12345

So basically it has a minimum of three digits but will certainly grow after sometime.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 09:50:24

Make a calculated column from an identity column.

= 'HR' + CASE WHEN id < 1000 THEN RIGHT('000' + cast(id as varchar), 3) ELSE cast(id as varchar) end

Peter Larsson
Helsingborg, Sweden

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 11:31:08

Thanks Peter, actually I do have one table that does that. But for this particular table, it would include different prefix or the IDs and for every prefix, it would have it's own set of numbers starting from 1. So it would somehow look like this
HR001
HR555
.
.
IT001
FIN444
.
HR12345

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 11:33:43

You should read the links properly

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-04 : 11:56:24

quote:
Originally posted by wackoyacky

Thanks for all the replies.

@Peso
I already came across that article, but it generates a-z & number combination but not allowing to define a predefine prefix.

@madhivanan
I'll try if I will be able to make it flexible and not to fix the number of digits after the prefix

What I'm trying to achieve is something like:
HR001
HR555
.
.
HR1234
.
.
HR12345

So basically it has a minimum of three digits but will certainly grow after sometime.

Thanks.

You should read the article more closely! It shows you exactly what you want.

- Jeff
http://weblogs.sqlteam.com/JeffS

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-06-04 : 12:06:52

I don't think the article will do what he wants. For example, I think he's saying that the first three values might be HR001, HR002, HR003 but the next one might be FIN001. Am I correct in saying that you want to provide the prefix and have the system determine the next value within that prefix? So if you passed in HR you would get HR004 and then if you passed in FIN you'd get FIN002?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-04 : 12:10:03

Perhaps. We could use a better example. My guess is that he has multiple tables, all with different prefixes and numbering, and is trying to maintain them all in 1 single master "ID" table. If so, then he can just follow the article and add identities to each table and use a UDF or computed column to append a prefix for each and get rid of that master table ... If that's not the case, then we definitely need more information, with some specifics and examples.

- Jeff
http://weblogs.sqlteam.com/JeffS

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 12:12:29

I'll go check again the articles if I miss something.

graz got my point.

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 12:30:28

Hi Jeff!

Actually I've been using your Option 3 for quite sometime already, it's just that I have this new requirement for a new table (Candidate's table) that needs the Business Unit code it belongs to be included with the number part. The number part is not continuous for all of the business units. Each business units have it's own set of numbers which starts from 1. The minimum number of digits is 3, so if there are less than 3 digits I place zeros in front.

I already have a setup for this it's just that I have concerns regarding concurrency issues. Below is the sample tables that I have:

BusinessUnit
------------
BUID
BUCode
SeedValue

Candidates
----------
CandidateID
CandidateCode
Name
Etc etc.

Sample data:

BusinessUnit
------------
1 - US - 12
2 - UK - 1
3 - SG - 23

Candidates
----------
1 - US001 - Enzo
2 - UK001 - Lorenzo
3 - US002 - Zaragoza

So I have this stored proc, that checks the BusinessUnits table for the seed value of the particular Business Unit, then from the value increment it by one, update the BusinessUnit table then generate the code from the seed value, and use the generated code in inserting a new candidate in Candidates table.

I was placing the CandidateCode generation under a transaction so that if there are concurrencies, I would be able to rollback. It just that someone told me that this could cause deadlock. But I'm not quite sure on this. I'm doing a test right now myself to see if there would be any problems with my current setup.

Thanks. I may not be able to reply again since I'm on the other side of the world and it's 12:36am already here. Thanks guys for your inputs, keep them coming.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-04 : 12:51:31

Are gaps in the numbers OK? For example, what happens if US004 gets deleted and you are up to US099? What is the next number? Is it US04 or US100? ... or does everything have to be updated to "cascade down" as soon as US004 is deleted?

If gaps are OK, then I still would use just one identity column and pass in a prefix and let the numbering go like US001, UK002, US003, SG004, etc .... As long as the unique code is there, and the prefix is correct, is that enough, or does the 4 in US004 have some meaning?

If gaps are not OK, then you have a lot of work to do to maintain your numbering ...

- Jeff
http://weblogs.sqlteam.com/JeffS

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-04 : 21:30:22

Hi Jeff!

Gaps are ok, and I don't know if there would be any because we don't do record deletions, we just mark it as deleted but never delete a row physically.

Your suggestion is great, I think it will be my option if I would not be able to do what I want.

Because what I do like with having each prefix its own set of numbers (1 to N), is that at least each business unit could visualize how many Candidates do they already have. Or in some other cases that I've seen is that they would be able to set the starting value for the IDs for each business unit, though it is not my requirement, just came across with an application which has this setup.

Thanks!

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-05 : 01:24:24

I came up with the idea of having SELECT COUNT plus 1 to get the next number for a particular Business Unit:

DECLARE @NextNo int

SELECT @NExtNo = COUNT(*) + 1 FROM Candidates WHERE BUCode='US'

-- format the generated ID
-- INSERT new Candidate using the generated ID
-- If Error 2627 (duplicate error) regenerate ID

Do you think there's a loophole in here, or performance problems when using this method?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-05 : 01:40:20

quote:
Originally posted by wackoyacky

I came up with the idea of having SELECT COUNT plus 1 to get the next number for a particular Business Unit:

the performance of that solution will be very poor as the table gets large.

EDIT: in addition, you'll get collisions if two or more readers come in at the same time to get a new id, because they'll both read the same COUNT(*).

www.elsasoft.org

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-05 : 03:11:50

Hi jezemine!

I guess the performance is inevitable in that case :( but if there is not so much user creating new candidates, I think it would not be noticeable at all.

For collisions, I'm checking for duplicate error upon inserting the candidate, if there's an error then I would try regenerate a new ID again.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources