SQL Server RANDNORM Function

Updated 2023-10-18 16:07:18.233000

Description

Use the scalar function RANDNORM to calculate a pseudo-random number based on the normal distribution.

Syntax

SELECT [westclintech].[wct].[RANDNORM](
  <@mu, float,>
 ,<@sigma, float,>)

Arguments

@mu

is the mean of a normal distribution. @mu is an expression of type float or of a type that can be implicitly converted to float.

@sigma

is the standard deviation a normal distribution. @sigma is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

RANDNORM is non-deterministic, so results will vary.

Unlike the built-in SQL Server RAND function, RANDNORM is evaluated for every row in the resultant table.

To generate a series of pseudo-random normal numbers, consider using the SeriesFloat function.

To generate a pseudo-random number between zero and one, consider using the XLeratorDB RAND function.

To generate a pseudo-random integer between two integers, consider using the RANDBETWEEN function.

If @mu is NULL it will be set to zero.

If @sigma is NULL it will be set to one.

Examples

This example demonstrates the difference between using the built-in SQL Server function RAND and the XLeratorDB RANDNORM function.

with mycte
as (select 1 as seq
    union all
    select seq + 1
    from mycte
    where seq < 15)
select seq,
       rand() as [SQL Server RAND],
       wct.randnorm(NULL, NULL) as [XLDB RANDNORM]
from mycte;

This produces the following result.

Your results will be different.

In this example we will generate 4 random numbers for each row.

with mycte
as (select 1 as seq
    union all
    select seq + 1
    from mycte
    where seq < 15)
select seq,
       wct.RANDNORM(NULL, NULL) as [A],
       wct.RANDNORM(100, 15) as [B],
       wct.RANDNORM(72, 2) as [C],
       wct.RANDNORM(50000, 15000) as [D]
from mycte;

This produces the following result.

Your results will be different.