Random Number in SQL

In programming random number is very popular and it is frequently used for different purpose. We can creates random number in different ways: we can use programming language, SQL queries, etc. Most of the famous programming language has some built-in mechanism to handle random number.  This article explains how to creates random number in SQL? Summary of the article:

• Generate Random Number in SQL Query
• Generate Random Number using NEWID()
• Generate Random Number between Range

Generate Random Number in SQL Query
SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND()AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1.  If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places.  By casting to integer we can get rid of the decimal places.

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

If we pass seed parameter value then the RAND() function will always return same value.

SELECT RAND(1) AS [RandomNumber]

Generate Random Number using NEWID()
Here’s a different way to generate a random number without using the RAND() function. We can use NEWID system function to generate a random numeric value.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

The NEWID function returns a unique value of unique identifier data type. To convert it into integer first we have to convert it into VARBINARY then integer. The resulting integer value can be positive and negative. It we want only a positive value then we need to use the absolute value mathematical function ABS.

Generate Random Number between Range
We can generate random integer number between a range. Sample SQL statement is given bellow:

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

-- Random number between 1 and 999

SET @Lower = 1   -- The lowest random number
SET @Upper = 999 -- The highest random number

SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

That’s all about SQL random number.