-1

I need to generate a 9 digit order id without any leading zeroes. What is the best way to do this ?

What I think of is: Create a Table with a unique ID Column and then generate a random number between 100000000 and 999999999 in C# Code and try to insert it into the table until I am successful. What are the chances of getting Unique Constraint Exception in this and re-generating the number ?

If I am thinking in the right direction, is there any way to do this in the sql server itself rather than handling exception in the C# code and then re-generating ?

Or is there any other best method to do this ?

Thanks

teenup
  • 7,459
  • 13
  • 63
  • 122
  • Why would you want a non sequential Order Identifier, seems like a waste of time for no benefit! – Lloyd Feb 12 '12 at 16:08
  • While doing online transactions on all the banks, I have never seen an OrderId like this: 100000000 – teenup Feb 12 '12 at 16:11
  • Never seen more than 2 or 3 zeroes, if I apply autoincrement, for first 9999 transactions, I will get an OrderId containing four continuous zeroes, thats why I wanted random OrderIds. – teenup Feb 12 '12 at 16:13
  • Well ... what you're gonna do about it when your random function spits 500000000? – doblak Feb 12 '12 at 16:34
  • That's why I asked a question here to get the opinions. I don't know, but have you ever seen such an order id ? like 500000000 – teenup Feb 12 '12 at 16:50
  • The best ideaiis not to do random numbers to start with for accounting items without checking first. You likely just write crap software because it violates legal requirements. Eve4ry accounting juristiction i know of requires items to be numberes in such a way that one can identify items where not removed or added at a later stage. – TomTom Feb 12 '12 at 17:09
  • Who cares what the order id is? Who cares if they're in sequence? What are you trying to prevent? Some hacker guessing what the next order id will be? Why? What are they going to do with that information? Hopefully your data model relies on more than just an order id to determine validity of accessing or manipulating data. – Aaron Bertrand Feb 12 '12 at 18:41
  • _I don't know, but have you ever seen such an order id ? like 500000000_ ... No, but OMG, think of 666666666! ;) – doblak Feb 12 '12 at 21:57
  • Well, if they really need to be random you'll probably want a source like [HotBits](http://www.fourmilab.ch/hotbits/). – HABO Feb 13 '12 at 01:44

4 Answers4

1

generate ... until I am successful.

Well. It would probably even work if number of your transactions would remain relatively low compared to all the possibilities, but that seems really wrong. Catching exceptions and retrying?

Do you have any reasons not to use autoincrement key, starting with 100000000 and counting?

doblak
  • 3,036
  • 1
  • 27
  • 22
  • If I want the OrderIDs not to be in sequence, then ? – teenup Feb 12 '12 at 15:59
  • Sorry but I can't think of a reason good enough to avoid sequential numbers here and complicate our lives. Security through obscurity? – doblak Feb 12 '12 at 17:06
  • 1
    Hm, you are aware you likely LEGALLY have to hkeep them in order? Invoices and accounting lines must be numbered, and this must be non random in pretty much evey jurisdiciton i know - so one can test that the ledger is complete. Note that some pseudo random sequence fits in perfectly here - fast, and not random but random enough for people not to se ethe enxt number. If I were you I would talk to a lawyer here - and make sur you dont write useless software. – TomTom Feb 12 '12 at 17:08
1

You can use identity seed feature in SQL Server and define the starting value http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

Pablo Retyk
  • 5,690
  • 6
  • 44
  • 59
1

If your goal is to generate a seemingly random, unique transaction ID, I suggest using parts from the transaction owner (userid) and parts from the transaction itself and combine those to generate your unique transactionid. If that's not possible, then I suggest converting a GUID to a decimal. Here's how to do it: Converting System.Decimal to System.Guid

Community
  • 1
  • 1
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
0

If you have a compelling reason for wanting the IDs to be in random order, maybe you could write a one-time utility program in C# to generate the random list of numbers and insert them into a SQL table. Then, when your program needs an ID, it can select the next available one from that table. Of course, you'd need a bit field or something to mark which IDs have been used. You might want to put the select and mark operations into a stored procedure and have the procedure lock the table while it's working so simultaneous processes don't get the same ID before they've had a chance to mark it as used.

Paul
  • 11
  • 1