Guid vs INT - Which is better as a primary key?

Question

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}

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

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.

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