MS SQL GUIDs, Curse or Benefit?

MS SQL GUIDs

What are the benefits and detriments of having a GUIDs within your database? Can you rely upon them to be truly unique? Are there any hidden “gotcha’s” associated with GUIDs? Today, lets dig into that and find out.

The other day, I was talking to a friend of mine and we were discussing GUIDs, and his observation was that they were messy to use as Primary Keys and also thought that they had some serious disadvantages, such as being very slow in large numbers. I, being the everything must have some benefit and detriment, pointed out some of the good things about them such as their extreme uniqueness, but I wasn’t to positive about their performance, although I did agree that they were very messy for trying visually see how things were linked and also could be a huge waste of physical space. Being the curious person that I am, I decided that I would dig into this and try to figure out exactly what a GUID was, a benefit or a curse. Now for this article, I am going to reference a lot of other websites, so it might be fast and loose and for that, and I will apologize for that in advance.

What is a GUID ({3cecbfbb-b53e-42e6-ae85-8b543f4b1ac3})?

Now the first question is, what is a GUID? A GUID, according to The Quick Guide to GUIDs, is a 1039 long hexadecimal number that is broken down into five sections (broken out in hex as 8, 4, 4, 4 and 12), and is supposed to be random with hints about how, when and where it was made. For me, that doesn’t cut it, so I dug further in and found a post by the Raymond Chen over at Microsoft’s MSDN are where he posted GUIDs are globally unique, but substrings of GUIDs aren’t, and describes how they are built.

  • 60 bits of timestamp
  • 48 bits of computer identifier
  • 14 bits of uniquifier
  • 06 bits are fixed

He goes onto describe how if a computer is generating two or more GUIDs within rapid succession (i.e. within the same second) or someone’s computer is back/forward date then the GUID counter will not be in the same place allowing the 14 bits of “uniquifier” to be different. To me, this means that the only way to get two GUIDS the same is if the records were 1) created at the exact same second, 2) the computer identifier has to equate out to be the same, 3) the GUID Counter must be in the same exact location on both machines.

OK, being at the exact same second is a difficult but not impossible thing for computers to create a record, but what about the hash of the computer identifier, how is that made? According to RFC 4122 Section 4.1.6, this is supposed to be generated from the IEEE 802 MAC Address, and if the system doesn’t have a MAC Address a pseudo-random value can be used. So in order to handle this, the computer must have either a) no MAC address and the pseudo-random number generator had to duplicate the values or b) you have to have installed a network card that had a hacked MAC address that is spoofing another computer (which would not allow you to be on the same network as the spoofed computer) or c) the same exact network card as another computer.

Lastly, the uniquifier, how does that work? Well, according to Ken Simmons who wrote Understanding and Examining the Uniquifier in SQL Server, the uniquifier is only hit when the record will be duplicated and the field is marked as Unique. This seems to be a safe bet the the GUID will be unique across different machines and times, even if the times are the same you should be pretty safe as long as the generating computer isn’t a virtualized computer, sharing physical access to the network card, running to a different SQL Server that is creating records at the same exact time and the same exact information.

Performance?

To test this out, I decided to build a program that would insert any number of records I wanted into one of three different table sets, a standard Integer controlled environment, using an integer key generated by the application, a GUID Automatically entered controlled environment and a GUID that the application would generate and manage. Very simple tables, almost identical except for the primary keys and the SQL Statements are as such

The program itself is to large to paste the Source Code, you can download it here, Database GUID Test; however, it will

  1. Drop the tables it anticipates working on
  2. Create the tables (ah, new table smell)
  3. Create the records one at a time and record the entire time it took for all records
    1. Create a “Header” Record, returning the Key Field using the SQL Output Clause (SQL 2008 and above)
    2. Create 10 “Detail” Records
  4. Join all the records for both the “Header” table and the “Detail” table and record the entire time it took for all records
  5. Get a listing of all the “Header” record Primary Keys,
  6. If selected, delete the records and record the entire time it took for all records
    1. Loop through all of the “Header” records and
      1. Delete all of the “Detail” records for the Header
      2. Delete the “Header” record
  7. If selected, drop the tables and act like it never occurred.
  8. Display the results of each event in milliseconds

There is some expected overhead for writing to the console the current record it is working on although I would suspect that is minimal and also I suspect that it should add to all three tests at the same time because that is in effect for all three. For the test, I am running a Windows 10 Intel Core i5 (2.40 GHz) with 8 Gigs of ram and SQL 2016, I had it generate 100,000 records into all three types at the same time in different instances of the application (try and press it hard) to see what would happen. Below are the results.

Insertions

  • 100,000 Records
    • Integer Based Records: 1666678.0463 Milliseconds = 27:46 Minutes
    • GUID Based Records – SQL Managed: 1716539.7326 Milliseconds  = 28:36 Minutes
    • GUID Based Records – Application Managed: 1683044.8951 Milliseconds  = 28:03 Minutes
  • 1 Record
    • Integer Based Records: 23.8278 Milliseconds
    • GUID Based Records – SQL Managed: 22.3039 Milliseconds
    • GUID Based Records – Application Managed: 23.6874 Milliseconds

Joinings

  • 100,000 Records
    • Integer Based Records: 7849.4239 Milliseconds = 00:07 Minutes
    • GUID Based Records – SQL Managed: 13602.463 Milliseconds = 00:13 Minutes
    • GUID Based Records – Application Managed: 12598.6037 Milliseconds = 00:12 Minutes
  • 1 Record
    • Integer Based Records: 7.1296 Milliseconds
    • GUID Based Records – SQL Managed: 7.5144 Milliseconds
    • GUID Based Records – Application Managed: 7.4296 Milliseconds

Deletions

  • 100,000 Records
    • Integer Based Records: 314318.4881 Milliseconds = 05:13 Minutes
    • GUID Based Records – SQL Managed: 345862.0843 Milliseconds = 05:45 Minutes
    • GUID Based Records – Application Managed: 334389.0808 Milliseconds = 05:34 Minutes
  • 1 Record
    • Integer Based Records: 4.4671 Milliseconds
    • GUID Based Records – SQL Managed: 4.265 Milliseconds
    • GUID Based Records – Application Managed: 4.0305 Milliseconds

Physical Space

  • 100,000 Records
    • Integer Based Records:
      • Header Record: 2.13 Mb
      • Detail Record: 27.69 Mb
    • GUID Based Records – SQL Managed:
      • Header Record: 4.57 Mb
      • Detail Record: 86.94 Mb
    • GUID Based Records – Application Managed:
      • Header Record: 4.50 Mb
      • Detail Record: 87.07 Mb
  • 1 Record
    • Integer Based Records:
      • Header Record: 8 KB
      • Detail Record: 8 KB
    • GUID Based Records – SQL Managed:
      • Header Record: 8 KB
      • Detail Record: 8 KB
    • GUID Based Records – Application Managed:
      • Header Record: 8 KB
      • Detail Record: 8 KB

Summary

All in all, the only two downsides to having a GUID as a primary key are Readability and Disk Space, Performance wise, it appears that it is nearly on par with each other, and even on a single entry the GUID appears to have a slight advantage. From these observations, a GUID is a realistic alternative for Integer values for a reference as Primary Keys.

14 comments

  1. I precisely wanted to thank you very much all over again. I am not sure the things that I might have worked on without the methods documented by you regarding that subject matter. It previously was a hard situation in my opinion, however , seeing a professional strategy you managed the issue made me to cry for gladness. Extremely thankful for this assistance and then hope that you comprehend what a great job you are always undertaking instructing many people through the use of your blog. I am sure you have never got to know all of us.

  2. Thank you for all your hard work on this blog. My mom take interest in doing investigation and it is obvious why. I notice all concerning the dynamic medium you give functional steps on the blog and increase participation from visitors on that issue so our daughter is really discovering a whole lot. Have fun with the rest of the year. You’re the one carrying out a stunning job.

  3. I and my guys appeared to be going through the best procedures from the blog and so immediately developed an awful suspicion I had not thanked the website owner for those tips. All the young boys came totally warmed to study all of them and have extremely been taking pleasure in them. Thanks for actually being considerably helpful as well as for deciding on varieties of beneficial ideas millions of individuals are really needing to be informed on. Our own honest apologies for not expressing appreciation to sooner.

  4. My spouse and i felt really thankful that Ervin managed to round up his reports with the ideas he acquired from your own web pages. It is now and again perplexing to just find yourself making a gift of instructions which a number of people might have been selling. We understand we need the website owner to appreciate for this. The main illustrations you’ve made, the easy site menu, the friendships your site make it easier to create – it is everything powerful, and it’s letting our son and our family know that the subject is satisfying, and that is unbelievably essential. Thank you for everything!

  5. I enjoy you because of all of the efforts on this web site. Kim takes pleasure in working on research and it’s easy to understand why. My spouse and i hear all relating to the lively way you produce sensible tips and hints on your blog and as well as cause contribution from others on that subject while our own girl is always being taught so much. Have fun with the remaining portion of the year. You have been conducting a tremendous job.

  6. I enjoy you because of all of your effort on this web site. My niece takes pleasure in making time for internet research and it’s easy to understand why. My partner and i learn all relating to the powerful form you give helpful techniques via this web blog and even attract participation from other ones about this article then my child is now becoming educated a lot. Take advantage of the remaining portion of the year. You are always carrying out a remarkable job.

  7. My spouse and i were now comfortable that Chris managed to carry out his reports using the precious recommendations he made out of your web pages. It’s not at all simplistic to just choose to be releasing guidelines that men and women have been selling. And we fully grasp we have got you to appreciate for this. All of the illustrations you’ve made, the straightforward blog navigation, the friendships your site help to create – it’s most superb, and it is assisting our son in addition to the family imagine that this content is awesome, and that is quite mandatory. Thank you for all!

  8. I wish to convey my appreciation for your kindness for those individuals that must have help on this issue. Your personal commitment to passing the message throughout appears to be exceedingly helpful and has encouraged regular people just like me to achieve their dreams. Your personal insightful help and advice signifies this much to me and further more to my office colleagues. With thanks; from all of us.

  9. I and my pals ended up reading through the excellent recommendations located on your site and unexpectedly developed a terrible suspicion I had not thanked the site owner for those tips. These women are already as a result glad to read through all of them and have in effect absolutely been loving them. Appreciation for simply being so considerate and for pick out such incredibly good subject areas most people are really desirous to be aware of. My personal sincere regret for not saying thanks to earlier.

  10. I really wanted to compose a simple remark to say thanks to you for all the stunning instructions you are placing here. My particularly long internet research has at the end been compensated with extremely good know-how to talk about with my friends. I would assume that many of us visitors are very much lucky to dwell in a really good place with very many perfect professionals with interesting tactics. I feel somewhat lucky to have seen your entire weblog and look forward to so many more amazing times reading here. Thanks a lot again for all the details.

  11. My wife and i were thankful Chris could carry out his analysis via the precious recommendations he gained from your weblog. It is now and again perplexing just to find yourself giving away helpful tips which usually the others might have been trying to sell. We discover we now have the website owner to be grateful to for this. All of the explanations you’ve made, the straightforward website menu, the friendships you will help instill – it is mostly fabulous, and it is leading our son and our family reckon that that subject is interesting, which is wonderfully serious. Many thanks for everything!

  12. Thank you for each of your hard work on this blog. Betty delights in managing research and it’s really easy to understand why. Most people know all regarding the dynamic form you make rewarding secrets through the website and increase response from some other people on this area while our favorite simple princess is in fact starting to learn a whole lot. Have fun with the remaining portion of the year. Your carrying out a good job.

  13. Thank you a lot for providing individuals with an extraordinarily marvellous opportunity to read articles and blog posts from this web site. It’s always very pleasing and full of fun for me and my office friends to search the blog nearly 3 times per week to study the newest secrets you have got. Of course, we’re certainly amazed with your cool inspiring ideas served by you. Selected 3 points in this posting are indeed the most efficient I have ever had.

Leave a Reply

Your email address will not be published. Required fields are marked *