Generating membership number

Hi all,
I have a question about generating membership numbers on the fly when someone registers to my website.

Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...

SR357324J

This will then stay with them for the lifetime of their membership and be on their printed loyalty card.

My questions are...
1) Is there a 'good practice' for membership number format and generation?

2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.

I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.

Thanks for your contribution.I'm not sure about #1, but as for #2: You can still use the ID to retrieve records, even if you display the manually generated membership #.

If, however, you create your clustered index on the manually generated membership # field, you can make that field the clustered index (though this may hurt performance as relates to joins with other tables).|||I'd say let the db autogenerate an ID for you and you can append the 'SR' later..you could get into locking problems and duplicate ID's sooner or later..

hth

generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generate AutoNumber

Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ......... etc. I've the code below but it's not working in the right way, what's wrong with my code? PrivateSub BtnAdd_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAdd.Click Dim ssqlAsString Dim ItemidAsInteger Dim updcmdAs SqlClient.SqlCommand Itemid = 0 mysqladap =New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn) Itemid = (Itemid) + 1 ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')" updcmd =New SqlClient.SqlCommand(ssql, mySqlConn) updcmd.ExecuteNonQuery() lblmsg.Visible =True EndSub Anyone can help me? Thanks.

As I replied elsewhere where you asked the exact same question, use an IDENTITY column. Then you will not need to insert the value for the column, it will populate itself.|||

First of all thanks for your reply,
Can you please explain more?
Can you write the statement for me or the modification on my given code?
Thank you

|||You have to set the Identity column in the datatable table properties. You dont do that via code. In Sql Enterprise manager, when you open the table in design time, you can set the Identity property of that column to "yes" at the bottom of the screen.|||Thank you sooooooo much, and after that, delete the code I've written? and don't inset that column in the insert statement of sql?|||What's meant by identity seed? it's set to 1, shall I change it?|||

I've inserted 6 records and they are given the numbers 1,2,3,4,5, and 6, then I deleted them all and add new record, it has given the number 7, WHY?
why it isn't set to 1 again?

|||the numbers will not reset unless you recreate the table, or truncate table.