How do I generate a Unique ID?

Hi,

I have 5 tables that need ID columns. These ID columns are the primary keys for these tables. They appear in the tables like so:

Clients table

CLT00001
CLT00002
CLT00003

Volunteers table

VOL00001
VOL00002
etc...

These ID's are given to the Clients/Volunteers so they need to be more than just a number (I want to use the 3 char prefix), but I want them to auto-increment. I am not concerned with gaps if a row is deleted.

At the moment I have a user defined datatype in MSSQL, and I am incrementing and adding the prefix in VB before inserting into the database.

This works fine, except I dont know how to retrieve the last ID number from the DB so I can increment it when I add a new record.

I was using a VB function that did this: SELECT MAX(client_id) FROM clients, then strip everything of the front but the number, increment + 1, add the prefix and leading zeros back on again, and return the new ID. This worked until CLT00011, then it returned CLT00002 again.

Is there a better way to do what I am trying to do? Please help!One solution:

Separate your ID into two fields; the prefix and the numeric portions. Autoincrement the numeric portion, but pad it with zeros and concatenate it with the prefix for display. You could also create a calculated field in your table that combined the two components.

Either way, you can set the combination of the two columns as the primary key, or just as a unique index.

blindman|||Does that mean I would have a Prefix field with 'VOL' in every row, and my IDENTITY field in another. Sounds good, but does it kmatter that I have redundant info. i.e. the prefix field?

How do I join the two with a calculated field, because I still need to have a 8 char long ID, 3 chars for the prefix, then the ID num padded with zeros between the two, e.g.

VOL00001
VOL00002|||Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).|||If every record has the same prefix then is it not necessary to dedicate a column to it.

Set up your autoincrement row, and then create your calculated field with this formula:

'VOL' + right('00000' + cast([AutoIncColumn] as varchar(5)))

blindman