We have one table like:

Table Name:


Its columns are as below:

number      [numeric(10)]
name        [varchar(10)]
status      [tinyint(1)]
type        [tinyint(1)]
createdDate [datetime]

This table have around 250-300 million rows.

Now our requirement is that we will generate random numbers in big quantity i.e. 1 million. We want to check that this generated numbers are there in above table (with number column) or not.

How best we can do this?


-> Random numbers will be generated in application logic.

-> I want to know strategy / pseudocode how best we can design?

  • What will you do when you find some are present? Or do you need to add exactly 1M; that is, you need to "keep trying" until you find enough new numbers? Or something else? – Rick James Nov 12 '16 at 4:26

Put the million numbers in a table. Then JOIN the tables together to see which ones exist. Or LEFT JOIN ... IS NULL to see which are missing.

| improve this answer | |
  • Yes this one is in mind, will try this one as well. thanks. – murtaza.webdev Nov 12 '16 at 7:10

Is your random_number_series table fairly static? Is the number sequence fairly fragmented?

If yes to both, my first thought was that it may be more efficient to store a "Gaps" table in your sequence.

random_number_series_gaps ( gap_start_number , gap_end_number )

If exists 
select * from random_number_series_gaps
where @randomNumberFromMyApp between gap_start_number and gap_end_number
SELECT 'Random number does not exist!'
| improve this answer | |
  • maintaining gaps table is not seems feasible. – murtaza.webdev Nov 12 '16 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.