A Truly Random Post

Want to generate a truly random number? It's a little more tricky than it first seems.

First off, TSQL has a RAND function. Simple huh? Not so fast. We'll start with some definitions...

RAND Returns a pseudo-random value of the float datatype from 0 through 1, exclusive.
The value returned is based on the seed, hence providing the same seed always produces the same values.
It is difficult to use RAND in set based results as it is only evaluated once per batch.
It will return the same value for each of the row in a query if the same seed is used.
Therefore to use RANDeffectively you need to use different seed values.

CHECKSUM Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes.
It returns an integer and is good (meant) for generating a one way hash.

NEWID Returns a unique value of type uniqueidentifier i.e. a 16-byte GUID.
for example ; 0EFD9C6D-7FF5-4CB1-A71B-69CF8FF02B4B

ABS Returns the absolute value of the input, i.e. a positive number.


Generating Random Numbers


Method 1 :

This involves using both NEWID and RAND for 2 levels of randomness.
Using CHECKSUM to change a string into a given integer is repeatable (not random), although the values are far from being visually predictable.

Because fractional values are generated in a float i.e. less than 1, the result is multiplied by the maximum value and cast as an integer to generate whole numbers.

Firstly, the workings ...

DECLARE @data_newid  uniqueidentifier
DECLARE @data_int  int
DECLARE @data_float  float
DECLARE @maxvalue  int

SET @maxvalue = 100

-- Generate Unique 16 bit GUID
SET @data_newid = NEWID() 
-- Generate hash value integer of the GUID
SET @data_int = CHECKSUM(@data_newid)
-- Use the hash value to seed the RAND function
SET @data_float = RAND(@data_int)

-- Show Results
SELECT @data_newid as d_uniqueidentifier
SELECT @data_int as d_int
SELECT @data_float as d_float
SELECT CAST(@maxvalue * @data_float AS int) as randomnumber
GO

The abridged version...

DECLARE @maxvalue int
SET @maxvalue = 100
SELECT CAST(@maxvalue * RAND(CHECKSUM(NEWID())) AS int) RandomNumber
GO

Method 2 :

This involves recasting NEWID() first to varbinary, then to int. ABS is used to convert any negative results to positive. Because high values are generated by this method, we use %, the modulo operator to retrieve a result. If we want numbers up to and including 15, then we divide by 16 as 0 to 15 would be the remainder generated by modulo.

-- The Workings

DECLARE @data_newid  uniqueidentifier
DECLARE @data_varbinary varbinary
DECLARE @data_int int
DECLARE @maxvalue int

SET @maxvalue = 100

SET @data_newid = NEWID()
SET @data_varbinary = CAST(@data_newid AS varbinary)
SET @data_int = CAST(@data_varbinary as int)

SELECT @data_newid as d_uniqueidentifier
SELECT @data_varbinary as d_varbinary
SELECT @data_int as d_int
SELECT @data_int % (@maxvalue +1) as randomnumber

The abridged version...

DECLARE @maxvalue int
SET @maxvalue = 100
SELECT (ABS(CAST(CAST(NEWID() AS varbinary) AS int)) % (@maxvalue + 1)) RandomNumber
GO

Method 3: 

Similar to Method 1, but only using NewID for the random element. Because a checksum can be negative, we use ABS to ensure the result is positive. Then we use the modulo operator like method 2, to get the remainder as an integer.

DECLARE @maxvalue int
SET @maxvalue = 100
SELECT ABS(CHECKSUM(NewId())) % (@maxvalue + 1) RandomNumber
GO

Generating a Random number within a range

The following example adapts version 1 so that you can provide a lower and upper value for the number you want generated.

DECLARE @MaxValue int
DECLARE @MinValue int
SET @MaxValue = 5000
SET @MinValue = 0

SELECT CAST((((@MaxValue) + 1) - (@MinValue))* RAND(CHECKSUM(NEWID())) + (@MinValue) AS int) RandomNumber

Links 

The HOBT : Check your lucky numbers
Less than dot : SET Based random numbers
Blackwasp : Random numbers in TSQL Queries
Technet : Checksum
Technet : Floor
Technet : Round
Technet : Abs
Technet : % (Modulo)