multiple nvarchar(450) fields unsuitable for clustered indexes

[Triwaters]

The worst offender is dbo.AspNetUserLogins, with three (3) nvarchar(450) fields making up the primary key. SQL Server balks at this and it's a design flaw that contents that can legally fit in these fields could (together) make the record unsavable, due to a violation of the 900-byte maximum length for clustered indexes.

I can imagine this is all rather baked into the system at this point, but it's a design flaw in two ways:

1. it can lead to legal record data-wise not being saved due to index violations, and
2. For larger sets of data, it's horribly inefficient

All that having been said, I suppose in practice "this will never be a problem" with regard to (1), since the fields in practice are populated with relatively small guids, and "this will never be a problem" with regard to (2) because most systems don't have more than a few thousand logins, and often fewer than 100, I suppose.

The other two tables affected by this (as in, SQL Server throws a fit when you try to create the index) are:
dbo.AspNetUserRoles (requesting 1,800 bytes in index space per record)
dbo.AspNetUserTokens (requesting 1,800 bytes in index space per record)

My recommended solution is that fields be reduced to more ordinary sizes, going forward, and primary keys be defaulted to integers.

[blowdart]

Discussion results - new templates could limit the string lengths to a more reasonable length, so this wouldn't appear in new projects, but the potential for data loss in older projects is a concern. Primary keys would never swap to integers.

@ajcvickers to discuss with @HaoK

[Triwaters]

Yes, I never figured the switch to integers would happen, and that's just personal preference for me. Glad you're looking into fixing the field lengths, however!

[Triwaters]

One more stupid question -- I can understand varchar (for legacy compatibility) or (preferably, for those who want Guids) uniqueidentifier... but why nvarchar to store a Guid? Or are there other scenarios being supported that require this kind of flexibility? I'm having a hard time picturing that, but maybe that's due to my lack of imagination.

[blowdart]

Closed as we think we've fixed it in later versions.