Random Unique Number Always

Question

Random Unique Number Always

amy26

SSCrazy Eights

Points: 8926
More actions
September 13, 2016 at 12:38 pm

#321465

Hello, I have a stored procedure that will be executing on a recurring basis at different intervals. There is a field that I need to populate with a random unique number but once I use one, I can never use it again. As this procedure will be called all the time and not just once, how do I ensure that I never use the same random number?
I was generating it with this code: (SELECT FLOOR(RAND()*(100000-1)+1))
But, it occurred to me that there is a small possibility that it could somehow pick the same number as a previous call to the stored procedure?
Do I need to actually store the ID somewhere and tell it not to use one that already exists?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

amy26 (9/13/2016)
Hello, I have a stored procedure that will be executing on a recurring basis at different intervals. There is a field that I need to populate with a random unique number but once I use one, I can never use it again. As this procedure will be called all the time and not just once, how do I ensure that I never use the same random number?
I was generating it with this code: (SELECT FLOOR(RAND()*(100000-1)+1))
But, it occurred to me that there is a small possibility that it could somehow pick the same number as a previous call to the stored procedure?
Do I need to actually store the ID somewhere and tell it not to use one that already exists?
Thanks!

That would be the best and easiest way. You could also store a date/time to identify when the number was consumed, as well. You also wouldn't have to generate the random number. Just consume it from the table and mark the date used.

Here's the code to quickly build such a table. You'd just need to add a PK on the ID column.

WITH cteGenRandomNumber AS

(

SELECT TOP 100000

RandomNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

SELECT RandomNumberID = IDENTITY(INT,1,1)

,RandomNumber

,UsedDate = CAST(NULL AS DATETIME)

INTO dbo.SomeTable

FROM cteGenRandomNumber

ORDER BY NEWID()

;

ALTER TABLE dbo.SomeTable

ADD CONSTRAINT PK_SomeTable PRIMARY KEY CLUSTERED (RandomNumberID)

;

--===== Let's see what we have

SELECT * FROM SomeTable

;

Just consume the random numbers in the order of the PK to keep things simple.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Further on Jeff's fine answer, here is an alternative which should work if you cannot store the numbers generated, I have used this method to generate 10^10 rows without any collisions so I don't think one has to worry too much.

😎

USE TEEST;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_SIZE BIGINT = 100000

;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

NM.N

,CONVERT(DECIMAL(36,0),REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(36),NEWID(),0)

,'-','0'),'A','1'),'B','2'),'C','3'),'D','4'),'E','5'),'F','6'),0) AS UNIQUE_NUM

FROM NUMS NM;

First few rows of the output (wich obviously will not be the same again)

N UNIQUE_NUM

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

1 135175280625604130093760626242760535

2 126112660613304485024340173516872486

3 511616110275204328082710641217492608

4 214653100416404716014530825155544862

5 234911220889404355097210824236653996

6 643090860043504550026840435544739832

7 381081880930804333016160303718724276

8 624900120545204632096120595995822513

9 566613250226104696012050243933148314

10 863626410665604637087640542153222212

11 192442310305904210018950337740554465

12 837567600538204979087070496244413015

13 16147680206204593095330155291383156

14 261269930123004044081180553099435328

15 11666800438804655099720790666812479

16 53364480316004907091920270442425738

17 258143540106504560025150789225079433

18 683828320155404239082920964216552067

19 894193660453904049026540658420239837

20 466639730433104583028070722245642288

amy26 SSCrazy Eights Points: 8926 More actions · Answer 3

amy26

SSCrazy Eights

Points: 8926

September 14, 2016 at 6:31 am

#1900777

Thank you so much! Perfect!

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Eirikur Eiriksson (9/13/2016)
First few rows of the output (wich obviously will not be the same again)

Not guaranteed to be true, though. Because of the translations, as unlikely as it would be, there could be a duplicate even in such a short list. My suggestion would be that it's better to go with the guaranteed list of non-duplicate numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Jeff Moden (9/14/2016)
Eirikur Eiriksson (9/13/2016)
First few rows of the output (wich obviously will not be the same again)
Not guaranteed to be true, though. Because of the translations, as unlikely as it would be, there could be a duplicate even in such a short list. My suggestion would be that it's better to go with the guaranteed list of non-duplicate numbers.

As the generated UUID from NEWID is RFC4122 compliant (according to MS) then it is very unlikely that it would happen on a single system (built in 2 sequential bytes), the problem may more likely show up when the values are generated on multiple systems.

The caveat is that there are "only" 3.4 × 10^38 values within this format.

😎

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Eirikur Eiriksson (9/14/2016)
Jeff Moden (9/14/2016)
Eirikur Eiriksson (9/13/2016)
First few rows of the output (wich obviously will not be the same again)
Not guaranteed to be true, though. Because of the translations, as unlikely as it would be, there could be a duplicate even in such a short list. My suggestion would be that it's better to go with the guaranteed list of non-duplicate numbers.
As the generated UUID from NEWID is RFC4122 compliant (according to MS) then it is very unlikely that it would happen on a single system (built in 2 sequential bytes), the problem may more likely show up when the values are generated on multiple systems.
The caveat is that there are "only" 3.4 × 10^38 values within this format.
😎

Agreed to all of that but "unlikely" does not mean "impossible". My luck at such things has been pretty bad lately and I just don't take such chances because I don't have the time to do it more than once. Takes less time to do it with a guarantee just once. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

amy26 (9/14/2016)
Thank you so much! Perfect!

Curiosity question please. Which method did you use?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amy26 SSCrazy Eights Points: 8926 More actions · Answer 8

Jeff Moden (9/14/2016)
amy26 (9/14/2016)
Thank you so much! Perfect!
Curiosity question please. Which method did you use?

The table.

But I added more rows.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

Jeff Moden (9/14/2016)
Eirikur Eiriksson (9/14/2016)
Jeff Moden (9/14/2016)
Eirikur Eiriksson (9/13/2016)
First few rows of the output (wich obviously will not be the same again)
Not guaranteed to be true, though. Because of the translations, as unlikely as it would be, there could be a duplicate even in such a short list. My suggestion would be that it's better to go with the guaranteed list of non-duplicate numbers.
As the generated UUID from NEWID is RFC4122 compliant (according to MS) then it is very unlikely that it would happen on a single system (built in 2 sequential bytes), the problem may more likely show up when the values are generated on multiple systems.
The caveat is that there are "only" 3.4 × 10^38 values within this format.
😎
Agreed to all of that but "unlikely" does not mean "impossible". My luck at such things has been pretty bad lately and I just don't take such chances because I don't have the time to do it more than once. Takes less time to do it with a guarantee just once. 🙂

You are right Jeff, a single source table is the best option if possible and guaranteed to be unique. How many times have we seen collisions in unique identifiers like MAC addresses, IMEI, SSN etc.;-)

😎