# 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