128

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

Improve this question
4
  • 1
    Rather than primary key, I think you mean surrogate key i.e. a key that is not the natural key (the latter being the key we use in the real world). Possibly you mean clustered index. – onedaywhen Jan 31 '12 at 8:48
  • Also remember the difference between (Primary) KEY and INDEX. – Allan S. Hansen Jan 7 '14 at 6:55
  • 1
    Also discussed on SO: stackoverflow.com/questions/11033435/… – Jon of All Trades Jun 22 '15 at 21:41
  • 2
    "int has no flaws except by the number limit, which in many cases are irrelevant.": actually, within this context of INT vs GUID, the upper limit of a signed, 32-bit INT is entirely irrelevant given that the upper limit of a signed, 64-bit BIGINT is well beyond nearly all uses (even more so if you start numbering at the lower limit; and same goes for INT) and it is still half the size of a GUID (8 bytes instead of 16) and sequential. – Solomon Rutzky Oct 7 '15 at 18:41
114

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

Improve this answer
6
  • 30
    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 '11 at 21:22
  • 3
    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 '11 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 '11 at 6:37
  • 39
    @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. – Solomon Rutzky Oct 7 '15 at 18:54
  • 4
    @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. – Solomon Rutzky Oct 7 '15 at 18:59
24

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.

Improve this answer
5
  • 3
    What value does the GUID give if the id already is sufficient for humans to identify a row? – Martin Smith Apr 3 '15 at 18:06
  • 7
    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. – rmirabelle Apr 3 '15 at 21:46
  • Makes sense!... – Martin Smith Apr 3 '15 at 21:47
  • 3
    @MartinSmith 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. – Solomon Rutzky Oct 7 '15 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 '20 at 22:44
19

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.

Improve this answer
0
6

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.

Improve this answer
0
1

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

Use: a Key Mapping Table

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.

Improve this answer

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