3

When I generate MVC.NET using Visual Studio 2017, it generates a few tables AspNetUsers, AspNetRoles, etc...

If you look at the Ids that have been generated, all are in nvarchar(128) and the value is just GUID.

My immediate reaction is to change it to int, but I thought Microsoft must have a very good reason to use GUID instead of int. But doing google search, I couldn't find the answer.

Now, I understand why it stored all ids as nvarchar(128) which is because not all database has field type GUID. But I couldn't find out the benefit of using GUID and int.

Further search the internet, I found this article: GUIDs as PRIMARY KEYs and/or the clustering key. Now, it sounds like GUID can be a bad choice.

I am on the early stage, so it is still time for me to change from GUID to int. But is there any real drawback (and/or benefit) by using int instead of GUID?

Thanks for any help...

Tetsuya Yamamoto
  • 24,297
  • 8
  • 39
  • 61
Sam
  • 1,826
  • 26
  • 58
  • Asp.net identity provider classes interact with identity database to facilitate the identity management and authentication out of the box. Why they used GUID for primaeiy key column, only MS can answer that question. Changing it to INT is possible but not worth it. If you have few thousand users in the database then performance actually doesn't have much change between GUID and INT. – Chetan Jan 27 '19 at 01:49
  • You can read [this](https://learn.microsoft.com/en-us/aspnet/identity/overview/extensibility/change-primary-key-for-users-in-aspnet-identity) to get an idea about how much work it is involved in changing the schema of identity database. – Chetan Jan 27 '19 at 01:51
  • 1
    Possible duplicate of [Why is ASP.NET Identity 2.0 using a GUID/string as user id?](https://stackoverflow.com/questions/23891446/why-is-asp-net-identity-2-0-using-a-guid-string-as-user-id) – HazardousGlitch Jan 27 '19 at 02:25

1 Answers1

3

Lot of discussion has already been made on this issue on the internet. This is really a never ending debate/topic as both int/long and GUID as primary key has pros and cons.

GUID vs INT Debate is a fantastic article on this topic.

You can also take a look on Why is ASP.NET Identity 2.0 using a GUID/string as user id?

Apart from these articles discussion I can propose you few things:

  1. By default SQL Server primary is clustering key, unless you specifically tell it not to, which will cause bad performance in case of GUID primary key. If you take this into consideration then don't choose GUID as primary key.

  2. If you think your primary key has no special meaning without being a unique identifier then choose GUID as Primary Key.

  3. If you need meaningful and readable primary key then choose int/long as primary key.

If you choose GUID as your primary then you can take a look on What are the best practices for using a GUID as a primary key, specifically regarding performance?

TanvirArjel
  • 30,049
  • 14
  • 78
  • 114
  • This is exactly what I am looking for. Thank you! I've decided to use int after reading the accepted answer written by marc_s – Sam Jan 28 '19 at 01:47