I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by necessity. I am reading about DataAttributes to further describe in C# what I want the database to do; and my question is: what penalty will I be eating by having these nvarchar(max) strings in my table (see example below)?

There are several columns in this particular table; in C# they are defined as such:

    public int ID { get; set; }
    public string Name { get; set; }
    public string Message { get; set; }
    public string Source { get; set; }
    public DateTime Generated { get; set; }
    public DateTime Written { get; set; }

I expect to query and/or sort based on Name, Source, Generated, and Written. I expect Name and Source to be in the 0-50 character length, occasionally up to 150. I expect this table to start pretty small (<100k rows), but grow significantly over time (>1m rows). Obviously message could be small or large, and will probably not be queried against.

What I want to know, is there a performance hit for my Name and Source columns being defined as nvarchar(max) when I never expect them to be larger than 150 characters?

  • 5
    Looks like you need to apply either [MaxLength] or [StringLength] attributes. Some additional possible negative factors of too wide columns are mentioned in @PaulWhite's answer here – Martin Smith Aug 20 '13 at 21:57
  • 3
    YES, using varchar(max) everywhere hurts your performance - don't do it! Use appropriate datatypes - use varchar(max) ONLY if you REALLY need more than 8000 characters! (I have never seen a person's name or e-mail be that long!) - See What's the Point of Using VARCHAR(n) Anymore? for more info – marc_s Aug 21 '13 at 7:44
  • @marc_s Great link. I know it does hurt performance. When I define my own tables with SQL I use varchar(n). My question was more about how much it hurts performance(though I realize as posted that wasn't explicitly clear). – Nate Aug 21 '13 at 14:30
  • Working link - What's the Point of Using VARCHAR(n) Anymore? – GFoley83 Feb 27 '18 at 18:44

Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O. Smaller items will be stored in-row. There are options that control this behaviour - see this MSDN article for more details.

If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on processing the data type but this is likely to be minor.

However, leaving nvarchar (max) columns lying around the database where they are not needed is rather poor form. It does have some performance overhead and often data sizes are quite helpful in understanding a data table - for example, a varchar column 50 or 100 chars wide is likely to be a description or a free-text field where one that's (say) 10-20 chars ling is likely to be a code. You would be surprised how much meaning that one often has to infer from a database through assumptions like this.

Working in data warehousing, as often as not on poorly supported or documented legacy systems, having a database schema that's easy to understand is quite valuable. If you think of the database as the application's legacy, try to be nice to the people who are going to inherit it from you.

| improve this answer | |

Although this doesn't answer your specific question, it may preclude you from needing to ask the question in the first place: It's possible to set a length on your string variables in your C# model class, which will cause Entity Framework to generate SQL that uses a fixed-length nvarchar type (e.g. nvarchar(50)), instead of nvarchar(max).

For example, instead of:

public string Name { get; set; }

You can use:

public string Name { get; set; }

You can also force the type to be varchar instead of nvarchar, if desired, as follows:

[Column(TypeName = "VARCHAR")]
public string Name { get; set; }

Source: https://stackoverflow.com/questions/7341783/entity-framework-data-annotations-set-stringlength-varchar/7341920

| improve this answer | |
  • 3
    It took finding this answer to let me figure out that EF Core supports setting type and length at the same time (varchar(50)), but EF 6 requires what's in this answer. – Sinjai Jan 25 '19 at 22:19

Indexing the biggest concern. From BOL:

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

If you can't index properly, you are going to have slow queries. And from a data integrity perspective, having nvarchar(max) will allow more bad data to be put in a field than specifying the limit would be.

| improve this answer | |

Yes, the default EF behavior in mapping string to nvarchar(max) is not good. In EF 6 you can add your own custom convention to override this behavior with your own preferred default mapping.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        .Configure(s => s.HasMaxLength(200).HasColumnType("varchar") );


Overriding OnModelCreating as above will change the default mapping for all strings to varchar(200).

| improve this answer | |
  • 1
    This does not work in EF Core 1.0 – Shittu Joseph Olugbenga Aug 15 '16 at 11:38
  • the default EF behavior in mapping string to nvarchar(max) is not good this seems to be your generalized opinion. can you explain why this is not good? Or, you think, EF is not a framework for business-applications where you need to work with multiple languages? Because that's the desired column-type for handling multiple languages on the database. – Matthias Burger Feb 9 '17 at 13:21
  • 1
    @MatthiasBurger nvarchar(max) is horrible for performance, especially in a replicated environment. It's not a generalized opinion, it is a well known fact. – user2966445 May 23 '17 at 21:11
  • @user2966445 sorry, I think there was a misunderstanding :) sure, max is horrible. But if you want to handle multiple languages (and their different charactersets) you need to use nvarchar am I wrong? – Matthias Burger May 24 '17 at 7:53
  • @MatthiasBurger That's correct, use nvarchar for different charactersets, but this entire post is about performance and field lengths, not the use of nvarchar vs. varchar. – user2966445 May 24 '17 at 20:31

Your Answer

