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

 

Written by

41   Posts

View All Posts

Leave a Reply