CODEWITHSUNDEEP
c#sql-serverado.net
C.J.
C.J.

Reputation: 3527

Generate Unique Random Number

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.

Upvotes: 0

Views: 1015

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500835

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.

Upvotes: 5

Steve
Steve

Reputation: 11963

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

Upvotes: 0

Related Questions