Should I use GUID or int as a primary key?

18

I'm about to start a new project in MVC 4 with Entity Framework, searching I found several examples here in the SOpt of models that use GUID as ID and some doubts have arisen:

What is the advantage of using GUID as a primary key?

Is it feasible to use in a small project?

Are there any cons when using GUID?

    
asked by anonymous 19.06.2014 / 21:38

4 answers

12

Advantage of using GUID:

Generates a unique value in each table and each database and this allows for easy merging / merge / migration of records between different databases. You can generate your IDs in the application without needing the database, for example:

Guid meuNovoGuid;
meuNovoGuid = Guid.NewGuid();

Disadvantages of using GUID:

I see the GUID as a large and unnecessary number, this can have serious performance and storage implications if you are not careful. Mainly for the case of an Asp.Net MVC application, where we have url's friendly is much simpler terms:

http:\localhost\Cliente\Detalhes34 instead of http:\localhost\Cliente\Detalhes1E9502-E283-4F87-9049-CE0E5C76B658

Using int in your Asp.Net MVC application will be easier to understand, display those IDs for users in grids for example and you will get better performance as well.

    
19.06.2014 / 22:48
14

What is the advantage of using GUID as the primary key?

Advantages:

  • Removes classic primary key IDENTITY problems, as the record limit increases considerably (plus or minus 5,316,911,983,136,663,491,615,228,141,141,400,000 records), and avoids gaps between Id's;
  • Make a human search for data (especially malicious ones) unpredictable, improving security. The search is made by another column, not by Id, which may not be safe in a modern application;
    • In the examples given in other responses, a user can easily try to access a record by placing any Id's in the address bar and making requests GET testing the existence of a record or not;
  • Removes the responsibility for column generation from the database. The column is generated by the application;
  • Eliminates almost all competition issues related to inserting and updating records. The rest can be resolved with explicit transactions;
  • Migrations and joins of data are tremendously simpler, since there is no problem reserving ranges of values for the primary key;
  • More natural for Ajax with aggregate and dependent entities, because there is no need to work with temporary keys. The key generated in the creation of the record can be used in the recording of the record, or else it is treated as provisional and replaced by the main entity and its derivatives.

Is it feasible to use in a small project?

Yes. There is no problem in using it.

Are there any cons when using GUID?

Yes, just like any other choice of the primary key data pattern for your application:

  • % s of% s usually use 16 bytes . This number may vary depending on the implementation, whereas Guid uses 4 bytes and int uses 8. Depending on the volume of data, the use of bigint s can significantly increase the volume of data stored;
    • A caveat is needed here: the difference is not as glaring as you might think, especially considering the volumes of data supported by the current hosting services.
  • Performance overhead is on the order of 10%;
  • Depending on the size of the table, using Guid s completely random may cause performance loss if the index is Guid . This is because there is no logical sorting pattern for the index to follow. One of the alternatives to this is to put some sequence pattern in the clustered generation, as explained in this Code Project article .
26.06.2014 / 19:16
10

If your case is a small project, most likely not. Of course I can not speak with precision, since "small" does not define well what the project is.

Usually in small projects use GUID is a violation of YAGNI . And it is very common for projects to violate this principle.

You should ask yourself what advantages you will have in your project. If you are not seeing a problem that requires the GUID, you are probably wanting to seek theoretical unnecessary perfection. So unnecessary that it turns against you.

  • Is your database distributed?
  • Do you have any unusual organization of how the database is deployed?
  • Does really need any operation that joins more than one table with the same structure and which IDs can conflict?
  • Is your application experiencing performance issues in obtaining a new ID in the database?
  • Are there multiple concurrent clients needing to create transactions with voluminous insertion of data that having the ID available before accessing the database would bring some advantage?
  • Do you know how to use it properly?

One of the most committed mistakes is that the programmer prefers something that everyone says is good and he does not know what to do with it. It may even be good for others who are experienced with it. The best tool is the one you know.

You have more technical reasons:

  • GUIDs are worse for computers and humans to manipulate / visualize;
  • consume space too in many ways;
  • and consequently worsens performance, even minimally.

It could be pristine and say that it is not 100% guaranteed single. But in practice it is.

    
20.06.2014 / 01:33
2

According to Microsoft, only GUIDs are used in very particular applications. These data types have many disadvantages:

1 - They are 50% slower than the bigint, regarding the search (I'm not considering joins);
2 - They are bad for maintenance,
3 - Regarding the int / bigint take up a lot of space.

You should evaluate the advantages and disadvantages. In my projects I only saw disadvantages.

Microsoft link: link

    
14.11.2018 / 21:03