148

I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server

0

6 Answers 6

132

This has been asked in Stack Overflow here and here.

Jeff's post explains a lot about pros and cons of using GUID.

GUID Pros

  • Unique across every table, every database and every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database, unless partial sequentiality is needed (i.e. with newsequentialid())
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL Server 2005+) and to enable use of clustered indexes

If you are certain about performance and you are not planning to replicate or merge records, then use int, and set it auto increment (identity seed in SQL Server).

5
  • 33
    Another con of the GUID approach is that you cannot use it as an identifier for your end-user. Do you really expect your users to tell you on the phone that they have an issue with Order "BAE7DF4-DDF-3RG-5TY3E3RF456AS10" ? :)
    – Brann
    Jul 8, 2011 at 21:22
  • 4
    If you do not use sequential guids, and your primary key is clustered (the SQL Server defaul) then all your data inserts will be randomly scattered throughout the table, leading to massive fragmentation of your data. That is assuming that the data would normally be inserted in some sort of order, such as chronological.
    – datagod
    Aug 6, 2011 at 4:32
  • 10
    Sequential guids are only sequential until the SQL instance is restarted. Then the first value will more than likely be lower than the prior one because of the way that the root value is generated, causing all sorts of problems all over again.
    – mrdenny
    Aug 6, 2011 at 6:37
  • 49
    @Brann Ideally you wouldn't be given your PK values out to end-users in the first place. I know it is somewhat common to do so, and it is something I myself have done in the past before I learned not to. But since it shouldn't be done, that particular reason to prefer INT over GUID isn't a valid one. Oct 7, 2015 at 18:54
  • 5
    @ChadKuehn Choosing UNIQUEIDENTIFIER over INT because INT has an upper limit is rather poor reasoning since being limitless, while true enough, is not a practical benefit. You can easily double the effective capacity of an INT by starting it at the lower limit (-2.14 billion) instead of at 1. Or, if the full 4.3 billion isn't enough, then start out with a BIGINT that is still only 8 bytes as compared to 16 for the GUID, and it is seqeuential. Oct 7, 2015 at 18:59
32

I have used a hybrid approach with success. Tables contain BOTH an auto-increment primary key integer id column AND a guid column. The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row.

The id identifies the row in this table. The GUID (at least in theory) identifies this row anywhere in the known universe. In my project, Android mobiles each have a structurally identical copy of the table on a local SQLite database. The row and its GUID are each generated on Android. Then, when Android is synchronized to the back-end database, its local row is written to the back-end table without fear of conflicting with rows created from any other Android mobile.

2
  • 4
    I have used this approach myself and it works quite nicely. The GUID is just an alternate key, with a NonClustered index, and is passed in from the application, but only resides in the primary table. All related tables are related via the INT PK. I find it strange that this approach is not much more common given it is the best of both worlds. It seems like most people just prefer to solve problems in very absolutist terms, not realizing that the PK doesn't need to be a GUID in order for the app to still use GUIDs for global uniqueness and/or portability. Oct 7, 2015 at 19:05
  • @SolomonRutzky if you have both and set the PK as being the INT-IDENTITY instead of being the GUID you lose the 2 major advantages of guid: 1) You'll have to wait for the db to generate the keys (if you add parent and child there's an extra roundtrip); 2) If you're synching distributed databases you'll have to renumber both your identity of the parent table AND the associated FK of child tables. If your PKs are only guids it's much easier, and then INT can be used only for non-critical things like url-identifiers.
    – drizin
    Dec 13, 2020 at 22:44
24

If you're synchronizing your data with an external source, a persistent GUID can be much better. A quick example of where we're using a GUIDs is a tool that is sent to the customer to crawl their network and do certain classes of auto-discovery, store the records found, and then all the customer records are integrated into a central database back on our end. If we used an integer, we would have 7,398 "1"s, and it'd be a lot harder to keep track of which "1" was which.

0
15

Using auto increment IDs might leak information about your business activity. If you are running a shop and uses order_id to publicly identify a purchase, then anybody can find out your monthly number of sales by simple arithmetic.

0
4

The answer by rmirabelle is what I do. However, for larger-scale projects, there is an ultimate design, where both can be used:

Use: a Key Mapping Table

The TableA.ID is used locally in your database as the Primary Key, and the key to use for JOINing. TableAMap.ID is the same as TableA.ID, and TableAMap.UniversalID is used for between systems only.

TableA

- ID int (PK)
- Data varchar(100)

TableAMap

- ID int (PK)
- UniversalID GUID (Indexed - nonclustered)

GUIDs are seldom needed for database replication/import/export. So, instead of having the GUID on the main table, where it takes up an extra 8-bytes per row, and where a GUID index will be (by default) stored on the same volume; a separate table (aka normalization) comes to the rescue.

With a separate table, your DBAs are free to store it on another slower disk. Also, if the GUID is ONLY needed for certain batch jobs, you can create the GUID index just before it's needed, then drop it after.

Also, it's certainly possible to simply add UniversalID to TableA, instead of adding an extension table such as TableAMap.

2
  • What's the use of this? You can just delete TableAMap and your system will work fine.
    – Tvde1
    Mar 22 at 14:58
  • @Tvde1 I have updated my answer. Upon review, I notice it was a bit too vague. The purpose of UniversalID is that you can have both. The role is important though: ID is used in the local database and for JOINing. UniversalID is ONLY to be used for integration between systems. Mar 23 at 0:13
3

I see a whole lot of typical answers on this thread about Random GUIDs and things like NEWSEQUENTIALID() saving the day. Yes, I do agree GUIDs in any form are quite large but that's really the only problem they have. I'll tell you that Random GUIDs are NOT the fragmentation problem that they they've supposedly been "proven" to be. In probably the most heterodoxical presentation about SQL Server that you'll ever see, I prove that Random GUID fragmentation is actually a myth perpetuated by insufficient testing and misinformation. It turns out they're not the problem... WE ARE THE PROBLEM!

To summarize on how to prevent GUID fragmentation quite literally for millions of inserts...

  1. Because of the even distribution, when you reach about 1% fragmentation, you're on the verge of massive fragmentation happening in the table on virtually all pages all at once. You must take action when you go over 1% logical fragmentation.

  2. The action that you need to take is to create free-space above the Fill Factor. That means that you MUST NOT USE REORGANIZE because it's not capable of creating additional pages to spread out the index to make the free-space above the Fill Factor. In fact, REORGANIZE COMPACTS pages UP to the Fill Factor. In other words, it does its best to remove as much free space as possible at the worst possible time. Instead, you MUST use a REBUILD instead.

  3. It also turns out that because of the compaction, REORGANIZE actually causes and perpetuates fragmentation. It turns out that it's actually better to NOT do any index maintenance rather than to do it wrong and the compaction of the index that REORGANIZE does is doing it totally wrong because it cannot create the free space above the Fill Factor to stop the fragmentation. If you're 24/7 or using the Standard Edition where you can't do an ONLINE rebuild, then wait for a maintenance period when you can.

  4. Additionally, REORGANIZE is advertised as being much less resource intensive than REBUILD. That's actually not true especially when you've lowered the Fill Factor to prevent fragmentation even on non-GUID indexes.

I agree that because of what we've been taught over the years, this all seems impossible. Here's the final slide of a presentation I made that shows the one year history of GUID Clustered Indexes (see the legend) using presorted GUIDs as an ever-increasing baseline with no fragmentation, Random GUIDs with no index maintenance, and Random GUIDs with 3 different Fill Factors where the 1% "Low Threshold" Rebuilds were used at an insert rate of 100 THOUSAND rows per day.

The "Flats are where virtually no fragmentation occurred

Here's one of the slides that show how bad REORGANIZE is for the log file. The Red Line is the supposed "Best Practice" line and the little tiny GREEN LINE at the very bottom of the chart is the 1% "Low Threshold" Rebuild Line.

Resource usage comparison between REORGANIZE and "Low Threshold" REBUILDs

If you care to see the presentation, please see the following video where I destroy the myths of Random GUID fragmentation and lay waste to the supposed "Best Practice" Index Maintenance that 98% of the world has made the mistake of using for the past 22 years. Then, also understand that the lessons learned about index maintenance apply to other types of indexes.

Here's the video link. As warning for those wearing headsets, they inserted some rather suddenly occurring and loud ads at the 15:00, 30:15, and 45:25 timestamps to "pay the bills" to keep GROUPBY.org going.

Here's the link.

Black Arts Index Maintenance 1.2 - Guids vs. Fragmentation

0

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