Is varchar(MAX) always preferable?

Question

Regarding SQL Server, I understand :

var means the memory is lazy allocated, meaning it fits to the data exactly (on insertion).
MAX means there is no size restriction\limitation.

Then, is it always preferable to use MAX when using varchar, as we don't allocate the whole size anyhow?

Should we use a constant size only if there is a constraint we want to enforce on this DB column?

This can be dependant on the database system. Which one do you refer to? — jjmontes, Dec 14 '11 at 21:53
Also, I'd add that the length limit is part of the constraints of the data model and some people likes to very strict at that level too. — jjmontes, Dec 14 '11 at 22:00
Another point: since an index entry in SQL Server can only be a maximum of 900 bytes, you can **never** put an index on a `VARCHAR(MAX)` column ..... not a good thing if you should have one! — marc_s, Dec 14 '11 at 22:01
"Should we use a constant size only if there is a constraint we want to enforce on this DB column?" -- you make it sound as if such columns are the the less common of the two possibilites. My experience tells me the opposite. — onedaywhen, Dec 15 '11 at 09:36
The answer is NO, it is not preferable to use VARCHAR(MAX). See below for more detail. — Shanerk, Jan 25 '17 at 14:48
check this out https://stackoverflow.com/questions/28980502/sql-varcharmax-vs-varcharfix — vicky, Mar 09 '22 at 13:25

score 67 · Accepted Answer · answered Dec 14 '11 at 21:55

There is a very good article on this subject by SO User @Remus Rusanu. Here is a snippit that I've stolen but I suggest you read the whole thing:

The code path that handles the MAX types (varchar, nvarchar and varbinary) is different from the code path that handles their equivalent non-max length types. The non-max types can internally be represented as an ordinary pointer-and-length structure. But the max types cannot be stored internally as a contiguous memory area, since they can possibly grow up to 2Gb. So they have to be represented by a streaming interface, similar to COM’s IStream. This carries over to every operation that involves the max types, including simple assignment and comparison, since these operations are more complicated over a streaming interface. The biggest impact is visible in the code that allocates and assign max-type variables (my first test), but the impact is visible on every operation.

In the article he shows several examples that demonstrate that using varchar(n) typically improves performance.

You can find the entire article here.

Also MAX types prevent online operations, at least up to SQL Server 11. http://rusanu.com/2011/08/05/online-index-operations-for-indexes-containing-lob-columns/ — Remus Rusanu, Dec 14 '11 at 22:27
So what is the maximum value that you can use for `n` with `varchar(n)` to still get the benefits of using `varchar(n)`? — Superdooperhero, Mar 07 '20 at 11:19
"Premature optimization is the root of all evil." --Donald Knuth — allyourcode, Dec 04 '21 at 23:29

score 20 · Answer 2 · answered Dec 14 '11 at 21:55

20

Look here for a good answer:

Should I use varchar(n) or varchar(MAX)?

The short answer is that from a storage perspective it's the same, but from a query optimization perspective, it's better to use varchar(N).

answered Dec 14 '11 at 21:55

Garrett Vlieger

9,354
4
32
44

O.O · Answer 3 · 2011-12-14T22:01:57.527

16

Here's what microsoft recommends:

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 size might exceed 8,000 bytes.

ref

edited Dec 14 '11 at 22:01

answered Dec 14 '11 at 21:54

O.O

11,077
18
94
182

4

I'd guess because stating that it's okay to use VARCHAR(MAX) for everything is wrong? – HardCode Dec 14 '11 at 22:04
1

Your current, edited answer varies drastically from your original answer, whether you intended it or not in your original answer. – HardCode Dec 14 '11 at 22:13
1

The first answer was wrong so I downvoted it. You implied basically that it's fine to use varchar(max) wherever and whenever you want without any consequences. Wrong answers deserve (and receive) downvotes. – JNK Dec 15 '11 at 01:54

score 13 · Answer 4 · answered Jan 29 '14 at 15:14

Just to be quite explicit on this one, the answer is NO. It is always preferable to use varchar(N), and if you know the size will not vary, then char(N). The MAX types do not and cannot support most of the native SQL features so you cannot add indexes, perform joins nor do effective searches on those types. Incidentally, this is one reason why the full-text search capability exists in SQL Server.

In addition, varchar(max) prevents the ability to perform online indexes against the entire table which contains the varchar(max) field. This will significantly impact performance of your system.

Varchar(max) should only ever be used if the size of the field is known to be over 8K. In every other instance, the size must be specified. Failure to do so is poor design and will lead to performance issues on any but the most trivial of systems.

Some references:

Is varchar(MAX) always preferable?

4 Answers4

Linked