Problem
Generating random numbers from a normal distribution is essential for accuracy and realistic modeling, simulation, inference, and algorithm design for scientific, engineering, statistical, and AI domains. How can we build a random number generator using Marsaglia Polar method in SQL Server without the use of external tools?
Solution
The Marsaglia polar method generates pairs of independent standard normal random variables from uniformly distributed random numbers. It does not use trigonometric functions to calculate angles instead uses a polar transformation.
Calculation Overview
The first step is to generate the number of rows desired with random numbers W1 and W2 varying between minus one and one.
Next calculate the value of s.
If the s value is greater than zero and less than one, we calculate the t value:
From the random number one we calculate the first normally distributed variable, using the formula:
And now the second normally distribute variable using the formula:
Let’s look at how we can build this with T-SQL.
Table to hold generated numbers
-- MSSQLTips (TSQL)
CREATE TABLE [dbo].[RndNormalDist](
[ClusterId] [int] NOT NULL,
[PointId] [int] NOT NULL,
[X] [float] NOT NULL,
[Y] [float] NOT NULL,
[SpatialPoint] AS ([geometry]::Point([x],[y],(0))),
CONSTRAINT [PK_RndNormalDist] PRIMARY KEY CLUSTERED
(
[ClusterId] ASC,
[PointId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOMarsaglia Polar Stored Procedure
Here is the SQL code to build the functionality.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20250716
-- Description: Normal Marsaglia Polar
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspRandGenMarsaglia]
(@Points int
,@X_Offset decimal(18,6)
,@Y_Offset decimal(18,6)
,@Scale decimal(18,6)
,@ClusterId int)
WITH EXECUTE AS CALLER
AS
BEGIN;
BEGIN TRY
DECLARE @X float
,@Y float
,@S float
,@T float
,@Count int = 0;
DECLARE @GenPoints
TABLE ([PointId] int
,[X] decimal(18, 6)
,[Y] decimal(18, 6)
,[S] decimal(18, 6)
,[T] decimal(18, 6));
WHILE @Count < @Points BEGIN
SET @X = (RAND(CHECKSUM(NEWID())) * 2) - 1;
SET @Y = (RAND(CHECKSUM(NEWID())) * 2) - 1;
SET @S = SQRT(SQUARE(@X) + SQUARE(@Y));
IF @S > 0 AND @S < 1 BEGIN
SET @Count += 1;
INSERT INTO @GenPoints
([PointId]
,[X]
,[Y]
,[S]
,[T])
VALUES (@Count
,@X
,@Y
,@S
,SQRT(-2 * LOG(@S) / @S))
END
END
SELECT [PointId]
,@X_Offset + @Scale * ([X] * [T]) AS [X]
,@Y_Offset + @Scale * ([Y] * [T]) AS [Y]
,@ClusterId
FROM @GenPoints;
RETURN;
END TRY
BEGIN CATCH
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH;
END
GOIf you need more variables just add more lines for the calculation and also the return point in the SELECT clause for the temporary table.
Generating Random Numbers
Let’s generate a group of three random clusters giving the number of points, the x offset value, the y offset value, the scale, and the cluster number for each one.
-- MSSQLTips (TSQL)
TRUNCATE TABLE [dbo].[RndNormalDist];
-- Cluster 1
INSERT INTO [dbo].[RndNormalDist]
([PointId]
,[X]
,[Y]
,[ClusterId])
EXEC [dbo].[uspRandGenMarsaglia] 250,-3,-3,2,1;
-- Cluster 2
INSERT INTO [dbo].[RndNormalDist]
([PointId]
,[X]
,[Y]
,[ClusterId])
EXEC [dbo].[uspRandGenMarsaglia] 250,3,3,2.5,2;
-- Cluster 3
INSERT INTO [dbo].[RndNormalDist]
([PointId]
,[X]
,[Y]
,[ClusterId])
EXEC [dbo].[uspRandGenMarsaglia] 250,1,-1,2,3;
-- Returning the generated values
SELECT [ClusterId]
,[PointId]
,[X]
,[Y]
FROM [dbo].[RndNormalDist];
GOResulting in:
If you want to have a better visualization using the Spatial Results tab, do the following:
-- MSSQLTips (TSQL)
DECLARE @OverallExtent geometry
,@ProportionalBuffer float;
SELECT @OverallExtent = geometry::EnvelopeAggregate([SpatialPoint])
FROM [dbo].[RndNormalDist];
DECLARE @MinX float = @OverallExtent.STPointN(1).STX;
DECLARE @MinY float = @OverallExtent.STPointN(1).STY;
DECLARE @MaxX float = @OverallExtent.STPointN(3).STX;
DECLARE @MaxY float = @OverallExtent.STPointN(3).STY;
DECLARE @ExtentWidth float = @MaxX - @MinX;
DECLARE @ExtentHeight float = @MaxY - @MinY;
DECLARE @MinDimension float = IIF(@ExtentWidth < @ExtentHeight, @ExtentWidth, @ExtentHeight);
DECLARE @Ratio float = @MinDimension * 0.015;
-- Returning the clusters
SELECT [ClusterId]
,COUNT(*) AS N
,geometry::CollectionAggregate(SpatialPoint) AS geom
FROM (SELECT [ClusterId]
,SpatialPoint.STBuffer(@Ratio) AS SpatialPoint
FROM [dbo].[RndNormalDist]) AS X
GROUP BY [ClusterId];
GOResulting in:
Next Steps
