Asp.net identity: Why the index is on GUID (nvarchar)? Any benefit/drawback to use int?

Question

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

Answer 1

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?