Alright... here you go. First, we'll demo a way to accomplish the task and then I'll show in the post following this one why this type of numbering should NEVER be done.

The way the following code works is that it actually uses an IDENTITY column to provide a true auto-incrementing capability instead of having to do any manual sequencing. That's important because instead of using a bunch of self joins to an "external number generator" or sequencer of any kind, we can use a Computed Column, instead.

[font="Arial Black"]I still think this type of "numbering" is a huge mistake (see the next post down for why).[/font]

--===== Create a table with a real auto-incrementing column and

-- a persisted formula to convert it to Base 26 automatically.

-- The auto-incrementing column is "Zero Based" to keep things simple.

-- Max value = 26^7-1 = 8,031,810,175 > Largest INT

CREATE TABLE #MyHead

(

MyHeadID INT IDENTITY(0,1) NOT NULL,

Base26ID AS CHAR(MyHeadID/308915776%26+65) --26^6

+ CHAR(MyHeadID/11881376%26+65) --26^5

+ CHAR(MyHeadID/456976%26+65) --26^4

+ CHAR(MyHeadID/17576%26+65) --26^3

+ CHAR(MyHeadID/676%26+65) --26^2

+ CHAR(MyHeadID/26%26+65) --26^1

+ CHAR(MyHeadID%26+65) --26^0

PERSISTED NOT NULL,

SomeString VARCHAR(36) NOT NULL

)

--===== Add a million+1 rows of "something" to the table

-- just to prove it works.

INSERT INTO #MyHead

(SomeString)

SELECT TOP 1000001

NEWID() AS SomeString

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

--===== Show what's in the table

SELECT *

FROM #MyHead