Generate unique random integer in database column

Question

My application creates coupons that each need a unique barcode number. This number needs to be a positive integer and must be between 6 - 12 digits. This number represents a unique coupon, so this number must be unique. I can't simply increment the barcode numbers by 1, because this will make it easy for hackers to guess other coupon barcodes.

If I have a coupon db table, how can I generate this random barcode number and guarantee uniqueness?

Answer 1

This will give you a random number of up to 12 digits, with very few collisions.

select -convert(bigint, convert(varbinary(max), newid())) % 1000000000000

You need to test and ignore collisions, as well as discard numbers that end up with less than 6 digits.

---

EDIT

To use the lowest lengths first, you won't be able to use a truly random number generator. This is because once you get to 95% of the 6-digit range, the collisions would be so high that the program spends all its time trying and retrying to get a unique number that hasn't been used yet. Once you get to only one number remaining, the program can wait forever and never "generate" that number. So, to fulfil "lowest lengths first" you would actually have to generate ALL numbers into a table and then row number (order by len(num), newid()) them randomly, then sequentially draw them out.

To 0-pad to 12 digits, use

select right('000000000000'
      +right(-convert(bigint, convert(varbinary(max), newid())),12),12)