I am frequently getting questions about the major differences between VARCHAR(MAX) and VARCHAR(N) and when to use which data type. So, I am writing this article comparing these two data types and lists down when to use what.
VARCHAR(MAX) vs VARCHAR(N)
| # |
VARCHAR(MAX) |
VARCHAR(N) |
|---|---|---|
| 1 | Introduced in SQL Server 2005 to replace TEXT data type. | |
| 2 | Can store up to 2GB of data. | Can store up to 8,000 bytes of data. |
| 3 | Uses normal data pages for storage until the string size is up to 8,000 bytes. Beyond that, the data is stored outside the row, similar to the old TEXT data type. | Uses normal data pages for storage. |
| 4 | Cannot have regular index. If you try to create index on a varchar(max) column, you will get this error: Msg 1919, Level 16, State 1, Line 1 Column ‘Column_Name’ in table ‘Table_Name’ is of a type that is invalid for use as a key column in an index. |
Can be indexed. |
Recommended Usage
- Use VARCHAR(N):
- When the size of the string data varies significantly.
- When you want to restrict the length of the string.
- When you need to index the column.
- Use VARCHAR(MAX) when the size of the string data might exceed the 8,000 bytes limit.
Next Step
Reference
- About varchar(n) and varchar(max) at Microsoft Docs.