Randomness in SQL (or lack thereof)

In case you didn’t know, SQL Server’s RAND() function is a bit of a joke. For a given seed value it will continually return the same number. Greeeeeat.

So, here’s my implementation.

/*
Author : Mel Padden
Purpose: Generate truly random numbers for tasks in SQL
(Because Microsoft don't really understand mathematics and think that
random numbers are only processed once per session)
Reference: http://technet.microsoft.com/en-us/library/ms177610.aspx
*/
CREATE FUNCTION [Utility].[GenerateRandomNumber](
@Max FLOAT
)
RETURNS FLOAT
AS
BEGIN

DECLARE      @BillGatesIsAnIdiot    FLOAT;
DECLARE      @Seed                  NVARCHAR(72);
DECLARE      @SeedIndex             INT;
DECLARE      @Ns                    FLOAT;

/*
I generated a pair of GUIDS, we take the current nanosecond value
and grab a series of characters, generate a hash from that,
convert that varbinary back to an int, divide the nanosecond value by that
the lot together to yield a moreorless random float.
Unfortunately, we can't then just use that as a seed to the RAND() function because Microsoft in their
wisdom have decided we can't use RAND() in a UDF. (fist)
*/
SET @Seed = '877F6E7A-23ED-419B-A51B-5E35C5D2918C15DF2CF4-B9E3-4151-A19E-D3AFEAA801BB';
SET @Ns = (CONVERT(FLOAT, DATEPART(NANOSECOND, GETUTCDATE())) / 1000000.00);
SET @SeedIndex = CONVERT(INT, @Ns) % 72;

SET @BillGatesIsAnIdiot = ABS(@Ns/CONVERT(INT, HASHBYTES('SHA1', SUBSTRING(@Seed, @seedIndex, 36)))/1.00);

/*
Right-shift the result until it's within the required range.
*/

DECLARE @Factor INT;
SET @Factor = (@Max / 10) - (@BillGatesIsAnIdiot / 10) ;
WHILE @BillGatesIsAnIdiot > @Max
BEGIN
SET @BillGatesIsAnIdiot = @BillGatesIsAnIdiot / (10 * @Factor)
END

RETURN @BillGatesIsAnIdiot

END

GO

Advertisements

About this entry