Article: Custom Auto-Generated Sequences with SQL Server

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

Site Related Forums

Article Discussion

Article: Custom Auto-Generated Sequences with SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-04-24 : 08:20:27

This article by Jeff Smith covers different ways to create sequence numbers. It starts with the basic identity and GUIDs and quickly moves to much more complicated sequence numbers including those with auto-incrementing mixed letters and numbers such as "A0001", etc. Jeff shows one of the easiest ways I've ever seen to efficiently handle very odd sequence numbers.

Article Link.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-24 : 08:59:35

great article jeff! i like the way you think.

also in sql server 2005 you can mark this calculated column as persisted and index it if you need to
which may speed some reports.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-24 : 09:10:28

Thanks, mladen ... there's a few threads here (some quite a ways back) where myself and others have recommended this technique and it works well. I saw a question on this topic a few weeks back and figured it would be helpful to put all into one article.

I think that even in SQL 2000 you can also put an index on a calculated column, if you choose to go that route.

Also -- as a quite side note, the some of the UDF's shown use the POWER() function with constants just to demonstrate the calculation; for optimal performance you would certainly just use the actual result there. (i.e., replace POWER(10,2) with 100).

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-24 : 09:34:25

in ss2k you could but not for imprecise values which the power will give.
with persisted you work around that.

anyway i'd have never thought about doing the way you did

.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-24 : 10:17:16

good article!

one thing you might want to add to the article is a reference to NEWSEQUENTIALID() for guids. it's a good alternative default value for guid columns that form a pk because they are sequential so don't cause fragmentation. it's new in 2005. http://msdn2.microsoft.com/en-us/library/ms189786.aspx

www.elsasoft.org

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-24 : 10:42:16

Huh, I did not now about newsequentialId(), very cool. I am gathering a few minor edits together to send to graz, I'll have to include that.

Thanks!!

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

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

save the "....minor edits together to send to graz, I'll have to include that." for another article...for the 2nd $50!!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-24 : 12:12:15

better give me half then! :)

btw, if you use Gert's xpguid library, you can get the functionality of newsequentialid in 2000 as well. note that there are potential security issues because you can guess the next guid if you know the current one. if knowing an id reveals information, then you probably want to avoid using newsequentialid().

xpguid.dll is here: http://www.sqldev.net/xp/xpguid.htm

www.elsasoft.org

jhermiz

3564 Posts

Posted - 2007-04-24 : 15:15:57

That reminds me of an application that I ran into where someone used an identity integer and the application was supposed to be secure. Changing the ID of the parameter in the URL changed who you were and gave you specific details about the person.
Here was the site: http://www.computertraining.com/

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

Kristen
Test

22859 Posts

Posted - 2007-05-01 : 09:03:54

We have one application which allows Alpha prefixes, and then "next number in range".

If a new record is created with just an Alpha key we look up the Max numeric for that prefix, and allocate the next sequential number.

If a key is provided that is NOT JUST Alpha we allow it through.

So I can create a new range for me by providing the key "KRISTEN100001", and thereafter I can get the next available number by just providing "KRISTEN":


IF @MyPK NOT LIKE '%[^A-Z]%'	-- Only contains Alphabetic characters? - e.g. User Initials
BEGIN
	-- Allocate next available reference number
	SELECT	@MyPK = @MyPK + CONVERT(varchar(20), MAX(CONVERT(int, SUBSTRING(MyPK, LEN(@MyPK)+1, 10)))+1)
	FROM	dbo.MyTable
	WHERE	(MyPK LIKE @MyPK + '[0-9][0-9][0-9]' 
		OR MyPK LIKE @MyPK + '[0-9][0-9][0-9][0-9]'
		OR MyPK LIKE @MyPK + '[0-9][0-9][0-9][0-9][0-9]'	-- Assume MAX numeric part is 99,999
		)
END

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-01 : 10:46:25

if that's what you've got to do, then that's how you do it. Just keep in mind all of the drawbacks of that approach, as mentioned in the article. Does it really have to number 1,2,3 for each name, or can it be any sequence number? If so, what happens when rows are deleted?

If it can be any sequential number and gaps are OK, then you can still use the identity as your basis, and just pass in the prefix plus the identity to the UDF, which returns the prefix + the formatted identity as your identifier.

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

Kristen
Test

22859 Posts

Posted - 2007-05-01 : 10:49:13

Indeed, agree with your points Jeff. I just put my code-snippet here for completeness of the thread really ... folk who read it can help themselves if they reckon it suits their situation, best they read the Doctor's Advice first though!

Kristen

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-02 : 20:44:18

Hi Jeff,

Not sure if you're still replying to threads from this article, but I'm going to take a chance and try in the hope of getting a reply.

Find my question below:
Note that I might not be able to use the system generate autonumber you recommend since I need to have this reset each time ColumnA changes. Also the value in ColumnC will infact always be unique, since it is concatenated with ColumnA [01-00010] though I have not mentioned it when I detailed it out in my post. Maybe there is a way to do it through the system autogeneration though I am relatively new to this so unaware.
Thanks for any help you can render.
------------------------------------------------------------------

Hey guys,

Not sure if this question has been answered before, but I've been looking around for a bit now and have not yet come up with an answer.

I found a few articles on autogeneration, but it did not take care of change in conditions like my case below.

If there already is a reply to this one, can you please direct me to the link? Thank you.

I have table [TableA] with the following structure / data:
ColumnA|ColumnB|ColumnC
01|AAA|NULL
01|BBB|NULL
01|CCC|NULL
02|DDD|NULL
02|EEE|NULL
02|FFF|NULL

All three columns have datatypes = varchar

ColumnC needs to store a formatted autogenerated number upto 5 characters padded with "0"(zero). This autogeneration is dependant on ColumnA and will happen in increments of 10. For every change in ColumnA, ColumnC value will be reset to 10 and start incrementing once again.

eg.
ColumnA|ColumnB|ColumnC
01|AAA|00010
01|BBB|00020
01|CCC|00030
02|DDD|00010
02|EEE|00020
02|FFF|00030

Thanks in advance for any help.

buzzi
Starting Member

48 Posts

Posted - 2008-07-02 : 15:18:06

Jeff,
Thank You, this is a great article, exactly serving my purpose, I have a question below for the function mentioned,

I was specifically looking at the below function and have a question in this

create function CustomerNumber(@i int)
returns char(5)
as
begin
return (char(@i / 26000 % 26 + 65) +
char(@i / 1000 % 26 + 65) +
char(@i / 100 % 10 + 48) +
char(@i / 10 % 10 + 48) +
char(@i % 10 + 48))
end

I was able to figure out adding "65" for the 1st 2 statements as these are base 26, the last 3 are base 10, but not able to figure out why "48" is added to the last 3?? if you are trying to return just a number for the last 3 statements is there any specific reason to use "48",

Also can i extend this to have all of them as base 26, so that i will have more posibilities, I am using the @id that is passed to this as primary key so that this will always generates a 1-to-1 map between the primary key and the customernumber

Thanks for the help

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-02 : 16:40:40

We add 65 to the letters because the letter "A" has an ascii value of 65; thus, 65+0=A, 65+1=B, etc.
We add 48 to the numbers because the number "0" has an ascii value of 48; thus, 48+0="0", 48+1="1", etc ..

see: http://www.asciitable.com/

Hope this helps.

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

ranxy
Starting Member

1 Post

Posted - 2008-11-27 : 03:37:39

Anybody know how to do the same to a PrimaryKey field?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-11-28 : 08:47:30

quote:
Originally posted by ranxy

Anybody know how to do the same to a PrimaryKey field?

That's kinda what the entire article is about ...

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

cgyq
Starting Member

7 Posts

Posted - 2010-03-18 : 11:26:41

I like the article. However I am runnin gthe following situation and not sure what to do:

I decided to use option 3 for my approach

dbID CustomerName CustomerNumber
------ --------------- --------------
60000 AAA C60001
60001 BBB C60002
.....
.....

68999 xxxx C68999
69000 qqqq C60001
69000 mmmm C60002

The Customer number range from C50000 to C69999. Currently it has been used till C6000 in the manual system. My new system will pick up the number started from C6000. The business rule says when customer number reach C69999, it start from C50000 again and reuse it. How can I create the sequence function then? Also, if reusing it, my dbID won't be able to be used in the trigger. What if dbID reach its maxium, how can I do?

Thanks for your help in advance!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2010-07-30 : 11:35:02

I've had to do quite a few of these types of things in the last year but I made the mod to remove all vowels from the mix. Do you have any idea how many 3, 4, and 5 letter swear words there actually are?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

ducatti.din
Starting Member

1 Post

Posted - 2011-01-21 : 11:07:08

Hi,
I am using the same code (i.e using identity values for generation) to generate Alpha-Numeric Sequence. The limitation is that the sequence needs to be limited to a length of 5 at any time.
So the sequence goes like this:

0 ... 2,59,999 -> A0000 ... Z9999
2,60,000 ... 6,75,999 -> AA000 ... ZZ999
6,76,000 ... 17,57,599 -> AAA00 ... ZZZ99
17,57,600 ... 45,69,759 -> AAAA0 ... ZZZZ9
45,69,760 ... 1,18,81,376 -> AAAAA ... ZZZZZ

But what happens is, the sequence is wrongly getting generated for certain value:
Ex

45,69,760 shud actually generate AAAAA since 45,69,759 generates ZZZZ9 but generates KAAAA but 1,18,81,376 generates ZZZZZ.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-01-21 : 17:30:37

Really, really, REALLY bad idea. Are you prepared to go to court if a user sees one of the many 3, 4, or 5 letter swear words that this method will occasionally spell out and decides to sue you for offending their sensibilities? Don't laugh... it's happened many times to many companies.

Alpha sequences suck. Don't use them... period.

--Jeff Moden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources