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
 Article: Custom Auto-Generated Sequences with SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/24/2007 :  08:20:27  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 04/24/2007 :  08:59:35  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/24/2007 :  09:10:28  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/24/2007 :  09:34:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/24/2007 :  10:17:16  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/24/2007 :  10:42:16  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/24/2007 :  11:32:56  Show Profile  Reply with Quote
save the "....minor edits together to send to graz, I'll have to include that." for another article...for the 2nd $50!!

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/24/2007 :  12:12:15  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 04/24/2007 12:15:10
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 04/24/2007 :  15:15:57  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://jhermiz.googlepages.com
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/01/2007 :  09:03:54  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/01/2007 :  10:46:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/01/2007 :  10:49:13  Show Profile  Reply with Quote
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
Go to Top of Page

Esc2Xtc
Starting Member

8 Posts

Posted - 08/02/2007 :  20:44:18  Show Profile  Reply with Quote
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.
Go to Top of Page

buzzi
Starting Member

48 Posts

Posted - 07/02/2008 :  15:18:06  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/02/2008 :  16:40:40  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

ranxy
Starting Member

1 Posts

Posted - 11/27/2008 :  03:37:39  Show Profile  Reply with Quote
Anybody know how to do the same to a PrimaryKey field?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/28/2008 :  08:47:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

cgyq
Starting Member

7 Posts

Posted - 03/18/2010 :  11:26:41  Show Profile  Reply with Quote
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!
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 07/30/2010 :  11:35:02  Show Profile  Reply with Quote
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"


Edited by - Jeff Moden on 07/30/2010 11:36:20
Go to Top of Page

ducatti.din
Starting Member

1 Posts

Posted - 01/21/2011 :  11:07:08  Show Profile  Reply with Quote
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.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/21/2011 :  17:30:37  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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