0

I am trying to generate unique "referral codes" for unique entries into a "customer" table upon INSERT. I want to do this with a default value constraint.

My question is, how do I guarantee that randomly generated values will be unique? I know I can do something like:

 SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [ReferralCode]

Will give me a random integer, but I suspect that it's non-unique.

Help?

Thanks!

Ray
  • 3,137
  • 8
  • 32
  • 59

3 Answers3

3

That is what uniqueidentifier is used for. But that wont work if int is a requirement. Int32 is to small to hold a fairly global unique value.

UPDATE:
If the value only needs to be unique in that table, just create another column, enable identity and set the seed to some value.

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • I'm thinking what I could do is make the ReferralCode column a uniqueidentifier data-type. Then, write a really simple function outside of it to CAST it to an integer. Will that always yield unique integers or am I mistaken? – Ray Nov 21 '11 at 22:42
  • @RayBao another option would be to save the Random referral nr. in a column and when a new is generated, check to make sure that it has not already been generated, in that case generate a new. `SELECT CAST(RAND() * 1000000 AS INT)` – Magnus Nov 21 '11 at 22:56
  • 1
    After speaking with a couple other folks, it doesn't seem like we're set on Integer's-only. So that being said, can I get away with a "uniqueidentifier" column and setting the default value to newid() ? – Ray Nov 21 '11 at 22:59
0

Could you base it off the primary key (assuming int) of the record, plus some big number to make it look random? E.g., (CustomerID+31281923928). This is if you don't mind the values being sequential.

Paul Stovell
  • 32,377
  • 16
  • 80
  • 108
  • I guess it depends on what the op means by "unique". If it only needs to be a unique value in that very table, one can just use the pk column. But that wont of course be a globally unique value. – Magnus Nov 21 '11 at 22:40
  • Yes, it only needs to be unique in this table. I want to avoid using the primary key column. – Ray Nov 21 '11 at 22:41
0

Maybe take a look at some of the strategies that are available in something like nhibernate

What's the Hi/Lo algorithm?

Community
  • 1
  • 1
Daniel Powell
  • 8,143
  • 11
  • 61
  • 108