June 9, 2016
How to generate random data
I’
ve been doing some performance testing of sql recently and wanted lots of data to work with. I didn’t want real data, with names and other confidential information. I’ve been using a function I created a few years ago and which is presented below. The function can be used to generate either numerical data, alpha data, or a combination, within a range.
Here’s an example of using the function to generate alphanumeric data between 5 and 10 characters in length:
select dbo.fn_RandomStringValue(5,10,1,1)
Execute the function and you’ll get a random value something like this:
6S24XM275
Here’s the same function now generating only numeric data:
select dbo.fn_RandomStringValue(5,10,0,1)
Now the random data contains only numbers:
106638016
The function depends on a view, vRandom, that uses the NEWID function as a seed to generate random numbers. The NEWID function can’t be used directly in a function. Try to do so and you’ll get an “Invalid use of a side-effecting operator” error.
create view dbo.vRandom as
select RAND(
CAST( NEWID() AS varbinary )
) 'random'
Here is the function.
-- 2011-05-10 JCL Generates a random string consisting of numbers, letters or some random combination
-- of the two.
drop function dbo.fn_RandomStringValue
go
create function dbo.fn_RandomStringValue(
@iMinLength int,
@iMaxLength int,
@IncludeRandomLetters bit,
@IncludeRandomNumbers bit)
returns varchar(8000)
as
begin
---declare @value varchar(100) select @value=dbo.fn_RandomStringValue(5,25,1,0) select @value,datalength(@value)
declare @iLengthRange as int,
@iLength int
select
@iLength=@iMinLength+((@iMaxLength-@iMinLength) * [random]) from vRandom
declare @value varchar(8000)
set @value=''
declare
@rnd_alphanum decimal(18,6),
@rnd_letter decimal(18,6),
@rnd_number decimal(18,6),
@ichar int
while DATALENGTH(@value)<@iLength
begin
set @ichar=0
select @rnd_alphanum=[random] from dbo.vRandom
if (@IncludeRandomLetters=1 and @IncludeRandomNumbers=0)
or (@IncludeRandomLetters=1 and @IncludeRandomNumbers=1 and @rnd_alphanum<0.51)
begin
select @rnd_letter=[random] from dbo.vRandom
set @ichar=(26*@rnd_letter)+65
end
if @ichar=0
begin
select @rnd_number=[random] from dbo.vRandom
set @ichar=(9*@rnd_number)+48
end
set @value=@value + CHAR(@ichar)
end
return @value
end