A table with auto increment charter ID field

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

SQL Server 2000 Forums

SQL Server Development (2000)

A table with auto increment charter ID field

Author

Topic

sarai
Starting Member

6 Posts

Posted - 2007-03-12 : 14:37:39

Hello,
I am trying to create a table with ID field which increment automatically. ID must be 6 charters contain both letters and numbers (A00A00)

i.e. A00A00, A00A001 ….. A00A99, A00B01 and so on

I try to generate and update table with unique ID s by writing a trigger and updating ID field by calling a function. This work fine when I try to insert a single row, but I am having problem updating ID when trying to insert multiple rows at same time i.e. inserts into statement.

The function (which calculate next id), check same table, find last ID value and increment the id according to formula. The main problem with multiple rows is that there is no way to know which last ID was used. Is there any other way to create a column with increment charters ID, which following ( i.e. A00A00, A00A001 ….. A00A99, A00B01 and so on) format? Note: ID must be 6 charters contain both letters and numbers (A00A00)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 15:04:30

Why must the id specifically contain letters and digits? This would be very easy if you can use a number - then it's a simple identity column.

sarai
Starting Member

6 Posts

Posted - 2007-03-12 : 15:46:18

Here little more background, we have table which keep record for customers, (i.e. name, and some other information). In the past we collected SSN number to uniquely identify customers. Now we are making changes to stop collecting SSN. Instead create a unique ID. Currently, we have integer identity column in this table but integer will be hard to remember by customers and we decided to create a 6 charters ID with number and letters.
First letter, then 2 numbers, then letter and last 2 more numbers
A00A00

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 16:10:08

I'm not sure that A28G46 is going to be any easier to remember than 273645 (digits seem to work well enough for phone numbers right?)

Anyhow, if you just want some letters in there, then why not have two columns in the table, one with a couple of letters, like the person's initials and the other with the identity value - because you're saying that the letters in the account number mean nothing anyway. Then when you give someone their account number you just format the two values together in an expression, padding the identity column with zeroes. So in the table you have

Col1   Col2
AB     1
GE     2
MM     3

And you give the customers their account numbers as

AB0001
GE0002
MM0003

and so on. That's a 6 character code and will allow for 9999 customers, if you increased to an 8 character code, like AB000001, you can have up to a million.

An expression for this would be
Col1 + right('0000' + cast(Col2 as varchar(4)), 4) for 6 total characters
or
Col1 + right('000000' + cast(Col2 as varchar(6)), 6) for 8 total characters

sarai
Starting Member

6 Posts

Posted - 2007-03-12 : 16:51:22

If we allow A00A00 format, will allow entering more records,

99(last 2 digits) x 26 (letter A..Z) X 99(2nd and 3rd digits) x 26 (1st letter) = 6, 625, 476

A00A00
A00A01
..
..
..
A00A99
A00B01
..
…
A00Z99
A01A99
..
…
X99Z99
Z99Z99

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 16:57:42

I realize that, and if you want to save two characters per account, for a total of about 10c worth of disk space at the expense of greatly complicating your database code, then that's fine. I'm sure there are a few $300/hr consultants watching these forums that would love to help you with that

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-12 : 18:00:33

It wouldn't actually save any disk space. The 6 character customer code would use 6 bytes per customer, while an integer column would use only only 4 bytes.

If you really must do this, it would be much easier to implement by using an IDENTITY column to generate an ID, and then creating a computed column, view, or trigger to generate the alpha customer code based on the IDENTITY. Then you could use the IDENTITY for the primary key, foreign keys, etc. internally.

The amount of overhead and contention from trying to generate the alpha code directly is a major problem, and directly impacts the scalability of the system.

The following code generates all possible customer codes according to your algorithm, so it shows how easy it is to generate your customer code from an integer IDENTITY. The code can easily be incorporated in a view, function, or computed column.


select
	ID,
	CUSTOMER_CODE =
		-- Generate Customer Code from Identity
		char((((N2%2600)-((N2%2600)%100))/100)+65)+
		right(10000+((N2%2600)%100),2)+
		char((((ID%2600)-((ID%2600)%100))/100)+65)+
		right(10000+((ID%2600)%100),2)
from
	(
	select
		ID = number,
		N2 =number/2600
	from
		-- Function F_TABLE_NUMBER_RANGE available on this link:
		-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
		F_TABLE_NUMBER_RANGE(0,6759999)
	) a

Results:


ID          CUSTOMER_CODE 
----------- ------------- 
0           A00A00
1           A00A01
2           A00A02
3           A00A03
4           A00A04
5           A00A05
6           A00A06
7           A00A07
8           A00A08
9           A00A09
10          A00A10
11          A00A11
12          A00A12
13          A00A13
14          A00A14
15          A00A15
16          A00A16
17          A00A17
...
6759989     Z99Z89
6759990     Z99Z90
6759991     Z99Z91
6759992     Z99Z92
6759993     Z99Z93
6759994     Z99Z94
6759995     Z99Z95
6759996     Z99Z96
6759997     Z99Z97
6759998     Z99Z98
6759999     Z99Z99

CODO ERGO SUM

sarai
Starting Member

6 Posts

Posted - 2007-03-12 : 18:48:19

Thanks, Michael

This is Exactly what I am looking for..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources