| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2008-08-31 : 15:44:03
|
| Hi there,I would like to generate a random order id for each order made to my online shop. Something of the form '2BXK81'. (I don't want to use the table's integer auto incrementing column because then the shoppers can work out how much stuff I've sold.)If I create the id when the order is created then I think that there's a fairly good chance that the generated order id will already be in the database. This could result in a potentially indefinite loop as a unique number is searched for.One way around this is to create a table of pre-generated order ids and then just refernece them when required using the order tables simple incrementing integer id. I suppose that I could start the table off with a million ids to begin with. The problem here is that eventually the ids will run out and probably someone will have forgotten to add new ones.Any ideas which route I should go down?Cheers, XF. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-31 : 16:18:19
|
| use newid()_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-09-01 : 15:16:32
|
| I can't go giving users a guid as an order number. Its way too unwieldy. When someone rings up with a query, one doesn't want to have to take 32 characters to look up an order. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-01 : 15:21:36
|
| I use left(newid(),8) to get the first 8 characters. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-01 : 15:24:43
|
quote:
Originally posted by afrika
I use left(newid(),8) to get the first 8 characters.
I would not recommend this as it is not unique enough.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-01 : 15:25:52
|
quote:
Originally posted by X-Factor
I can't go giving users a guid as an order number. Its way too unwieldy. When someone rings up with a query, one doesn't want to have to take 32 characters to look up an order.
Use the identity column but start off with say 1,000,000 and use bigint. That way they things your products are very popular!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-01 : 15:29:07
|
| something like set @sessionID = left(newid(),8)or set @sessionID = left(newid(),8) +right(newid(),8) |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-01 : 15:31:51
|
quote:
Originally posted by tkizer
quote:
Originally posted by X-Factor
I can't go giving users a guid as an order number. Its way too unwieldy. When someone rings up with a query, one doesn't want to have to take 32 characters to look up an order.
Use the identity column but start off with say 1,000,000 and use bigint. That way they things your products are very popular!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Actually we use the above for passwords.for products. We have a code for the category, Which is abreviated. And append an auto generated number to itSo from the first 3 characters you will know the category it belongs to and the others are the product or order number |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-01 : 15:51:56
|
quote:
Originally posted by tkizer
quote:
Originally posted by afrika
I use left(newid(),8) to get the first 8 characters.
I would not recommend this as it is not unique enough.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Hi Tara,he says he doesnt want to use auto increments.When ever i fly virgin, thier ticket reservation number is also a funny 8 character number. That looks like an auto generated alpha numeric character. And if He has a unique constraint the db will kick out the value if it isnt unique. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-02 : 01:01:47
|
| afrika, your solution isn't guaranteed to be unique so it can not be used where uniqueness is needed.You can't assume that Virgin airlines is using your solution for their ticketing system even if it looks similar. They are probably building the values rather than relying on a random number and then grabbing only a portion of it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-02 : 03:59:53
|
 |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-09-02 : 12:52:30
|
| OK, well I think I'm going to go with the pre-populated table. I'll stick a million codes in there. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-09-04 : 10:02:30
|
| What I have done is insert a guid into the order code column upon the insert to keep the unique constraint happy. Then used an after trigger to copy over a friendly code from the pre-populated table into the column. If no friendly code is found then uses will just have to make do with a guid until some more codes can be added. |
|
|
|
|
Generating a random order id