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 ««12345»»»

An alternative to GUIDs Expand / Collapse
Author
Message
Posted Thursday, September 15, 2011 3:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
One of the mottos that has served me well over the years is "Don't code a solution to an infrastructure problem when an infrastructure solution exists".

To cope with failover, there are a number of infrastructure solutions including mirroring and log shipping. Trained DBAs are familiar with the processes involved in failover in these scenarios.

It is not the job of the application/database developer to implement his own failover mechanism, especially in larger shops where standardisation, monitoring, alerting and interchangeability of team members are all issues. Additionally it is the responsibility of DBAs to protect the availability of company data, not that of the developer. Furthermore, the DBAs will be working to a service level that will require one or more of them to be available to deal with failover contingencies - can this be equally said of a developer?
Post #1175512
Posted Thursday, September 15, 2011 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:48 AM
Points: 2, Visits: 192
I agree with iposner. Database failover redundancies are the DBA's job, not the Developer's.
There should be no metadata in the data a developer is using to help them figure out which server created the record - if you think you need that type of information, then that should be a tell-tale sign you're already headed down the wrong path.

I also don't care for some of the examples other are posting of calling another service to get an ID, well what happens when that service goes down (because you have no control over it or those who hit it) - Can we say, fail-whale?

Then there's the suggestion of creating an ID with letters and numbers in it to represent what type of record it is. You can generate this ID before displaying it to the user. If the user query's for the mnemonic-ID, you can convert it into a query that parses out the mnemonic-ID to query based on record type, numeric ID, and so on.

It is best to have a sequential numeric ID for performance purposes. It saves on space, and allows for better indexing, which equals better speed.

There are times when you do need a GUID, but I make it a point to never use them as a Primary Key. Instead, for example, I use them when creating a new login that I need to email to a user for a confirmation link. That GUID is not their User ID, instead it is linked to their User ID. If you don't want GUID's expanding your table row length, then throw them into a separate table (i.e. A table with just UserID and ConfirmationGUID, where UserID is a foreign key to the Primary Key in the User's table).
Post #1175549
Posted Thursday, September 15, 2011 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 11:33 AM
Points: 4, Visits: 22
Thanks for a very good thought provoking article.
Every time I try to think of an alternative to a 16 byte GUID,
an 8 byte BIGINT (in one form or another) always seems to
be a reasonable alternative.

That being said, I believe there is a small typo in the article.
There are 86400 seconds in a day, not 84400 :)

Keep up the good work!
Post #1175602
Posted Thursday, September 15, 2011 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 7:40 AM
Points: 6, Visits: 41
I like this solution (and Nick 2535's). it is simple. Doesn't use GUIDs. And has time stamp as high order portion of key.
Post #1175607
Posted Thursday, September 15, 2011 6:16 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
John.E.McGuire (9/15/2011)
I like this solution (and Nick 2535's). it is simple. Doesn't use GUIDs. And has time stamp as high order portion of key.


Thanks

It's also VERY customisable to give you tighter timing, or less chance of a collision - or not timing if you want. The ability to just get the last 3 digits off a customer for reference - that generally allows you to see their order (as ordered on screen newest first).

The only place we haven't rolled it out is for invoice numbering as the UK VAT (tax) office don't allow non-sequential numbering.
Post #1175614
Posted Thursday, September 15, 2011 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 7:40 AM
Points: 6, Visits: 41
You're welcome

"Simple" meant that it was easy to understand, people friendlier (for both company and customer) than some I've seen, and easy to implement (code and test).

Per the "people friendly" point, I do not understand why some customer numbers are umteen chars long without any punctuation. They should be easy for the customer to recite and write, too.

Enough ranting.

"Happy" Jack
Post #1175616
Posted Thursday, September 15, 2011 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 11:41 AM
Points: 1, Visits: 43
Great article, and great responses. I'm a developer on a team working on a redesign of a Visual FoxPro application. Initially the project scope will focus on migrating the VFP data to SQL Server along with redesigning the VFP app to use SQL as the backend. Then a new .NET app will be built to replace the VFP app.

One of the topics that comes up frequently in our discussions is touched on by this article... namely:

"How can we know what the parent/child/grandchild id's are so we can pre-populate PK/FK fields before we start a transaction?"

The diagram in the article seems to address this, and to me also seems to throw transactions out the door. Is this normal? After persisting the parent, and retrieving the PK, what happens if/when child records cannot be persisted and we need to roll back to a state prior to the parent being persisted?

Our existing VFP app has a "key generator" function that increments an integer value stored in an "id table" which is just a table with an integer field. Since the database is always local to the app (on a LAN), performance is fine.

But, with our redesign, we will be migrating multiple geographically dispersed VFP databases into a single SQL Server instance, and now WAN and Internet latency will become a factor. I can see that repeated calls to either a SP to retrieve an ID value, or repeated calls to retrieve auto-incremented identity values, might become a performance bottleneck.

Why are GUID's so bad? As long as they're not used as meaningful ID's (I.E. something people need to read and understand as part of the data) who cares how ugly they are? What realistic performance issues are there in SQL Server with GUID's?

If someone can point me to resources documenting why GUID's are bad for SQL Server, it would help us.

Again, great article, and thoughtful responses. Thank you.
Post #1175618
Posted Thursday, September 15, 2011 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 1, Visits: 440

If identity integer columns cannot be used because of the possibility of the same number being generated by two different servers, why not make the primary key a composite key that consists of the identity column + a tinyint that indicates which server it came from?
Post #1175631
Posted Thursday, September 15, 2011 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
Right, I've seen it mentioned on here as well, but we use GUID COMBs as well (a GUID which has as it's basis a stamp in time). We have one FUNCTION in the database and all our "ID" columns use this function to derive their primary key in stead of "NEWID()" or "NEWSEQUENTIALID()". In the code, the same is done so we don't have to hit the database. A "helper" or utility class was created so we can just say "SomeObject.ID = DataUtility.NewSequentialID()". We couldn't do it with .NET Extension methods because extension methods rely on a instance already being created in order to pick up their intellisense (ex. this makes no sense to do: Guid.NewGuid().NewSequentialID()). So a static class/method was used.

So, don't reinvent the wheel in this case, the GUID COMBs work great! I've also been able to insert millions of rows using that function and it was fast! Also, there is little to no fragmentation in the database because these ARE sequential IDs.



Post #1175635
Posted Thursday, September 15, 2011 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
ComputerFieldsInc (9/15/2011)
Great article, and great responses. I'm a developer on a team working on a redesign of a Visual FoxPro application. Initially the project scope will focus on migrating the VFP data to SQL Server along with redesigning the VFP app to use SQL as the backend. Then a new .NET app will be built to replace the VFP app.

One of the topics that comes up frequently in our discussions is touched on by this article... namely:

"How can we know what the parent/child/grandchild id's are so we can pre-populate PK/FK fields before we start a transaction?"

The diagram in the article seems to address this, and to me also seems to throw transactions out the door. Is this normal? After persisting the parent, and retrieving the PK, what happens if/when child records cannot be persisted and we need to roll back to a state prior to the parent being persisted?

Our existing VFP app has a "key generator" function that increments an integer value stored in an "id table" which is just a table with an integer field. Since the database is always local to the app (on a LAN), performance is fine.

But, with our redesign, we will be migrating multiple geographically dispersed VFP databases into a single SQL Server instance, and now WAN and Internet latency will become a factor. I can see that repeated calls to either a SP to retrieve an ID value, or repeated calls to retrieve auto-incremented identity values, might become a performance bottleneck.

Why are GUID's so bad? As long as they're not used as meaningful ID's (I.E. something people need to read and understand as part of the data) who cares how ugly they are? What realistic performance issues are there in SQL Server with GUID's?

If someone can point me to resources documenting why GUID's are bad for SQL Server, it would help us.

Again, great article, and thoughtful responses. Thank you.


For why GUID's are bad for SQL Server you should start at Kimberly Tripp's blog. Here's a link, http://sqlskills.com/BLOGS/KIMBERLY/category/GUIDs.aspx. The top blog post on the link is the second so scroll down and read the bottom post first.

It's no so much that GUID's are bad, but more about how you use them. They use more space than most applications really need which means fewer rows fit on a page which means more reads and IO is typically the biggest performance problem.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1175636
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse