varcharmax

Varchar(max) is a variable-length non-Unicode string data type in Microsoft SQL Server that can store up to 2,147,483,647 characters, effectively up to 2 GB of data. It was introduced with SQL Server 2005 to replace the deprecated text and ntext types and to provide a flexible, single data type for large strings.

Storage and capacity: The value can occupy up to the full 2 GB of storage. In-row storage is used for small values, but if the row would exceed the in-row limit, the data is stored off-row with a pointer in the row. This behavior helps manage large values within normal row structures while still allowing very large strings to be stored.

Usage: Varchar(max) can be used for table columns, variables, and parameters. It supports most ordinary string operations, including concatenation, comparisons, and many built-in functions. It is not Unicode; for Unicode data, nvarchar(max) should be used. Varchar(max) values can be passed to and from applications and can be converted to other varchar lengths as needed.

Limitations and considerations: You cannot directly create a standard index on a varchar(max) column; indexing is typically achieved via persisted computed columns or by using full-text indexing where appropriate. Large values can affect query performance and memory usage, so design choices often involve streaming or paging results, and avoiding operations that scan very large values unnecessarily. Varchar(max) is preferred over the older text types, but developers should still consider appropriate data modeling and performance implications when storing very large strings.

See also: nvarchar(max), varchar(n), text and ntext (deprecated), varbinary(max).