RMJCS Logo
 

Introduction

 
 

The T/SQL RAND() (see the BOL entry) function produces random numbers with a uniform distribution - there is an equal probability of getting any one random number as any other. Sometimes this is not ideal, for example, when generating test data.

All the 'functions' here are stored procedures because T/SQL user defined functions can not use the RAND() function.

 
     
 

dbo.get_uniform_random_integer

 
 
create procedure dbo.get_uniform_random_integer (@min int, @max int) as  
/*
** INFO   : Return a random number between @min and @max inclusive. A significant
**          series of these numbers will produce a uniform (i.e. flat) distribution.
**            
** INPUTS : @min = lower end of required range.
**          @max = upper end of required range.
**
** NOTES  : 1) The result of the expression is natively a float because the
**             addition and multiplcation operators return the data type of the
**             argument with the higher precedence.
**             See the topic 'Data Type Precedence' in BOL.
**          2) The truncate (floor) is achieved via the implicit cast from float to int.
**             From BOL topic : CAST and CONVERT;
**               When data types are converted with a different number of decimal places,
**               the value is truncated to the most precise digit. For example, the
**               result of SELECT CAST(10.6496 AS int) is 10.
*/
begin
  declare @rand float
  declare @result int

  set @rand = rand()

  --set @result = floor((@max - @min + 1) * @rand) + @min
  set @result = (@max - @min + 1) * @rand + @min

  return @result
end
go
 
     
 

dbo.get_linear_random_integer

 
 
create procedure dbo.get_linear_random_integer (@min int, @max int) as  
/*
** INFO   : Return a random number between @min and @max inclusive. A significant
**          series of these numbers will produce a linear (i.e. sloping up) distribution.
**            
** INPUTS : @min = lower end of required range.
**          @max = upper end of required range.
**
** NOTES  :
*/
begin
  declare @rand1 float
  declare @rand2 float
  declare @result int

  while (1=1)
    begin
      set @rand1 = rand()
      set @rand2 = rand()

      -- produce @f, mean 0, stdev 1
      if (@rand1 > @rand2) break
    end

  --set @result = floor((@max - @min + 1) * @rand) + @min
  set @result = (@max - @min + 1) * @rand1 + @min

  return @result
end
go
 
     
 

dbo.get_normal_random_integer

 
 
create procedure dbo.get_normal_random_integer (@min int, @max int, @normality float = 1.0) as  
/*
** INFO   : Return a random number between @min and @max inclusive. A significant
**          series of these numbers will produce a roughly normal (i.e. bell shaped)
**          distribution centered between @min and @max.
**
** INFO   : This procedure is not GUARANTEED to produce a statistically correct normal
**          distribution, just a roughly normal distribution.
**            
** INPUTS : @min = lower end of required range.
**          @max = upper end of required range.
**          @normality = probability curve shaping parameter, 1 means a normal normal,
**                                                           <1 means flatter,
**                                                           >1 means a tighter peak.
**
** NOTES  :
*/
begin
  declare @f float
  declare @r int
  declare @rand1 float
  declare @rand2 float

  -- scale normality.
  set @normality = 3.5 * @normality

  while (1=1)
    begin
      set @rand1 = rand()
      set @rand2 = rand()

      -- produce @f, mean 0, stdev 1
      set @f = cos(2.0 * PI() * @rand1) * sqrt(-2.0 * log(1.0 - @rand2))

      -- If the result is within the required range then exit this loop, otherwise try again.
      if (@f >= -@normality and @f <= @normality) break
    end

  -- set range
  set @f = (@max - @min + 1) / (@normality * 2) * @f

  --offset to @min
  set @f =  ((@min + @max) / 2.0) + @f

  -- round and return
  set @r = round(@f, 0)

  return @r
end
go
 
     
Copyright 2007-2015 RMJCS Ltd