5

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?

Improve this question
2
  • To answer the second part of the question, it seems the uniqueness can only be guaranteed by testing the number against the coupon table. Commented Mar 16, 2011 at 23:13
  • 2
    The uniqueness shall be guaranteed with a unique index, and insert routine have to take in account the possible unique violation to re-generate the coupon number and retry. With the @Richard proposed solution, collision probability will be very low, because UIDs are very well designed to that. Commented Mar 16, 2011 at 23:36

2 Answers 2

Reset to default
5

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 for the nice solution, having some idea of how newid is generated, I think OP is unlikely to have a number with less than 6 digits. You can prevent this by adding 100000 to any value, which in turn will rarely generate a 13 digit number, which is unlikely to happen frequently. Applying a module operation over a 1e11 and not 1e12 will prevent this also, reducing the possible range but increasing the collision chance.
what if I wanted a code that would allow 0s to pad the front of a number, as in 000243530981?
also, lets say that lengths of 6-12 digits are acceptable, but I'd prefer to use up the lowest possible lengths first. How would you approach that, would you use logic in the insert code, or alter the SQL statement above?
another approach to the lowest first would be to say lowest preferred and use a variant of the above where you first try a random with 6 digits, and if you hit an existing one you increase the length and try again. (you could also do several retries on the same length before increasing length.
2

Might sound lame, but depending on the # of values you're going to need, you could just put a unique constraint on the column, and update each row with a random number (with the 6-12 digits) and loop until it doesn't fail. 12 digits is a lot of values, so you're probably not going to get many collisions.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.