| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
 Posted - 04/24/2007 : 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
USA
7423 Posts |
Posted - 04/24/2007 : 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
Slovenia
11741 Posts |
Posted - 04/24/2007 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/24/2007 : 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
USA
7423 Posts |
Posted - 04/24/2007 : 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
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 04/24/2007 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/24/2007 : 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 |
Edited by - jezemine on 04/24/2007 12:15:10 |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 04/24/2007 : 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 -- http://jhermiz.googlepages.com |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/01/2007 : 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
USA
7423 Posts |
Posted - 05/01/2007 : 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
United Kingdom
22191 Posts |
Posted - 05/01/2007 : 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 - 08/02/2007 : 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 - 07/02/2008 : 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
USA
7423 Posts |
Posted - 07/02/2008 : 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 Posts |
Posted - 11/27/2008 : 03:37:39
|
| Anybody know how to do the same to a PrimaryKey field? |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/28/2008 : 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 - 03/18/2010 : 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
USA
643 Posts |
Posted - 07/30/2010 : 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"
|
Edited by - Jeff Moden on 07/30/2010 11:36:20 |
|
|
|
ducatti.din
Starting Member
1 Posts |
Posted - 01/21/2011 : 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
USA
643 Posts |
Posted - 01/21/2011 : 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 |
|
|
|
Topic |
|
Article: Custom Auto-Generated Sequences with SQL Server
