The first function that springs to mind is the RAND() function, but this suffers from a few drawbacks. Firstly it is only a pseudo random number and it generates values between 0 and 1. This is easily rectified by multiplying the result by the range you want, so for values between 0 and 255 the expression would be
select cast(rand() * 255 as tinyint)
A second drawback is if you try to use it in a query to assign every record a unique number it doesn’t, it simply generates a single number for all rows.
select top 20 *,cast(rand() * 250 as tinyint) from mytable
This generates the same number for every row.
A more flexible function in SQL server is the CRYPT_GEN_RANDOM() and this will generate a variety of random values and is designed to be cryptographically secure which means it will potentially be more random than the existing RAND() function.
This can easily replace the RAND() function and is called like this depending on the size of the value you wish to return.
Select CAST(CRYPT_GEN_RANDOM(1) AS TINYINT)
Select CAST(CRYPT_GEN_RANDOM(2) AS SMALLINT)
Select CAST(CRYPT_GEN_RANDOM(4) AS INT)
Select CAST(CRYPT_GEN_RANDOM(8) AS BIGINT)
Numbers returned by this function will be between the MIN and MAX values for the datatype you are returning and as such may be both positive and negative.
This time if you run the query
select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) from mytable
You will notice a different random number returned against each row.
If you only wish to have a value between 0 and 250 it would initially be quite easy to simply use the modulus function to return it, so a query like
select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) % 250 from mytable
will provide results in the 0-250 range. A potential issue here is that the values may not be completely random as some of the values between 1 and 5 will have a higher probability of being returned as for these numbers as there are two possible output values that are in the modulus operator that will give the same final result. To output the number 1 the outputs from the random number could be 1 or 251, and for 2 it could be 2 or 252 and so on.
To prevent this being an issue you could simply throw away all the numbers higher than 250 so each number only has a 1 chance of being included in the output.
There are two possible solutions here, firstly we could make the range of the CRPT_GEN_RANDOM() function so large that most of the statistical deviations could be removed and the simplest solution could be
Select CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) % 250
As the output could swing from -9223372036854775808 through 9223372036854775807 the possible values for each of the desired 250 output values are many so that it should produce a nice smooth spread of random numbers. It wont be perfect but for most applications it will be fine.
The second and slightly more convoluted way to remove this deviation would simply be to throw away any numbers higher than 250 and rerun the CRYPT_GEN_RANDOM() function again.
One issue that will be encountered is that the CRYPT_GEN_RANDOM() function cannot be run from within a UDF which would be the simplest way to handle this.
First we have to embed the function in a view as unfortunately embedding it in a stored procedure will not work from within the function.
CREATE VIEW vWGenerateRandomNumber
AS
SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT) AS RandomNumber
This view is really more of a workaround as we aren’t actually returning any actual database rows just the value from the CRYPT_GEN_RANDOM() function.
Next we can create the function that calls the view
CREATE FUNCTION Get_RandomNumber()
RETURNS TINYINT
AS
BEGIN
DECLARE @Value TINYINT
/* rerun the loop while the value is out of range */
WHILE (@Value IS NULL OR @Value >= 250)
select @Value= RandomNumber from dbo.vWGenerateRandomNumber
RETURN @Value
END
In the function we have added the login in the WHILE loop so that it calls the CRYPT_GEN_RANDOM() function again if the value is > =250 or NULL.
Now whenever we need a random number we can simply call the function.
select top 20 *,dbo.Get_RandomNumber() from mytable