As the title says, I wonder, why ASP.NET Identity 2.0 uses a string with a GUID as primary clustered key for the user table. Does this have any advantages to an integer id? I see only the problem, that a GUID isn't the best choice for a clustered index.
Am I missing anything or is a integer still the better choice?
Regarding the use of guid, there is a point of view that promotes using ids without "meaning" in order to completely separate the identifier from the data surrounding it; this id shouldn't be visible from outside the datastore. If we look at some characteristics of a surrogate key, we have the following
- the value is unique system-wide, hence never reused
- the value is system generated
- the value is not manipulable by the user or application
- the value contains no semantic meaning
- the value is not visible to the user or application
- the value is not composed of several values from different domains.
So a guid fits the bill since it is indeed generated by the system and has no relationship to the domain. I think that the use of a guid is mainly a question of trend in this particular way of thinking; however since they are introducing a new mechanism of "extensible primary key" the key can be changed, so you can fallback on an integer for your PK.
Regarding performance i'd point you to this thread where the accepted answer says:
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
- the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
- the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
which completely confirm your impression.