Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

An alternative to GUIDs Expand / Collapse
Author
Message
Posted Monday, September 19, 2011 3:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1177518
Posted Monday, September 19, 2011 4:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 2,889, Visits: 1,779
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
Post #1177536
Posted Monday, September 19, 2011 9:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 36,712, Visits: 31,163
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1177610
Posted Tuesday, September 20, 2011 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 6, 2011 1:24 AM
Points: 4, 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
Post #1177671
Posted Saturday, September 24, 2011 6:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 36,712, Visits: 31,163
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1180573
Posted Tuesday, September 27, 2011 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 2:17 PM
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.
Post #1181533
Posted Tuesday, September 27, 2011 1:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
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.
Post #1181536
Posted Tuesday, September 27, 2011 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 6, 2011 1:24 AM
Points: 4, 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!

Post #1181537
Posted Tuesday, September 27, 2011 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:40 PM
Points: 7, 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!



Post #1181622
Posted Tuesday, September 27, 2011 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:40 PM
Points: 7, 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.



Post #1181624
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse