nvarcharmax

nvarchar(max) is a variable-length Unicode string data type in Microsoft SQL Server. It can store up to 2,147,483,647 bytes (2 GB) of data, which corresponds to up to 1,073,741,823 Unicode characters. It serves as the Unicode counterpart to varchar(max) and is used when text data may exceed the 4000-character limit of nvarchar(n).

The data is stored using UTF-16 encoding. As a variable-length type, values up to 8 KB may be stored in-row, while larger values can be stored off-row with pointers to LOB storage. Collation settings govern comparisons and sorting, even for Unicode data, so locale-aware behavior applies to nvarchar(max) as well.

nvarchar(max) is suitable for long text fields such as descriptions, notes, XML fragments, or user-generated content. It cannot be used directly in primary keys or unique constraints, and it generally cannot be indexed like a standard nvarchar(n) column. For quick lookups, you can use full-text indexing or index a persisted computed column that exposes a prefix of the data. Large values may impact performance, so design choices should balance storage and query patterns.

String functions such as SUBSTRING, LEFT, RIGHT, REPLACE, and concatenation work with nvarchar(max). LEN returns the number of characters (excluding trailing spaces), while DATALENGTH returns the number of bytes. When comparing values, the chosen collation affects results, including sorting and equality checks.

Related types and best practices: For shorter text, nvarchar(n) offers more predictable storage. nvarchar(max) should be preferred over the deprecated ntext type in modern SQL Server versions, as ntext is being phased out. Use appropriate indexing strategies and consider full-text search for efficient querying of large text fields.