You've double posted... but I'll post the answer here, as well...
...And I assume that when you get to A999 and insert one more record, it should roll over to B000, huh?
Ok, in order to keep this from becoming "Death by SQL", here's how to do it... your table should have two column in it that are very similar to the ID and AlphaID columns... like this...
CREATE TABLE AlphaID
ID INT IDENTITY(0,1) PRIMARY KEY CLUSTERED ,
AlphaID AS CHAR(ID/1000+65)+REPLACE(STR(RIGHT(ID,3),3),' ','0'),
ALTER TABLE dbo.AlphaID
ADD CONSTRAINT CK_AlphaID_MaxID
CHECK (ID < 26999)
The key here is two things... the ID column and the calculated column called AlphaID. Neat formula, huh?
And, to demo the table in the code above, let's do 10,000 quick little inserts and then select from the table... don't blink or you'll miss it...
INSERT INTO AlphaID (SomeOtherCol)
SELECT TOP 10000 'TestOnly'
FROM Master.dbo.SysColumns sc1,
SELECT * FROM AlphaID
p.s. The first number is A000 and the rollover numbers are B000, C000, D000, etc. It would be a fair bit more difficult to generate rollover numbers that were B001, C001, D001, etc.