{"id":14,"date":"2008-01-17T17:18:45","date_gmt":"2008-01-17T23:18:45","guid":{"rendered":"http:\/\/todesco-technologies.com\/unboxed\/?p=7"},"modified":"2008-01-17T17:18:45","modified_gmt":"2008-01-17T23:18:45","slug":"use-guids-as-primary-key-in-your-database-design","status":"publish","type":"post","link":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/2008\/01\/17\/use-guids-as-primary-key-in-your-database-design\/","title":{"rendered":"Use GUID\u2019s as primary key in your database design"},"content":{"rendered":"

GUID\u2019s are unique identifiers. When you create one it is guaranteed there will never be another one that is the same. Not once. Nowhere.<\/em> <\/strong>(At least that\u2019s the theory, in reality GUID\u2019s are simply really \u2013 and I mean really, really \u2013 large random number that are very extremely unlikely to be repeated). GUID\u2019s are called UUID\u2019s sometimes (for example in Java, GUID is actually the Microsoft name) and in SQL Server they call them \u201cuniqueidentifier\u201d. However all of them mean the same thing: A 16 byte (128 bit) number. For a more detailed definition of GUID\u2019s read this: http:\/\/en.wikipedia.org\/wiki\/Uuid<\/a>.<\/p>\n

They have a big advantage: They don\u2019t need a central coordination to be created. This means wherever I create them they are guaranteed to be unique. This is great if you work with databases. It means you can create unique records without being connected with the database. It means as well, you can merge databases without a problem!<\/p>\n

Therefore I use GUID\u2019s (uniqueidentifiers) as primary keys when I design a database.<\/p>\n

Funny enough I don\u2019t see many people doing it, and I have almost always to convince colleagues using it. The main arguments against is are:<\/p>\n

    \n
  1. They are big and therefore slow<\/li>\n
  2. You can\u2019t read them<\/li>\n<\/ol>\n

    Before I start going in to more details I have to mention: The following examples assume a Microsoft environment (SQL Server, .NET Framework).<\/em><\/p>\n

    GUID\u2019s are bigger than your integer primary key (normally 4 times bigger \u2013 16 bytes instead of 4 bytes). The fact that they are bigger usually is not an issue in itself. Most DB\u2019s these days support huge sizes and having a bigger ID won\u2019t make the difference. A lot of people think though that your database will be slower because you have bigger primary keys. That is usually a much bigger issue for people and therefore needs clarification:<\/p>\n

    We have to separate the issue of speed in two discussions: query statements and insert statements. In Query statements there is almost no difference using GUID\u2019s or int\u2019s. If you think about it, it is logical: You have an index that is sorted and you will do a binary tree search over it. All you do is comparing a few numbers. Comparing a 4 byte or a 16 byte number makes (almost) no difference and will therefore not make your query much slower.<\/p>\n

    Inserting is a bit more tricky: Inserting a record with a GUID primary key takes considerably longer than its integer counter part! This has a simple reason: If you use an auto incrementing integer primary key you have a nice little side effect: Your record will be inserted at the end of the index (as your primary key was incremented). If you use a GUID (due to the random nature of a GUID) it will be inserted somewhere in the index. Finding this place in the index and moving around the data (resulting in page splits) takes time. If I say it takes time I\u2019m talking about miliseconds. If you insert single records that will make no difference at all. If you plan to insert huge amounts of data on a regular basis however (thousands of records in one go) it might be a performance issue. But there is a solution! Using the NEWSEQUENTIALID SQL Server generates GUID\u2019s that have an incrementing value (http:\/\/msdn2.microsoft.com\/en-us\/library\/ms189786.aspx<\/a>). With this approach you will have a very good performance that is comparable to using integer primary keys.<\/p>\n

    Whenever I hear someone claiming this and this is faster than that and that I say: We don\u2019t know until we run a test! Luckily someone has done exactly that for me: http:\/\/www.sql-server-performance.com\/articles\/per\/guid_performance_p1.aspx.<\/a><\/p>\n

    The second argument is that GUID\u2019s are not readable and not easy to remember. I must say this is a feature, not a bug! I think there is something wrong if you want users to remember primary keys. We are in the 21st century\u2026 If you need an Id (lets say an order Id, so they can talk to a sales rep) then generate one separately, but do not use it as your primary key.<\/p>\n","protected":false},"excerpt":{"rendered":"

    GUID\u2019s are unique identifiers. When you create one it is guaranteed there will never be another one that is the same. Not once. Nowhere. (At least that\u2019s the theory, in reality GUID\u2019s are simply really \u2013 and I mean really, really \u2013 large random number that are very extremely unlikely to be repeated). GUID\u2019s are … Continue reading Use GUID\u2019s as primary key in your database design<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-14","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/posts\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/comments?post=14"}],"version-history":[{"count":0,"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/posts\/14\/revisions"}],"wp:attachment":[{"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/media?parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/categories?post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/todescotechnologies.com\/unboxed\/index.php\/wp-json\/wp\/v2\/tags?post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}