well you are assuming based on your formatting that you'd never have more than 10 people with the same name(xxx09);
i'm providing an example below, but i don't think like the idea;
id rather let people use their emails as a username, which at least ends up unique to the end user; i'd hate to comeback to some web site and not be able to remember my "username" was "Lowell312" or something(I'm talking to you, Progressive Insurance!), which was something i didn't pick or even want.
don't get caught up on the CTE's, they are just generating random data;
the trick is to use row number and a case statement to generate the string
--this is just generating fake sample data.
With MYFirstNames(FName) AS
(
Select 'Leonardo' UNION ALL
Select 'Brad' UNION ALL
Select 'Arnold' UNION ALL
Select 'Mark' UNION ALL
Select 'Matt' UNION ALL
Select 'Bruce'
),
MyLastNames(LName) AS
(
Select 'DeCaprio' UNION ALL
Select 'Pitt' UNION ALL
Select 'Schwarzenegger' UNION ALL
Select 'Wahlberg' UNION ALL
Select 'Damon' UNION ALL
Select 'Willis'
), NamesWithRowNumber
AS
(
SELECT TOP 10000
row_number() over (partition by LEFT(A.FName,1),B.LName ORDER BY A.FName,B.LName) - 1 AS RW, --zero indexed
A.FName,B.LName
FROM MYFirstNames A
CROSS JOIN MyLastNames B
CROSS JOIN sys.columns
ORDER BY NEWID()
)
SELECT
CASE
WHEN RW = 0
THEN LEFT(FName,1)+ LName
ELSE LEFT(FName,1) + LName + RIGHT('0000' + CONVERT(varchar,rw),4)
END AS UserName,
*
FROM NamesWithRowNumber
ORDER BY RW,UserName
Lowell