6

I want to use GUIDs since they are portable and makes it easier to use several database. But since there are performance issues it might not be a good idea? Although I've found a MS blog entry which states otherwise: Link

I've read about an alternative where a datetime2 column is used as the clustered index and the Guid as a PK. As most of my tables have a CreatedAtUtc column that should be a viable option for me. But do it really help?

So my questions is whether I should use GUIDs to get portability or if there are better solutions?

(Using GUIDs I can also create the Id in my code and do more stuff before committing). I've also created a COMB generator which should work better with MSSQL than regular GUIDs.

Improve this question

3 Answers 3

Reset to default
8

GUIDs only really cause performance problems for a couple of reasons.

First their size. You are using a 16 byte identifier where an int(4 bytes) or even bigint(8 bytes) is much smaller. This doesn't really matter so much on a wide table or short table. If your table is fairly narrow then the percent increase is of course greater. A 2000 byte width vs a 2012 byte width is really not that big a deal where a 20 vs 32 is obviously a greater difference. And if your table is going to be large, millions or even 100's of millions of rows, then that 8-12 bytes you are adding is going to make a more obvious difference than say a table with only 1000 rows. On the other hand if you need that portability then you may be willing to accept the space issues.

Next are the issues that only occur if the GUID is part of the clustered index. If the GUID is the clustered index then the GUID will be included the leaf level pages of all non-clustered indexes. So to compare, a table with an integer clustered index will only add 4 bytes to the leaf page while the GUID will add 16. This can add up over time particularly with multiple indexes. It also will of course increase the number of reads to use that index since you can fit less entries per page. The other problem GUIDs have with a clustered index is that they are not typically in order. This means potential page splits during inserts. The link you provided mentioned that this isn't a huge increase over the write times in Azure however. Typically you could use use NEWSEQUENTIALID() to generate the values as it returns a value greater than any previous value generated by NEWSEQUENTIALID() on that computer since windows was started however it is not supported by Azure.

In my opinion if you are already going to have a CreatedAtUTC column then go ahead and make it the clustered index and use the GUID as your primary key. Also here is a link I found to a blog by the Azure team on the subject. Of course he self processes to love GUIDs so he is somewhat biased. Also another benefit of using your CreatedAtUTC as the clustered index is that it doesn't change. This means minimal row moves.

Improve this answer
5
  • What other alternatives are there? I do need portability, but it doesn't have to be GUIDs.
    – jgauffin
    Jan 16, 2014 at 17:48
  • Depends on how portable you need it to be. GUIDs are best for it but I've done BIGINT with different start values .. Say 1000000 for one DB, 2000000 for another, 3000000 for the next etc. Or a combined key, a CHAR(4) + an int for example.
    – Kenneth Fisher
    Jan 16, 2014 at 17:52
  • RE: "however it is not supported by Azure." This has been supported since SQL Azure V12: msdn.microsoft.com/en-us/library/ms189786.aspx
    – JoeBrockhaus
    Jun 11, 2016 at 18:38
  • @JoeBrockhaus Glad to hear that's changed.
    – Kenneth Fisher
    Jun 11, 2016 at 21:02
  • The link to the azure blog is now dead. stackoverflow.com/help/how-to-answer "Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline."
    – Reinstate Monica
    Feb 16, 2017 at 10:39
0

You can just make an extra index on the GUID column, in addition to what you internally use as a primary key index. Guid would come in most handy if storing a record ID client side. This gives a lesser chance of someone spoofing user IDs that are sequentially or incremental and obvious to predict.

Improve this answer
0

The blog post you have essentially says that GUIDs are OK for SQL Database, due to the fact that Microsoft will compensate for the fragmentation and performance issues for you. I can't say that I've seen this work well for anyone, but if you truly need to create unique identifiers for portability, then GUIDs are one way to go.

You can also read this post regarding a manual solution put into place: http://cloud.dzone.com/articles/how-create-incremental-sql

There is also SnowMaker, which should work for any Cloud environment: http://blog.tatham.oddie.com.au/2011/07/14/released-snowmaker-a-unique-id-generator-for-azure-or-any-other-cloud-hosting-environment/

HTH

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.