Creating a Normal Distribution in SQL Server

I needed to create a normally-distributed sample data set in SQL Server recently. I suspect that there are a few of you out there who need to be able to generate this sort of sample data from time to time.

Using the so-called “Polar Method”, the following code will generate 200,000 values with a sigma of one and a mean of zero. On my laptop running SQL Server 2005 Developer Edition, this only takes a few seconds to run.

set nocount on
drop table #temp
create table #temp (x float)

declare @p float, @2p float, @u1 float, @u2 float
declare @x float, @y float, @index int

select @index = 0
select @p = pi()
select @2p = 2.0 * @p

while (@index < 100000)
    select @u1 = rand(), @u2 = rand()
    select @x = sqrt(-2.0 * log(@u1)) * cos(@2p * @u2)
    select @y = sqrt(-2.0 * log(@u1)) * sin(@2p * @u2)
    -- @x and @y are now normally distributed around zero (0),
    -- with a standard deviation of one (1)

    insert into #temp (x)
    values (@x)
    insert into #temp (x)
    values (@y)

    select @index = @index + 1
select 'Average'=avg(x) from #temp
select 'Standard Deviation'=stdev(x) from #temp

There are a couple variations that I might post about. One removes the trig functions for better performance, and another wraps this code in a function so you can do different sigma and mean values.

Hope this helps someone!


0 Responses to “Creating a Normal Distribution in SQL Server”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: