T-SQL Random Numbers using RAND()
SQL Server includes the T-SQL RAND() function to create a random value between 0 and 1 of float datatype. To create a random number execute Select Rand(), in my example it returned .0131039082850364. If I wanted to always return the same number I can include a seed value, such as Select RAND(21). Using 21 as the seed value, the query returned .713964652638088.
One way to leverage the RAND() function is to create sample data. For example, I often use RAND() to create new columns and populate with data for testing such as gender (0, 1), churn rate (1,0), product numbers.
To limit the results between 0 and 1 you need to generate the random number and convert it to an integer. In the following steps you will see how to convert the float to a integer and generate the desired results.
Step 1 –create a random value
declare @randomfloat float
set @randomfloat = (select rand())
select @randomfloat as random_float
–Multiplying the float value returned by 2
select @randomfloat * 2 as Random_2
–Convert the float value to an integer that returns 0 or 1
SELECT convert (int, (2)*@randomfloat ) as Random_0_1
If you need higher values than 0 or 1 you can increase the value used to multiple times the @randomfloat. For example to generate a key between 0-9 multiply the @randomfloat * 10.
For complete details on RAND() see MSDN.