Generate Unique Random Number

Question

I know similar questions have been asked, but I have a rather different scenario here.

I have a SQL Server database which will store TicketNumber and other details. This TicketNumber is generated randomly from a C# program, which is passed to the database and stored there. The TicketNumber must be unique, and can be from 000000000-999999999.

Currently, what I do is: I will do a select statement to query all existing TicketNumber from the database:

Select TicketNumber from SomeTable

After that, I will load all the TicketNumber into a List:

List<int> temp = new List<int>();
//foreach loop to add all numbers to the List
Random random = new Random();
int randomNumber = random.Next(0, 1000000000);
if !(temp.Contain(randomNumber))
//Add this new number to the database

There is no problem with the code above, however, when the dataset get larger, the performance is deteriorating. (I have close to hundred thousand of records now). I'm wondering if there is any more effective way of handling this?

I can do this from either the C# application or the SQL Server side.

How big is the database likely to get, and why does the ticket number need to be random rather than just unique? — Jon Skeet
– Jon Skeet, Commented Oct 2, 2014 at 21:02
How about keeping a separate table of unassigned ticket numbers, and deleting them as they are selected? You could even presort the table in a random sequence. — Dave Mackersie
– Dave Mackersie, Commented Oct 2, 2014 at 21:08
@C.J. Ask the client exactly why they want it this way when it's clearly problematic and will cause issues later. — xxbbcc
– xxbbcc, Commented Oct 2, 2014 at 21:08
@C.J. Btw, this is a terrible design and is a recipe for trouble; especially over multiple threads. — xxbbcc
– xxbbcc, Commented Oct 2, 2014 at 21:09

Jon Skeet · Accepted Answer · 2014-10-02 21:15:49Z

5

This answer assumes you can't change the requirements. If you can use a hi/lo scheme to generate unique IDs which aren't random, that would be better.

I assume you've already set this as a primary key in the database. Given that you've already got the information in the database, there's little sense (IMO) in fetching it to the client as well. That goes double if you've got multiple clients (which seems likely - if not now then in the future).

Instead, just try to insert a record with a random ID. If it works, great! If not, generate a new random number and try again.

After 1000 days, you'll have a million records, so roughly one in a thousand inserts will fail. That's only one a day - unless you've got some hard limit on the insertion time, that seems pretty reasonable to me.

EDIT: I've just thought of another solution, which would take a bunch of storage, but might be quite reasonable otherwise... create a table with two columns:

NaturalID ObfuscatedID

Prepopulate that with a billion rows, which you generate by basically shuffling all the possible ticket IDs. It may take quite a while, but it's a one-off cost.

Now, you can use an auto-incrementing ID for your ticket table, and then either copy the corresponding obfuscated ID into the table as you populate it, or join into it when you need the ticket ID.

edited Oct 2, 2014 at 21:15

answered Oct 2, 2014 at 21:09

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C.J. Over a year ago

I actually think this would be the best solution among the impossible.

Jon Skeet Over a year ago

@C.J.: See my alternative at the end of the answer.

Steve · Accepted Answer · 2014-10-02 21:10:47Z

0

You can create a separate table with only one column . Lets just name it UniqueID for now. Populate that column with UniqueID = 000000000-999999999. Everytime you want to generate a random number, do something like

SELECT TOP 1 UniqueID From (Table) WHERE UniqueID NOT IN (SELECT ID FROM (YOUR TABLE))

Code has not been tested but just to show the idea

answered Oct 2, 2014 at 21:10

Steve

12k7 gold badges48 silver badges85 bronze badges

3 Comments

Steve Over a year ago

@xxbbcc Lock the table then

C.J. Over a year ago

So you are saying only one ticket can be generated at a time?

Steve Over a year ago

@C.J. One thread at a time. if one thread tries to generate 10 tickets, feel free to do so

Collectives™ on Stack Overflow

Generate Unique Random Number

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related