A more controllable random string generator function for SQL Server, Sql Server

Language: Layout:

How to generate random strings on SQL Server, 
While controling their length and the type of chars they can contain.

Published on What the # do I know.

https://zoharpeled.wordpress.com/2019/12/10/a-more-controllable-random-string-generator-function-for-sql-server/

CREATE VIEW dbo.GuidGenerator
AS
    SELECT Newid() As NewGuid;
GO

CREATE FUNCTION dbo.RandomStringGenerator
(
    -- the minimum length 
    @MinLength int, 
    -- the maximum length
    @MaxLength int, 
    -- the maximum number of rows to return. Note: up to 1,000,000 rows
    @Count int, 
    -- 1, 2 and 4 stands for lower-case, upper-case and digits. 
    -- a bitwise combination of these values can be used to generate all possible combinations:
    -- 3: lower and upper, 5: lower and digis, 6: upper and digits, 7: lower, upper nad digits
    @CharType tinyint 
)
RETURNS TABLE
AS 
RETURN 
-- An inline tally table with 1,000,000 rows
WITH E1(N) AS (SELECT N FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) V(N)),   -- 10
     E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
     E3(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
     Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) FROM E3 a, E2 b) --1,000,000

SELECT TOP(@Count) 
        n As Number, 
        (
        SELECT  TOP (Length) 
                -- choose what char combination to use for the random part
                CASE @CharType 
                    WHEN 1 THEN Lower
                    WHEN 2 THEN Upper
                    WHEN 3 THEN IIF(Rnd % 2 = 0, Lower, Upper)
                    WHEN 4 THEN Digit
                    WHEN 5 THEN IIF(Rnd % 2 = 0, Lower, Digit)
                    WHEN 6 THEN IIF(Rnd % 2 = 0, Upper, Digit)
                    WHEN 7 THEN 
                        CASE Rnd % 3
                            WHEN 0 THEN Lower
                            WHEN 1 THEN Upper
                            ELSE Digit
                        END
                END
        FROM Tally As t0  
        -- create a random number from a guid using the GuidGenerator view
        CROSS APPLY (SELECT Abs(Checksum(NewGuid)) As Rnd FROM GuidGenerator) As rand
        CROSS APPLY
        (
            -- generate a random lower-case char, upper-case char and digit
            SELECT  CHAR(97 + Rnd % 26) As Lower, -- Random lower case letter
                    CHAR(65 + Rnd % 26) As Upper,-- Random upper case letter
                    CHAR(48 + Rnd % 10) As Digit -- Random digit
        ) As Chars
        WHERE  t0.n <> -t1.n -- Needed for the subquery to get re-evaluated for each row
        FOR XML PATH('') 
        ) As RandomString
FROM Tally As t1
CROSS APPLY
(
    -- Select a random length between @MinLength and @MaxLength (inclusive)
    SELECT TOP 1 n As Length
    FROM Tally As t2
    CROSS JOIN GuidGenerator 
    WHERE t2.n >= @MinLength
    AND t2.n <= @MaxLength
    AND t2.n <> t1.n
    ORDER BY NewGuid
) As Lengths;

DECLARE @Count int = 10;

SELECT  CAST(IntVal.RandomString As Int) As IntColumn, 
        UpVal.RandomString as UpperCaseValue, 
        LowVal.RandomString as LowerCaseValue, 
        MixVal.RandomString as MixedValue
FROM dbo.RandomStringGenerator(3, 7, @Count, 4) As IntVal
JOIN dbo.RandomStringGenerator(10, 10, @Count, 1) As LowVal
    ON IntVal.Number = LowVal.Number
JOIN dbo.RandomStringGenerator(5, 10, @Count, 2) As UpVal
    ON IntVal.Number = UpVal.Number
JOIN dbo.RandomStringGenerator(10, 20, @Count, 7) As MixVal
    ON IntVal.Number = MixVal.Number

View schema
Execution time: 0,61 sec, rows selected: 10, rows affected: 0, absolute service time: 1,74 sec, absolute service time: 1,79 sec