Identity Integers Using ASP.Net Identity On MVC 5

ASP.Net MVC 5 introduced a new identity model, ASP.Net Identity.  Brock Allen has a blow-by-blow analysis of the product, and that’s pretty fantastic reading.  What I was really interested in, however, was an understanding of how to change the database tables used in the identity model.

By default, ASP.Net Identity creates a five tables, including dbo.AspNetUsers and dbo.AspNetUserRoles.  Ignoring the “you probably shouldn’t have table names plural because tables describe singular entities” bit, I looked at the way this information is stored and noticed that UserId is stored as NVARCHAR(128) and is a clustered primary key.  Even better, once you create an account, the NVARCHAR(128) actually holds a GUID.

Let’s take a moment to enumerate the problems with this.

1. Define your data types appropriately.  If you are going to store a GUID, create a UNIQUEIDENTIFIER data type.  The reason you want to do this is that a UNIQUEIDENTIFIER takes up 16 bytes, whereas storing the GUID as a string takes up either 72 bytes (36 characters on an NVARCHAR table without compression).  With a large enough user base, this can make a difference.
2. Clustered indexes should be narrow, unique, static, and ever-increasing (NUSE).  GUIDs stored as 72-byte strings are definitely not narrow, nor are they ever-increasing.
3. Entity Framework understands the concept of integers quite well and handles identity integers with no problem.  Dropping back to using strings for these seems a bit string, especially when the AspNetUserClaims table uses an identity integer.  If you’ve got it going for one of the five tables, why not take care of the other four?

I will give Hao Kung and the ASP.Net Identity team credit:  they understood the problem and worked on solutions.  Hao Kung in particular has been very active on forums helping people out, and that deserves respect.

Fortunately, you can change this default behavior.  It’s a little complex, but Tom FitzMacken has a fantastic step-by-step guide on how to use integer values with ASP.Net Identity.  After following these steps, I’m back to being a (relatively) happy camper.