Help for Newbie: How to create an autonumber

Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.

I really need advise on how do I create a field that can generate autonumbering for eg. scenario

There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.

I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.

-PatP|||my money's on access

and what do you mean by basic skills?

can you spell dba?

Is this homework?|||Sarcasm, Brett? How unlike you!

NOT!

:)

If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.

Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.

But this is not simple SQL.

An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.

Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.