if you can have an identity column in your table, you can create a calculated persisted column that auto generates that texty-like value.

--#################################################################################################

--Pattern: AA000 to ZZ999 max value=676000

--#################################################################################################

IF OBJECT_ID('X') IS NOT NULL

DROP TABLE X

CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)), --limit=26 * 26 + 999 + 1

XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter

+CHAR((XID/1000)%26+65) --2nd Letter

+REPLACE(STR(XID%1000,3),' ','0') PERSISTED, --The 3 digit numeric part

SOMEOTHERCOL VARCHAR(30)

)

INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')

SET IDENTITY_INSERT X OFF

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF') --FAILS! too big!

SET IDENTITY_INSERT X OFF

SELECT * FROM X

--three char table: bigger range

--#################################################################################################

--Pattern: AAA000 to ZZZ999 max value=196040000

--#################################################################################################

IF OBJECT_ID('X') IS NOT NULL

DROP TABLE X

CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 196040000)),

XCALCULATED AS

CHAR((XID/260000)%26+65) --1st Letter

+ CHAR((XID/26000)%26+65) --2nd Letter

+ CHAR((XID/1000)%26+65) --3rd Letter

+ REPLACE(STR(XID%10000,4),' ','0') PERSISTED, --The 4 digit numeric part

SOMEOTHERCOL VARCHAR(30)

)

INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')

SET IDENTITY_INSERT X OFF

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')

SET IDENTITY_INSERT X OFF

SELECT * FROM X

XID XCALCULATED SOMEOTHERCOL

1 AA001 WHATEVER

675999 ZZ999 MORESTUFF