Generating random numbers in SQL Server

To generate random integer within the desired range, use this simple code (in this example generated numbers are between 20 and 50):

DECLARE @Max_Value INT = 50, @Min_Value INT = 20, @Random_Number INT;
SELECT @Random_Number = FLOOR((@Max_Value - @Min_Value + 1)
* RAND() + @Min_Value)
SELECT @Random_Number as Random_Number

or this query:

SELECT FLOOR (20 + (50-20+1)*RAND())

To generate random float number within the desired range, use the following code (in this example generated numbers are between 20 and 50):

DECLARE @Max_Value INT = 50, @Min_Value INT = 20, @Random_Number FLOAT;
SELECT @Random_Number = ((@Max_Value - @Min_Value)
* RAND() + @Min_Value)
SELECT @Random_Number as Random_Number

or this query:

SELECT 20 + (50-20)*RAND()

If you use RAND() in a single query it will produce the same value in each row:

SELECT ProductNumber, RAND() as Random FROM Production.Product

Using rand function in a query

To avoid the problem, you can include NEWID() functon into the query to get different random values for each row.
CHECKSUM(NEWID()) generates a random integer seed for the RAND function.

SELECT ProductNumber, RAND(CHECKSUM(NEWID())) as Random FROM Production.Product

Using rand and newid function in a query

To insert N (@n) random integer numbers between @Min_Value and @Max_Value into a SQL Server table, use the following code:

DECLARE @Min_Value AS int
SET @Min_Value= 5
DECLARE @Max_Value AS int
SET @Max_Value = 10
DECLARE @n AS int
SET @n = 1000
BEGIN TRANSACTION
DECLARE @uid uniqueidentifier
SET @uid = NEWID()
DECLARE @i AS int
SET @i = 1
WHILE(@i <= @n) BEGIN INSERT INTO Table_Name VALUES(FLOOR(RAND(CHECKSUM(@uid))*(@Max_Value - @Min_Value +1) + @Min_Value)) SET @i += 1 SET @uid = NEWID() END COMMIT TRANSACTION

The code inserted 1000 random integer numbers between 5 and 10 into the table:

n_random_integer_numbers_inserted

To see How to Insert N Random Dates between a given range into SQL Server Table, read related blog post:
How to Insert N Random Dates between a given range into SQL Server Table

If the post helped you, please share it:
Pin It

1 comment to Generating random numbers in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">