This is one of the more "fun" discussion threads here for a while -- along with the companion thread about autoincrement on a varchar.

The short answers are: (1) you can't autoincrement anything but a numeric (but, why not? letters are sequential as well, aren't they? " 1" might be math, but "get next value" applies to anything that has sequence) (2) don't overload semantics in a column value (the relational god had a stroke when you did that) (3) if only we could start over, it would be soooo much cleaner and simpler!

--bryan :)

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

You can do that.
You have to split the string to emp and 001 and you have to convert number
part in to int and use the sequence which will increment the number and
concatinate with the number and emp.
Thanks,
Lakshman.

I'd create an auto number column in the table then set an update insert trigger (since this is EMP number I suspect a low volume of changes. I would then have the trigger concat the new auto increment number to Emp and update the column in my table based on the auto increment value. A bit of excess space and slightly violates normal form but allows a more natural query to retrieve the data. At least that is one approach. If there are a lot of transactions in this table then another approach should be examined as this IMO won't scale well to a high volume of update/inserts.

I'm not disagreeing with your idea, just tossing out an alternative. Your computed column is 'unnatural' to select, IMO. Hardly a big deal except to those who are weak on basic SELECT statements (adhoc queries by untrained users). My way sort of does away with the computed column in the SELECT. Your way is far more efficient and much closer to normal form and would scale far far better than my approach. I'm sort of assuming HR people might be involved and my experience is to expect minimal computer skills from them.

Here is a routine I wrote about 10 years ago that does all of the things
that you're looking for. It uses a partial name rather than a "EMPL"
string and then it uses searches for the next available value for the
numeric part. You would probably want to use a search for "MAX" as you
are only incrementing (and not using a partial name) .

Pay particular attention to how the string is built - of the string part,
the concatenation of the zeros with the numeric part.

This SP was designed to also use an alpha-numeric sequence, so it uses the
ASCII value of the numbers and then goes in to the letters. You may want
to use only numbers.

This SP was called off of the record create trigger to gen up the ID that
was inserted when the record was created.

The error processing has changed a bit since this was written, but I'll
leave that up to you to figure out.
Enjoy.
Alan

/*********/
/* INSERT TRIGGER for Person Table */
/* Creates a PersonID based on the user state, Last Name and a sequence
nbr */
/* can't do the State - the address record is produced after the Person
record */
/* NOTE - THIS DOES NOT HANDLE BULK INSERTS! */
/* 6/19/02 ALW create */
/*******/
drop trigger dbo.Person_Insert
GO
CREATE TRIGGER dbo.Person_Insert
ON Person
FOR INSERT
AS
DECLARE
@NewID char(12), -- Holds the builtup ID string
@FindID varchar(12), -- temporary ID used vaious places
@loopcounter tinyint, -- used to keep track of which digit is being edited
@IncrVar tinyint, -- used to keep track of the val we are incrementing
@RecID Int, -- record ID from the record to calc
@Updater char(12), -- updater used to set LastUpdateBy and Enteredby
@ErrMsg varchar(50) -- holds any failure messages
--- Set the Updater value -----ALW add 2/5/04 ----------------------
SET @Updater = (SELECT PersonID
FROM Staff
WHERE LoginName = (select system_user))
IF @@RowCount = 0
BEGIN
SET @ErrMsg = 'User name not found in staff table - insert canceled'
GOTO CancelTran
END
---- Build a 12 char string with the lesser of 6 chars of last name or
whole name and zeros
SET @NewID = (SELECT LEFT(LEFT(UPPER(RTRIM(LastName)), 3)+
LEFT(UPPER(RTRIM(FirstName)),3)+
'000000000000',12) FROM inserted)
IF @@ERROR <> 0 -- Multiple records will blow this up and cancel the
trans
BEGIN
SET @ErrMsg = 'Bulk insert not supported - insert canceled'
GOTO CancelTran
END
------- check the first query - 00 and if fail, increment until succeed
SET @FindID = (SELECT PersonID FROM Person WHERE PersonID = @NewID)
WHILE @FindID IS NOT NULL ---- loop until no match found
BEGIN
SELECT @loopcounter = 12 -- start at rightmost slot
WHILE @loopcounter > 6 -- leftmost 6 slots are name - don't touch
BEGIN
SELECT @IncrVar = ASCII(SUBSTRING(@NewID, @loopcounter, 1))
SELECT @IncrVar =
CASE
WHEN @IncrVar = 57 THEN 65 -- if 9 then A
WHEN @IncrVar = 90 THEN 48 -- if Z then 0
ELSE @IncrVar + 1 -- increment by 1
END

SELECT @NewID = STUFF(@NewID, @loopcounter, 1, CHAR(@IncrVar))

IF @IncrVar <> 48 -- if we have not looped back to zero,
BREAK -- drop out - we dont need to increment any more

SELECT @loopcounter = @loopcounter - 1
END ----------------- end loop for incrementing values -----------
SET @FindID = (SELECT PersonID FROM Person WHERE PersonID = @NewID)
-- check to see if ID in use
IF @FindID IS NULL -- No match found for the new ID
BREAK -- Hop out - we're done
----------------------- error trap if counter down to 2 --------------
IF @loopcounter = 6 --- if we exited because we increment too far
GOTO CancelTran --- nothing further we can do here
END
------ Do the UPDATE -----
UPDATE Person SET PersonID = @NewID,
LastUpdate = CURRENT_TIMESTAMP,
LastUpdateBy = @Updater,
EnteredByID = @Updater
WHERE PersonID = 'SET' --the default value for the ID field = SET
RETURN
/*== Error Trap ==*/
CancelTran:
ROLLBACK TRAN
RAISERROR(@ErrMsg, 16, -1)
GO

you raise an interesting problem... the precision (size) of the numeric part of this string in probably limited to 3 positions, since it is a formatted string of the form (3-char string)+(3-digit number).

Rather than allow the autoinc to continue to its system maximum, I would expect to limit the number part to the range 000...999, and throw an error when it overflows.

In your solution, then, the xid column would be better defined as numeric(3,0) then as int.

Is it possible to use autoincrement on a numeric( ) rather than an int?

If not, then a trigger is needed to test for a logical value over 999 and block the insert/update.

I'm just covering a worst case scenario. If it were my deployment I'd limit to 4 or 5 digits (to string) as well and place checks (actually in the front end program). Your concern over the size is well taken. I just don't have the full scope of the OP's problem. Sort of sounds like a school assignment to me, as I would never do anything like this in production. When dealing with my last customer they created Employee numbers based on several criteria, a department prefix (yes if you moved you got a new number, old one marked inactive) some value I never understood and then a final 4 digit number. No auto number involved. To ensure there were no repeats a manual check is made off the sorted list. This company has a low turnover rate so this whole thing might take 10 minutes of someone's time a month ??
An issue might come up if you are a Wal-Mart with their high turnover rate but in most situations the employee table doesn't see a lot of action.

Oh, I'm not recommending the department thing, that's just the way they have done it. It doesn't harm anything the way they do it, when you move your old number isn't deleted, it is marked inactive and a new record for you is created. They only have at most 500 employees (might be less) and maybe 600 records since the 1980's.

All of these discussions about encoding the department in the employee ID, and about handling the problems when auto increment generates a number that exceeds the available digits, illustrate the value of one of my rules:

DON'T ALLOW SEMANTICS IN AN IDENTIFIER

An identifier, such as an employee number, should be just that - a number that is associated with a specific employee. It shouldn't include any other information. It most certainly shouldn't include a department number (Isn't that why we have relational databases? The department information should be in a separate table, and the employee's department should be a foreign key in the employee table). It shouldn't have a "prefix" to indicate that it's an employee number. It shouldn't be limited to a specific number of digits -- unless the number of digits allows for more numbers than the world population (and even then, I'd be nervous).

This is one of the more "fun" discussion threads here for a while -- along with the companion thread about autoincrement on a varchar.

The short answers are:
(1) you can't autoincrement anything but a numeric (but, why not? letters are sequential as well, aren't they? "+1" might be math, but "get next value" applies to anything that has sequence)
(2) don't overload semantics in a column value (the relational god had a stroke when you did that)
(3) if only we could start over, it would be soooo much cleaner and simpler!

Although the example deletes all rows, I suspect the actual requirement is to reuse retired empid numbers sequentially as new rows are added. An example would be the deletion of emp002 only. This is generally as bad an idea as including non-numeric data in the identifier because it complicates queries on historical data. When forced to do something like this, I archive the deleted data with an identifying date or dates so that it is possible to determine who emp001 was at the time relevant to a particular historical query.

There are cases when even strictly numeric identifiers must be reused such as team jersey numbers, so this is not purely an academic exercise. One way to determine the lowest sequential number available is to compare each empid sequentially to its rank. I think it would look somthing like the following, but do not have a way to test and am translating from Oracle SQL with which I am more familiar. I suspect there are cleaner and more efficient ways to write this.

with empid_cte as
(
select empid,
rank() OVER (ORDER BY empid)) as emprank,
from emp_table
)
select 'emp' + min(emprank) as empid
from empid_cte
where 'emp' + emprank < empid
order by emprank;