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
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)
