How to generate unique random number in SQL?

**Random Number in SQL**

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]

**The NEWID() Way**

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.

**Method 1 : Generate Random Numbers (Int) between Range**

-- Variables for the random number

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

Awesome! Exactly what I need. Thank you for the tutorial :)

ReplyDelete