Even though I still don't like this kind of "numbering", let's try things in a slightly different manner. If we remove all the vowels from the alphabet, it's a wee bit more difficult to spell swear words.

First, build the following scalar function. It's got to be scalar because we're still going to use it in a computed column. It can't be persisted, either, because it ends up being non-deterministic in this case and I haven't tried to figure out how to make it so because it actually runs faster than the previous example...

CREATE FUNCTION dbo.Base21

--===== This function accepts an integer and returns mostly harmless

-- "numbering" using letters with all vowels removed.

-- Jeff Moden

(@Integer INT)

RETURNS VARCHAR(8) AS

BEGIN

DECLARE @Return VARCHAR(8)

;

WITH

cteAllowed AS

(

SELECT 'BCDFGHJKLMNPQRSTVWXYZ' AS Letters

)

SELECT @Return =

SUBSTRING(Letters, @integer/1801088541%21+1, 1) --21^7

+ SUBSTRING(Letters, @integer/85766121%21+1, 1) --21^6

+ SUBSTRING(Letters, @integer/4084101%21+1, 1) --21^5

+ SUBSTRING(Letters, @integer/194481%21+1, 1) --21^4

+ SUBSTRING(Letters, @integer/9261%21+1, 1) --21^3

+ SUBSTRING(Letters, @integer/441%21+1, 1) --21^2

+ SUBSTRING(Letters, @integer/21%21+1, 1) --21^1

+ SUBSTRING(Letters, @integer%21+1, 1) --21^0

FROM cteAllowed

;

RETURN @Return

END

GO

Now we can do this without all the swear words...

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

-- a persisted formula to convert it to Base 21 (no vowels) automatically.

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

CREATE TABLE #MyHead

(

MyHeadID INT IDENTITY(0,1) NOT NULL,

Base21ID AS dbo.Base21(MyHeadID)

,

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 1000000

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