C# Tips

C# Tip Article

When not to use nvarchar(max)

Entity Framework has an annotation (StringLength) to set max length of string field. Code-first approach uses this data annotation to set column size in SQL database. For example, the following code limits 255 chars on Title field (and so on its column in DB).

[Table("Sample")]
public class Sample
{
   //....
   [StringLength(255)]
   public string Title { get; set; }   
   //....
}

But many EF samples do not use the annotation and many developers tend to omit the size in many cases. If the StringLength is not added, the default is NVARCHAR(MAX) in SQL Server (let's assume SQL Server here).

So why does it matter?
Generally speaking, MAX types such as NVARCHAR(MAX) are slower than non-MAX types such as (NVARCHAR(255)). But its performance hit is practically minor, you probably do not have to be picky... Other than this general concern, I noticed another interesting aspect - eligibility to create index.

When it matters is the time you need to add an index on the column. For example, you want to search Title from your application and so want to add an index on Title column later to improve search speed. Then that's when you got into trouble.

The thing is you cannot create an index on MAX type columns in SQL Server. Actually, in SQL Server, max size of index key is 900 bytes. This means if you have NVARCHAR(4000) column, the index key will use only first 900 bytes of the column (that is, up to nvarchar(450)). However SQL Server - at least - does not keep from creating index on NVARCHAR(4000) column.

But you will never create any index against NVARCHAR(MAX) column at all... So if you need index on the column, make sure you specify real max length of the column correctly.

View More