C#•3y ago

The maximum length of the index

I have such a model:

[Index(nameof(Name), IsUnique = true)]
public class Variables
{
    public int Id { get; set; }
    [Required]
    public string Name { get; set; } = string.Empty; 
    public string? Description { get; set; } = string.Empty;
    public ICollection<Translate> Translates { get; set; } = null!;
}

[Index(nameof(Name), IsUnique = true)]
public class Variables
{
    public int Id { get; set; }
    [Required]
    public string Name { get; set; } = string.Empty; 
    public string? Description { get; set; } = string.Empty;
    public ICollection<Translate> Translates { get; set; } = null!;
}

EF core created this table for me:

CREATE TABLE [Stand].[Culture.Variables] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (450) NOT NULL,
    [Description] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_Culture.Variables] PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Culture.Variables_Name]
    ON [Stand].[Culture.Variables]([Name] ASC);

CREATE TABLE [Stand].[Culture.Variables] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (450) NOT NULL,
    [Description] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_Culture.Variables] PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Culture.Variables_Name]
    ON [Stand].[Culture.Variables]([Name] ASC);

[Name]        NVARCHAR (450) NOT NULL,

[Name]        NVARCHAR (450) NOT NULL,

Here we have a maximum length of 450 characters. Can I safely change this to MAX?

4 Replies

AlerinOP•3y ago

dancepanda42•3y ago

Yes, but why should the name be longer than 450 characters?

AlerinOP•3y ago

it is a string / sentence of the translation in English. My system works like this: @ Culture.Translate ("Hello {user}", User.Name);

Yawnder•3y ago

@Alerin The problem won't be in EF specifically. Depending on the actual database engine, they have different restrictions. For example, MSSQL wouldn't allow you to have an index on a NVARCHAR(MAX). "900 bytes for a clustered index. 1,700 bytes for a nonclustered index. For SQL Server 2014 (12.x) and earlier, all versions supported 900 bytes for all index types." https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16

Gaming

Programming

The maximum length of the index