# SQL Server RANDNORM function

RANDNORM

Updated: 31 January 2012

Use RANDNORM to calculate a pseudo-random number based on the normal distribution.
Syntax
SELECT [wctMath].[wct].[RANDNORM](
<@mu, float,>
,<@sigma, float,>)
Arguments
@mu
is 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 Types
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.
Example
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.
seq        SQL Server RAND          XLDB RANDNORM
----------- ---------------------- ----------------------
1      0.786185655285411      0.132515769270168
2      0.786185655285411       0.55422759842865
3      0.786185655285411    0.00279640603651261
4      0.786185655285411       1.20615188572666
5      0.786185655285411      0.258279276822493
6      0.786185655285411      0.328259483474435
7      0.786185655285411     0.0704535933678625
8      0.786185655285411     -0.325716938056977
9      0.786185655285411     -0.276812639860543
10      0.786185655285411      0.487789164610815
11      0.786185655285411      0.303768736287741
12      0.786185655285411       0.79775224936946
13      0.786185655285411      0.478204215978753
14      0.786185655285411      0.745115412390468
15      0.786185655285411     -0.561231528988071
Your results will be different.
In this exampe 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
seq A                 B                 C                 D
--- ----------------- ----------------- ----------------- -------------------
1  -0.37438455959187  112.197012137904  70.0460586334768    49452.9037458025
2   0.35242562693047  96.2771935371399   72.017519378787    52025.4333293082
3   0.58187159757270  93.8010204479432  73.6484751036427    66384.5057201813
4  -0.24507083494989  100.552442469468  72.3968994750811    49969.1110176925
5   0.67990016539522  103.492544687236  69.7743425613628    49654.2304364637
6  -0.94473559022718  101.760233294853  71.1030513177572    42593.7391076126
7  -0.57363162397797    100.0563965629  66.4380195801886    49856.9245265609
8  -0.08180582927527  87.3615395039761  72.6962990907136    58191.1279599142
9    1.0092680429425  80.8457950480969  73.8710030361076    46528.7518703674
10  2.66764331693742  118.639172077512  72.5444920000896    80447.1037280331
11  0.12854384378103  104.837932040327  74.0798610244449    109923.572201717
12 -0.77086257411754  96.3579139091106  71.8940299672826    56044.7478108594
13  0.37903147013382  134.378620126305  71.5551686460212    39432.7239847204
14 -0.07790490300863  87.7613538547814   70.994167886532     47844.105807051
15 -0.59210431507451  103.671932190145   73.852650712671    44921.2034130292
Your results will be different.