How to generate a Range of Sequence Numbers and Random Numbers in SQL Server

How to generate a Sequence of Numbers and Random Numbers in SQL Server

In some Scenarios, we need to generate a set of Sequence of Numbers or Random Numbers in SQL Server. This can be done in numerous ways as discussed below.

Random Unique ID :

The NEWID() function will generate a  unique identifier(alpha-numeric) on each execution.

This is the logical id format that uses to assign to each record across the Servers/Databases by the SQL SERVER.
Select NEWID() As Random_UID

Result :

223F240E-C068-4F6E-ACB9-76CEDD5504F6

Random Number :

The RAND() function will generate a unique random number between 0 and 1 on each execution.

Select RAND() As Random_Number

Result:

0.0458571872134441

If you want re-generate the same unique random number on each execution, then set the seed for it. The seed can be any integer.

Select RAND(10) As Random_Number
0.713759689954247

Select RAND(50) As Random_Number
0.714505008804575

Random Unique ID Int Type :

We can use the CHECKSUM () Function with NEWID() Function to generate a quite unique integer value from the unique identifier.
Select (CHECKSUM(NEWID())) As Random_UID_Int

Now with the Combination of above Functions, we can generate the Random Numbers between the specified ranges as follows...

Random Number between -100 to 100 (inclusive of -100 and 100) :
We can generate a Random Number between -100 and 100 (inclusive) on each execution of the below Query. We are doing module division with 101 to get 100 in random. 

Select (CHECKSUM(NEWID()))%101 As [Random_Number_btw_-100_100]

Random Number between 0 to 100 (inclusive of 0 and 100) :

We can generate a Random Number between 0 and 100 (inclusive) on each execution of the below Query. We are doing module division with 101 to get 100 in random and taking only the Absolute Value(+ve). We can use 201 to get a random number between 0 to 200.  

Select ABS(CHECKSUM(NEWID()))%101 As [Random_Number_btw_0_100]

Random Number between 0 to 100 (inclusive of 0 and 100) :

Select CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1  AS [Random_Number_btw_1_100]

OR

Select FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1) AS [Random_Number_btw_1_100]

Result:

Till now we discussed about only generating one random number. Now will see how we can generate the Sequence of Numbers and Random Numbers Sequence using the Common

Table expressions as follows.

Sequence of Numbers from 1 to 100 with use of CTE :

We can generate the Sequence of Numbers from 1 to 100 using the below CTE Query. The no.of recursions used here is 100. In SQL Server, the Max no.of allowed recursions are 32767, so that we need to make sure that the recursions specified should be less than the Max allowed.

WITH SeqOfNumbers (Num)
AS (
-- Anchor member definition
SELECT 1
UNION ALL
-- Recursive member definition
SELECT sn.Num + 1
FROM SeqOfNumbers sn
WHERE sn.Num < 100
)
-- Statement that executes the CTE
SELECT Num As NumberSeq
FROM SeqOfNumbers
OPTION (MAXRECURSION 32767);

GO

Result:

In the following result we are showing only the first 15 rows out of 100.

Notes :

The no.of recursions used here is 100. In SQL Server, the Max no.of allowed recursions are 32767, so that we need to make sure that the recursions specified should be less than the Max allowed.

If we use more than 32767 recursions(eg: if we use 33000 instead of 100 in above), we will get the following error.

Msg 530, Level 16, State 1, Line 2

The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

Caution Note:

To avoid the limitations on the Max no.of recursions, we can set the option as OPTION (MAXRECURSION 0), but it leads to infinite no.of loops if you don't specify your recursions correctly.

Sequence of Numbers from 1 to 100 with use of CTE :
We can generate the Random Numbers Sequence from 1 to 100 using the below CTE Query.
WITH RandomNumbers (SeqNum, RandomNum) AS (
-- Anchor member definition
SELECT 1 AS SeqNum,
(CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1) AS RandomNum
-- FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1) AS RandomNumber
UNION ALL
-- Recursive member definition
SELECT rn.SeqNum + 1 ,
(CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1)
--FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1)
FROM RandomNumbers rn
WHERE rn.SeqNum < 100
)
-- Statement that executes the CTE
SELECT rn.SeqNum, rn.RandomNum
FROM RandomNumbers rn
OPTION (MAXRECURSION 500);
GO

Result:

In the following result we are showing only the first 15 rows out of 100.

Note:

Please note that only the Random Numbers Sequence will change on each execution but not the Sequence Numbers.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------