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.

The post T-SQL Random Numbers using RAND() for Test Data appeared first on BI and Predictive Analytics.