Warning maximum key length for a clustered index

[thdotnet]

I'm running SQL script to create the aspnet identity tables, but I'm getting the following warnings on Management Studio:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserLogins' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserTokens' has maximum length of 2700 bytes. For some combination of large values, the insert/update operation will fail.

Is that right? Is there any fix for that?

[blowdart]

What sql script? There's no sql script in ASP.NET Core identity. Do you perhaps mean the one in the .NET framework? Or is this a migration script?

[thdotnet]

My bad, It's missing some important details. I want to use Identity without the Code First Approach. So what I did: I've created a sample app using the code first approach and let it creates the identity tables. Using SQL Management studio, I've generated the script from the previous step in order to create the tables on my real app (the one that does not use Code First).

It generates the errors described in the original description.

[jmatheti]

@thdotnet do you mind elaborating on the missing important details?
I have come across this issue today. Though it is a code first approach. As a part of our deployment plan, we are exporting the scripts from the migrations into a file and handing it over to DB admin so this way we can prevent the application db access user will have low permissions.

[ajcvickers]

I will investigate this.

[Wesam18]

I am experiencing the same problem

[thdotnet]

@Janidbest I already did that:

So what I did: I've created a sample app using the code first approach and let it creates the identity tables. Using SQL Management studio, I've generated the script from the previous step in order to create the tables on my real app (the one that does not use Code First).

It generates the errors described in the original description.

@Wesam18 is facing the same problem

[Triwaters]

I noted this problem in my own work and got curious enough to search out this conversation.

I'm new to ASP.NET Identity, but not new to SQL Server. The three errors noted (all of which I see every time I propagate tables, etc. to a new database) are being thrown because ASP.NET Identity is breaking SQL Server rules. As SQL Server keeps patiently explaining every time one builds the ASP.Net Identity tables in their databases, a clustered index (usually that's your primary key on a table) cannot exceed 900 bytes.

That doesn't mean you can't create the index. What it means is that you may find yourself creating a legitimate and otherwise legal record that SQL Server nevertheless cannot save into the table.

Meaning...

The table design on AspNetUserLogins prescribes a primary key that is a composite of two fields: LoginProvider and ProviderKey. Both fields are nvarchar(450). That's two-bytes per character, and therefore one would think that it'd be possible to provide a full 450 unicode value in each field without breaking table rules for those fields....

...Except that when the record goes to save, the clustered index finds itself indexing two unicode fields of 450 chars apiece ... and craps out. That's 450x2x2 = 1,800 bytes. No can do. The index cannot hold that much data for a single record. You will not be able to save the record.

In practice? I'm betting this never happens. As I said, I am new to ASP.Net Identity, but I'd bet high odds these fields are usually quite short. So in practice, this is probably a non-problem.

But in principle, it's a pretty silly situation. Why has it not been remedied with more reasonable limits on field sizes?

I'm also wondering if the offending fields used to be varchar() and not nvarchar(). The problem goes away if they were varchar() fields--for two tables, at least. For AspNetUserTokens, even that wouldn't fix the problem. It's got a primary key that is a composite of three (3) nvarchar(450) fields. That's 2,700 bytes.

As a final note, given the small size of these tables, I guess again in practice there are no concerns. In principal, however, using multiple nvarchar(450) fields as primary key fields is indicative of design flaws that would be fatal with larger tables with substantially more records. We're apparently now stuck with a design that makes primary keys out of description fields. What else do you call fields nearing 500 chars in length?

Bad. This situation reminds me of Azure object names that we final users see but are not allowed to change. Same problem. IMHO in both cases the problems would go away if primary keys (composite or otherwise) were comprised of integers, but that topic for another day.

[Triwaters]

Not being one to leave "problems waiting to happen" (however remote) in my databases, not least because they usually surface at the wrong time of day or while I'm on vacation... I have been digging into how to improve the situation (see my prior comment).

I've some outstanding resources out there that helped me over the hurdles. In case anyone else is interested:
https://docs.microsoft.com/en-us/aspnet/core/security/authentication/identity-primary-key-configuration?tabs=aspnetcore2x
https://www.captechconsulting.com/blogs/Customizing-the-ASPNET-Identity-Data-Model-with-the-Entity-Framework-Fluent-API--Part-1
https://medium.com/@goodealsnow/asp-net-core-identity-3-0-6018fc151b4
and if you're really motivated:
https://www.youtube.com/watch?v=RyActxOsnsg
This last youtube video convinced me that I might as well own the entire setup. Problem solved, and I'm now well-placed to customize away.

[HaoK]

This should be now fixed in the latest templates initial migration in 2.1

[Eilon]

@HaoK can this bug be closed?

[AndyMDoyle]

Out of the box with 2.1.2 we are still seeing the same warnings based on the schema generated with the initial migration.

Removing the initial migration from the template and recreating also results in a schema that throws up these 3 warnings.

Are we missing something?

[Eilon]

Hi, it looks like you are posting on a closed issue/PR/commit!

We're very likely to lose track of your bug/feedback/question unless you:

1. Open a new issue
2. Explain very clearly what you need help with
3. If you think you have found a bug, include detailed repro steps so that we can investigate the problem.

[nanodrive]

I updated the sdk to 2.1.302 and created a 2.1 web application in visual studio 2017. The number of errors is down to 2 now.

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserTokens' has maximum length of 1412 bytes. For some combination of large values, the insert/update operation will fail.

It's an improvement from 3 errors, but it still needs some more work.

[coolmikkel]

I updated the sdk to 2.1.302 and created a 2.1 web application in visual studio 2017. The number of errors is down to 2 now.

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserTokens' has maximum length of 1412 bytes. For some combination of large values, the insert/update operation will fail.

It's an improvement from 3 errors, but it still needs some more work.

@nanodrive any news with the last 2 warnings?

I only have the warning regarding the index on AspNetUsersTokens,
Running 2.1.4 sdk.

[WellspringCS]

Per my prior (too verbose, sorry) comments, this is easily remedied with sane field sizes.

I ended up switching to integers (in hindsight I wonder if that was wise), but the out-of-the-box guid-based keys only take up... what... 16 bytes? The tables SQL Server is complaining about contain fields ostensibly for guids that are defined as nvarchar(450).

If the problem's still present, then at least one of those fields is still ginormous, to literally no one's benefit.

[coolmikkel]

@WellspringCS I do not understand what you are explaining.
Did you change "nvarchar(450)" to "int"?
How do that work? How does a guid gets to be saved in an "int" column?

[WellspringCS]

My bad for not looking back at how I solved the problem last year. On that particular table, I did this:

I'm pretty sure in my original solution I had to change UserId from guid to int, and similar changes in several places. But I see you have it as int there, too. Is that the setup out of the box, now?

As you can see, I didn't have much patience for ginormous field sizes. I believe field sizes should tell people what is expected to go into them. In hindsight I wonder if varchar(100) might be too small for a token! (Not an issues for me as I'm not using this table.)

That said... Name and LoginProvider should not require 450 characters. They should be storing a name, not the Library of Congress. By the same token (pun intended), varchar(max) seems like overkill for the token field (Value). I suspect tokens can be quite large, however, so here I confess ignorance.

[ghost]

how was EF able to create the table with nvarchar(450), nvarchar(128), nvarchar(128) clustered primary key? I want to do this on my table as well.