IF OBJECT_ID('dbo.randomNumbers','IF') IS NOT NULL DROP FUNCTION dbo.randomNumbers; IF OBJECT_ID('dbo.vnewid','V') IS NOT NULL DROP VIEW dbo.vnewid; GO --====== 1. generate a uniqueidentifier (a way to sneak a uniqueidentifier into an iTVF) CREATE VIEW dbo.vNewID WITH SCHEMABINDING AS SELECT ID = NEWID(); GO --====== 2. function to dbo.nview.id into a random number between CREATE FUNCTION dbo.randomNumbers ( @Rows INT, @Low INT, @High INT ) /***************************************************************************************** [Purpose]: A 100% set-based inline table valued function that generates @Rows X Rows of random numbers beteween @Low & @High. [Author]: Alan Burstein [Compatibility]: SQL Server 2008+ [Syntax]: SELECT random.RN, random.RND FROM dbo.randomNumbers(@Rows,@Low,@High) AS random; [Parameters]: @L = BIGINT; Lowest number in the set; @H = BIGINT; Highest number in the set; @N = BIGINT; The N value from your numbers/tally table or numbers function; [Returns]: Inline Table Valued Function returns: RN = BIGINT; A Forward-ordered ROW NUMBER Beginning with 1 and ending with @Rows RND = BIGINT; A random number between @L and @H [Dependencies]: 1. dbo.vNewID 2. dbo.RangeAB [Developer Notes]: 1. Logic based used to generate random numbers is based on these two excellent articles by Jeff Moden: https://www.sqlservercentral.com/articles/generating-test-data-part-1-generating-random-integers-and-floats-1 https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates 2. It does not matter which is higher: @Low or @High; the random number returned will be between @Low and @High 3. dbo.randomNumbers is deterministic. For more deterministic functions see: https://msdn.microsoft.com/en-us/library/ms178091.aspx [Examples]: --===== 1. Random Numbers -- 1.1. Ten random numbers between 1 and 5 SELECT random.RN, random.RND FROM dbo.randomNumbers(10,1,5) AS random; -- 1.2. One Hundred random numbers between -3 and 3 SELECT random.RN, random.RND FROM dbo.randomNumbers(100,-3,3) AS random; -- 1.3. Ten increasing random numbers SELECT random.RN, random.RND, RunningTotal = SUM(random.RND) OVER (ORDER BY random.RN) FROM dbo.randomNumbers(10,1,20) AS random; -- 1.4. 25 rows, 2 columns: Col1 Between 1 & 10 Million, Col2 between 500.00 & 2000.00 SELECT RN = r1.RN, Col1 = r1.RND, Col2 = CAST(CONCAT(r2.RND,'.',r3.RND) AS DECIMAL(6,2)) FROM dbo.randomNumbers(25,1000000,9999999) AS r1 CROSS JOIN dbo.randomNumbers(1,500,2000) AS r2 CROSS JOIN dbo.randomNumbers(1,0,99) AS r3; --===== 2. Random Strings -- 2.1. Random Binary Value (5 Characters long) SELECT RandomBinary = ( SELECT rc.RND+'' FROM dbo.randomNumbers(1,0,1) AS rc CROSS JOIN dbo.rangeAB(1,5,1,1) AS rowz FOR XML PATH('')); -- concatination using XML PATH -- 2.2. String of Alphabetical Characters; length defined by @Len) DECLARE @Len INT = 6; SELECT RandomChars = ( SELECT CHAR(rc.RND)+'' FROM dbo.randomNumbers(1,65,90) AS rc CROSS JOIN dbo.rangeAB(1,@Len,1,1) AS rowz FOR XML PATH('')); -- concatination using XML PATH GO -- 2.3. String of alphabetical characters; length between @Low and @High DECLARE @Low INT = 5, @High INT = 10; SELECT RandomChars = STRING_AGG(CHAR(rc.RND),'') -- concatination using STRING_AGG (SQL 2017+) FROM dbo.randomNumbers(1,65,90) AS rc CROSS APPLY dbo.randomNumbers(1,@Low,@High) AS fLen CROSS APPLY dbo.rangeAB(1,fLen.RND,1,1) AS rowz GO -- 2.4. String of Random Hexidecimal characters; Length defined by @Len DECLARE @Len INT = 6; SELECT STRING_AGG(SUBSTRING(f.Chr,rnd.RND,1),'') FROM (VALUES('0123456789ABCDEF')) AS f(Chr) CROSS APPLY dbo.randomNumbers(1,1,LEN(f.Chr)) AS rnd CROSS APPLY dbo.rangeAB(1,@Len,1,1) AS r; --===== 3. Random Name Generator DECLARE @rows INT = 1000, @mid INT = 60, -- est percentage who will have a Middle Initial @pre INT = 20, -- est percentage who will have a prefix @sfx INT = 20; -- est percentage who will have a suffix WITH -- these can all be tables: Prefix AS (SELECT p.Pfx FROM (VALUES ('Dr. '),('Sir '),('Judge '),('Prince ')) AS p(Pfx)), FName AS (SELECT f.FN FROM (VALUES ('Bill'),('Fred'),('Mary'),('Jen'),('Joe')) AS f(FN)), LName AS (SELECT l.LN FROM (VALUES ('Smith'),('Woo'),('Murphy'),('Brown')) AS l(LN)), Suffix AS (SELECT s.Sfx FROM (VALUES (' II'),(' III'),(' Jr'),(' Sr'),(' Esq')) AS s(Sfx)), MInit AS (SELECT MI = CHAR(m.N1)+'. ' FROM dbo.rangeAB(65,90,1,1) AS m), Blend AS (SELECT * FROM Prefix, FName, MInit, LName, Suffix) -- Note: 10400 Rows: 5*4*4*5*26 = 10400 SELECT TOP (@rows) --b.Pfx, b.FN, b.MI, b.LN, b.Sfx, WholeName = CONCAT( REPLICATE(b.Pfx,ABS(SIGN(rPre.RND/@pre)-1)), -- Prefix b.FN,' ', -- First Name REPLICATE(b.MI,ABS(SIGN(rMid.RND/@mid)-1)), -- Middle Initial b.LN, -- Last Name REPLICATE(b.Sfx,ABS(SIGN(rSfx.RND/@sfx)-1))) -- Suffix FROM Blend AS b CROSS JOIN dbo.randomNumbers(1,1,100) AS rMid CROSS JOIN dbo.randomNumbers(1,1,100) AS rPre CROSS JOIN dbo.randomNumbers(1,1,100) AS rSfx ORDER BY NEWID(); [Revision History]: ----------------------------------------------------------------------------------------- Rev 00 - 20180000 - Initial Development - Alan Burstein *****************************************************************************************/ RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT RN = r.RN, -- Standard Row Number RND = rnd.N -- Random Number between @Low AND @High FROM ( SELECT MIN(f.N), MAX(f.N) FROM (VALUES(@Low),(@High)) AS f(N) ) AS b([Low],[High]) -- flip if @Low>@High CROSS JOIN dbo.vNewId AS new -- sneak a newid into this ITVF CROSS JOIN dbo.rangeAB(1,@Rows,1,1) AS r -- Retrieve this many(@Rows) rows CROSS APPLY (VALUES(ABS(b.high-b.low)+1)) AS f(Rng) -- Number of possible values CROSS APPLY (VALUES(ABS(CHECKSUM(new.ID)%f.RNg)+b.Low)) AS rnd(N); -- Final Product GO