Why does ASP.Net Identity 2 store in UserId in a NVARCHAR?

Question

The default stores the UserId in a NVARCHAR(128). Can someone explain why it does not store it in a NVARCHAR? From what I see the GUID is nothing more than numbers, letters and dashes. Does it need a NVARCHAR to be stored correctly?

I believe MS has addressed this publicly but I do not have the link — IrishChieftain, Jul 04 '14 at 04:42
I don't use varchar(15) for a phone number. I use nvarchar(15). In fact I started storing all string type values as NVARCHAR. Let technology work for you. Why use VARCHAR(10) WHEN you can use NVARCHAR(10), the latter will strip paddings that plagued the VARCHAR data type. And at the worst varchars can cause inconsistencies when querying values imported from heterogeneous datasets. — Ross Bush, Jul 04 '14 at 04:45
Can you explain more about the differences between NVARCHAR and VARCHAR? I was under the impression that the N meant it could hold different character sets. — , Jul 04 '14 at 04:49
@Lrb - Can you confirm you are referring to SQL Server data types ? — , Jul 04 '14 at 04:55
@lrb: because with using a `NVARCHAR` type on something like a phone number (which will never have a real Unicode letter), you're just basically wasting 15 bytes for every single entry in your table .... — marc_s, Jul 04 '14 at 05:01
@Marilou: `NVARCHAR` is the **Unicode** enabled string type - needs 2 bytes of storage for each and every single character. Good if you really need it (to support e.g. Hebrew, Arabic, Far-East Asian languages) - a total waste of storage when you store nothing but numbers in it ... — marc_s, Jul 04 '14 at 05:02
Yes, day to day I use NVARCHAR. There could be situations when the varchar datatype is warranted. I honestly believe you can do anything with a nvarchar that you can do with varchar — Ross Bush, Jul 04 '14 at 05:02
@lrb - My understanding was similar to that of marc_s but I posted here for some clarification. Can you explain in a bit more detail how storing the phone number in a NVARCHAR helps and what is the advantage of using NVARCHAR over VARCHAR for this. Thanks very much. — , Jul 04 '14 at 05:15
@marilou I think the problem here is the sane way of creating new assumptions. A Phone could be 15 digits but up to 21. I store that in a NVARCHAR(45)...this is my default. — Ross Bush, Jul 04 '14 at 05:20
@marilou However a phone number or any fields in this case goes to 31 digits. In this case your Database field is still in line with that change. — Ross Bush, Jul 04 '14 at 05:26
@marilou I understand the whole Unicode/not thing but is the tradeoff really worth what you are speculating it to be? — Ross Bush, Jul 04 '14 at 05:28
What about Chinese phone numbers? Do they use regular digits? Or do they require `NVARCHAR`? Other countries with different characters apply too ofcause. — Alxandr, Jul 04 '14 at 07:20
@Alxandr - For this question I am asking "why does Identity 2 store GUIDS in a nvarchar and not a varchar. — , Jul 04 '14 at 08:25
@Anne Right. I lost track of that when people started talking about storing phone numbers in NVARCHAR. — Alxandr, Jul 04 '14 at 12:16
Guids are also not 128 characters long? Could one not just change the sql data type to varchar(36). On big tables this will reduce the index size? — Zapnologica, May 21 '17 at 09:09

score 1 · Accepted Answer · edited May 23 '17 at 11:47

The data type usage is an implementation detail of the Entity Framework implementation. Other connectors (such as the one for MongoDB) use other data types. You could write your own implementation for that, but since version 2 of Identity you can use other types for your IDs (no need to use VARCHARs or strings). To get this work you have to implement some additional classes which derive from some framework classes.

For an implementation example have a look at the answer from James in this thread. With this code snippet you simply have to change the generic type from int to Guid to get Guids as the data type for the IDs.

Why does ASP.Net Identity 2 store in UserId in a NVARCHAR?

1 Answers1