Should I use GUID or int as the primary key?

Question

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

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

It is feasible to use in a small project?

There are drawbacks in using GUID?

Answer 1

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

Perks:

• Eliminates classic primary key problems IDENTITY, because the record limit increases considerably (about 5,316,911,983,139,663,491,615,228,241,121,400,000 records), and avoids gaps between Id’s;
• It makes unpredictable a human search for data (especially malicious ones), improving security. The search happens to be done by another column, and 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 responsibility for column generation from the database. The column is generated by the application;
• Eliminates almost all competition issues related to entering and updating records. The rest can be solved with explicit transactions;
• Migrations and junctions of data get tremendously simpler, as there is no problem of reserving ranges of values for the primary key;
• More natural for Ajax with aggregated and dependent entities, because there is no need to work with provisional keys. The key generated in the creation of the record can be used in the recording of the record, or else be treated as provisional and replaced by persisting the main entity and its derivatives.

It is feasible to use in a small project?

Yeah. No problem using.

There are drawbacks in using GUID?

Yes, as with any other choice of your application’s primary key data pattern:

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

Answer 2

If your case is a small project, most likely not. Of course I can’t speak accurately, since "small" doesn’t define well what the project is.

Normally in small projects utilize GUID is in violation of YAGNI. And it’s very common for projects to violate this principle.

You may wonder what advantages you will have in your project. If you’re not seeing a problem that needs the GUID, you’re probably looking for the theoretical unnecessary perfection. So unnecessary that she turns against you.

• Your database is distributed?
• Do you have any unusual organization of how the database is deployed?
• Him really will need some operation that joins more than one table with the same structure and that the Ids may conflict with?
• Your application is experiencing performance issues in getting a new ID in the database?
• There are several clients in parallel needing to create transactions with voluminous data insertion that having the ID available before accessing the bank would bring some advantage?
• 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 doesn’t 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.

It has more technical reasons:

• Guids are worse for computers and humans to manipulate/view;
• consume too much space in various ways;
• and consequently worsens performance, even if minimally.

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

Answer 3

Advantage of using GUID:

It generates a unique value in each table and each database and this allows easy merge/merge/migrate records between different databases. You can generate your Ids in the application without needing the database, 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’re not careful. Especially in the case of an Asp.Net MVC application, where we have friendly url’s is much simpler terms:

http:\\localhost\Cliente\Detalhes\1234 instead of http:\\localhost\Cliente\Detalhes\031E9502-E283-4F87-9049-CE0E5C76B658

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

Answer 4

According to Microsoft, GUID is only used in very particular applications. This type of data has many disadvantages:

1 - They are 50% slower than bigint, with respect to search (I am not considering joins);
2 - Bad for maintenance;
3 - For int/bigint take up a lot of space.

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

Link Microsoft: https://docs.microsoft.com/pt-br/dotnet/framework/data/adonet/sql/comparing-guid-and-uniqueidentifier-values

Answer 5

I have used an alternative solution. I used a function that generates a random id with letters and numbers with 15 characters. I’ve run tests generating a billion sequences in a loop without repetition. this consumes less space in tables that you know will have fewer records, you can generate an id with less characters, occupying less space. In my scenario I only ask that id not be repeated in the table, and not in the entire bank. guid will never repeat itself, facebook/instagram surely uses guid to identify billions of daily posts.

The main advantage I see in this technique for banks that have to meet distributed systems, and that you can always create primary keys with only 1 column. Composite keys, tend to consume more space and not have the same performance.

Even in tables with parent child relation, in the child table I create a key with the ID and another index field to relate to the parent table.

Link the function that generates a random key. https://showdelphi.com.br/dica-funcao-para-gerar-uma-senha-aleatoria-delphi/

You can also choose a bigint field and generate a Random only with numbers. Random(9999999999) big int allows a monstrous number, generating a high Random is unlikely to be repeated.