--===== Do this in a nice safe place that everyone has
     -- (You can build a permanent one in any database)
    USE TempDB;
     IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL
        DROP TABLE Tally;
GO
--===================================================================
--      Create a Tally table from 1 to 1000 (you can increase this number as per your requirement)
--===================================================================
--===== Create and populate the Tally table on the fly.
 SELECT TOP 1000
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.sys.ALL_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO

------ Now move to your query 

SELECT 'AA' + RIGHT('00'+ CONVERT(VARCHAR,N),3) + 'A0' AS NUM 
FROM tempdb.dbo.Tally
WHERE N < 1000

--#################################################################################################
--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 

DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(20))

INSERT INTO @t
SELECT 'aa001' UNION ALL SELECT 'ab001'
UNION ALL SELECT 'aa001a0' UNION ALL SELECT 'aa001b0'

;WITH Tally (n) AS (
    SELECT TOP 999 RIGHT('00' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 3)
    FROM sys.all_columns)
SELECT STUFF(strcol, m, 3, n) 
FROM @t
CROSS APPLY (SELECT CHARINDEX('001', strcol)) a(m)
CROSS APPLY Tally
ORDER BY ID, n