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


An alternative to GUIDs


An alternative to GUIDs

Author
Message
iposner
iposner
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 127
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?
MikeTeeVee
MikeTeeVee
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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).
mikefolden
mikefolden
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 23
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 Smile

Keep up the good work!
John.E.McGuire
John.E.McGuire
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
nick 2435
nick 2435
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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.
John.E.McGuire
John.E.McGuire
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
ComputerFieldsInc
ComputerFieldsInc
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 45
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.
nicedream
nicedream
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 547
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?
tymberwyld
tymberwyld
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 Visits: 275
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.



Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
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