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