8

I'm trying to understand why the new ASP.NET Identity tables stopped using Guid (uniqueidentifier type) as keys - instead it is now using nvarchar(128) but still keep a Guid as a string...

Isn't it a huge waste? (uniqueidentifier is just 2 integers vs the whole Guid as a 36 character string)

I'm suspecting that Entity Framework might be responsible for this...

Is it safe to change back to uniqueidentifier keys?

Can anyone tell me what are the benefits of using 36 character strings?

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
Yovav
  • 2,557
  • 2
  • 32
  • 53
  • `uniqueidentifier` is 16 bytes, not 8. And the size of the string doesn't depend on its length, so it's actually always the whole 256 bytes (nvarchar has two bytes per "character"), and the same for any indices and associations. I guess the goal is flexibility, but it seems like a poor trade-off to me... – Luaan Aug 21 '15 at 07:30
  • Thanks, for some reason I had Guid = 2 int stuck in my head... but I think the new SQL server only allocate the number of nvarchar characters that are being used and not spending all 128 if only 36 are used - but still a big waste... – Yovav Aug 21 '15 at 08:31
  • I know it does that with `nvarchar(max)` (and even then there's a minimum length IIRC) and similar, I haven't explored how exactly it works otherwise. It's possible they fixed all those issues, but then wouldn't we simply be using `nvarchar(max)` everywhere? :D – Luaan Aug 21 '15 at 08:37
  • I have found another discussion similar to this: http://stackoverflow.com/q/23891446/809357 - some good answers there. – trailmax Aug 21 '15 at 08:44

1 Answers1

2

Identity is built to work on multiple storage platforms and not every storage platform has Guid as a supported storage type.

You can change the default string pkey into Guid, but that involves some work on your C# models. Or you can change the pkey into an int - whatever you like. Just be aware that there is a huge debate about which is better.

Community
  • 1
  • 1
trailmax
  • 34,305
  • 22
  • 140
  • 234
  • Thanks. I wonder if there are more legitimate reasons to use 128 nvarchar other than supporting other SQL platforms... I used to be a big fan of Entity Framework but it seems like they are moving too fast and not closing all ends right... this kind of performance hit is unacceptable... – Yovav Aug 21 '15 at 08:36
  • Well, EF could emulate GUIDs on those platforms, but why do it like that everywhere? It sounds like one of the things that should be handled by the abstraction EF provides. – Luaan Aug 21 '15 at 08:38
  • @Luaan What do you mean "do it like that everywhere"? If you make a `Guid` property in your storage class and tell EF to work with it, it will create a `UNIQUEIDENTIFIER` field in SQL Server. Storing Guid as a string is a choice of Identity framework. – trailmax Aug 21 '15 at 08:40
  • @trailmax Oh, that was a response to Yovav's comment. E.g. if it were to support DB engines that do not support guids, that should be EF's job, not Identity's. – Luaan Aug 21 '15 at 08:43
  • The EF provides are immature... try to store a byte field that should be converted to a tinyint but EF converts it to an int – Yovav Aug 21 '15 at 08:53
  • @Yovav that again goes back to EF != SQL Server. EF also works with other databases where `tinyint` might not be available. If you need these kind of micro-optimisations (what is the use case?), use Dapper or PetaPoco or other micro ORM where you are entirely in control of the DB. – trailmax Aug 21 '15 at 08:57
  • But U see - that's my point, because if EF is using SQL Server provider it is expected that the provider should handle all these platform specific issues... – Yovav Aug 21 '15 at 08:59
  • @Yovav yes, you have a point here. That does not help with your original question though - default identity template specifically tells EF to use `String` and then generate a Guid to put as a value for the pkey. This is not a decision made by EF - this is by whoever done Identity default template. – trailmax Aug 21 '15 at 09:11