SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An alternative to GUIDs


An alternative to GUIDs

Author
Message
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10634 Visits: 11970
iposner (9/19/2011)
Sergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.


I cannot see anything "more efficient" in that new identifier.
Same size of the value, not very efficient process of generation, additional checks required to prevent duplications...
And it's all in sake of succession.

As SQL Kiwi (cheers mate!) said - evil is not in GUID, evil is in clustered key on GUID column. It's another extremely bad practice, so common that you'll say I should not challenge it.
But I still do.
You'll never select a range of GUID's, so it should be never used for a clustered index.

Problem with GUID's is not in lack of succession.
It's in architectural flaw of using internally auto-generated identifiers for global identification of business objects. No matter how smart will be your auto-ID it will never be good for the purpose.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7857 Visits: 3290
Well the size is half the size of the GUID.
I'm not sure how GUIDs are generated but I'm willing to be there is substantially more calculation involved albeit highly efficient and optimised calculation.

As to not being able to come up with something better than GUIDs that implies that GUIDs are at the pinacle of identity generation and can never be surpassed.

I have heard anecdotal evidence that GUIDS are only guaranteed unique on the machine on which they are issued. It is improbable that you will get two systems generating contradictory GUIDs but not impossible for it to occur.

Just as a matter of interest how do you handle multiple data centres, particularly where there is a large geographic distance between them?

LinkedIn Profile

Newbie on www.simple-talk.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88344 Visits: 41128
nick 2435 (9/15/2011)
We changed the SO numbering here to be a long set of letters and numbers that are part random and part sequence (based on time) which means the computer just “makes it up” rather than grabbing the next one.

What we did was build a 15 digit order number in the following format, using essentially base 30 - built from 30 easily distinguisable characters (23456789ABCDEFGHJKLMNPRTUVWXYZ)

S123-TYP-QRT-6RE


Interesting. What are you using to prevent the spelling out of random swear words?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
nick 2435
nick 2435
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
Interesting. What are you using to prevent the spelling out of random swear words?


Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88344 Visits: 41128
nick 2435 (9/20/2011)
Interesting. What are you using to prevent the spelling out of random swear words?


Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow :-)


You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out. You should be fine and I'm sure that the users that receive an SO with a swear word in it will marvel at the technical prowess of your company and are sure to create many additional orders with the company just to see which swear words will be generated. Of course, they'll also tell all of the people and maybe even some lawyer friends they know so they can all join in the fun. Yep... I'm sure it'll all be fine and your boss will actually congratulate you for providing so much fun for everyone especially those sour-puss lawyers. ;-)

At least remove the vowels and the letter "K" from the mix.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jh-638585
jh-638585
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 18
Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).

I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about Identity columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed by the database (single instance).

Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.

Perhaps you suggest all identity/identifier fields should be calculated somehow using an appropriate guid-type creation function from within the database?

I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that this database is only to be access from this application or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....limiting.

Again, very much enjoyed this lateral thinking article.
iposner
iposner
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 127
A common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.
nick 2435
nick 2435
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out.


To be honest though we have been running this scheme for a few years (around 300,000 orders since we moved to using it) and although a few have appeared we have never had any comments. I suppose it is quite childish I guess to "spot" swear words in order numbers ;-) - and especially with the hypens they become less obvious.

I am the business owner so no one is going to shout at me either!
StuR
StuR
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 69
jh-638585 (9/27/2011)
Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).

I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about Identity columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed by the database (single instance).

Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.

Perhaps you suggest all identity/identifier fields should be calculated somehow using an appropriate guid-type creation function from within the database?

I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that this database is only to be access from this application or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....limiting.

Again, very much enjoyed this lateral thinking article.


Hey jh, you are correct in what you assert for a single server environment. The issue here is really what happens when a database table spans multiple SQL Servers, either for temporary capacity or as a part of a failover procedure.

It was only a few years ago that this sort of argument was not even contemplated due of cost except in the biggest systems but with the dropping price of hardware & hosting, along with the increasing abilities of SQL Azure, it is becoming more and more of a consideration.

Even then the partitioning of a table ("vertical" partitioning rather than "horizontal" I guess) is really only an issue when you need to bring some or all of the partitioned data back onto a single DB server. Where a system is partitioned across multiple SQL Servers for temporary capacity increase, or when a DR site temporarily receives live transactions are examples of this. With identity columns on each server this means you almost always have key collisions.

There are ways to get around this, and the guid, sequential guid and generated ID methods are just some of them.

Hope this helps!



StuR
StuR
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 69
iposner (9/27/2011)
A common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.


Plus 1 to that. I am constantly amazed by otherwise smart people on both sides of one software divide or another that are unwilling to understand things that directly affect what they do, and in many cases software projects live or die by these decisions.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search