Document trade-offs in string store-type mappings

[nicholasyin]

For example, is it a good idea, in terms of performance concerns, to use the default convention and let all string properties be mapped to nvarchar(max) columns, or it's better to set a fixed length (e.g. 500) globally to all string properties?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 8d6f6f7b-b8d3-df80-33b7-46b0b98acd29
• Version Independent ID: 1a74a6d8-0ad4-9a22-d62e-1a2bab8b80b1
• Content: Maximum Length - EF Core
• Content Source: entity-framework/core/modeling/max-length.md
• Product: entity-framework
• GitHub Login: @rowanmiller
• Microsoft Alias: divega

[steven-tomlinson]

I think this really more of an database schema design decision that should be driven by the application requirements.
At any rate here is some general guidance specifically for MS SQL Server that may help to determine the best case for your particular situation.
If you use char or varchar, we recommend to:

Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.
If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

[nicholasyin]

Maybe it's more clear to ask this way: would there be any harm to use the defaults, which is nvarchar(max) for all string properties, in MS SQL Server? Would it be better to specify a default length for string properties in general?

[xenophilios]

I would like to second this question by nicholasyin, which has been unanswered for almost a year, and rephrase it to make clear why I think it is an important issue. Let's say that we have a Person table in the database and a field for FirstName. In practice, 32 characters should be plenty for that. So we could give the field a data type of nvarchar(32). The question is whether using nvarchar(max) would waste memory or cause any performance issues. If it would not do either of those things, there would be no need to take the time to set specific values of n in nvarchar(n). Some official guidance on this question would be greatly appreciated.