Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is varchar(MAX) always preferable?

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?

like image 220
Elad Benda Avatar asked Dec 14 '11 21:12

Elad Benda


People also ask

Why should we avoid varchar Max?

In varchar(MAX) fields if your data size is shorter than 8000 characters your data is stored in row automatically (therefore the data execution is faster). Over 8000 characters your data is considered to be text and stored out of row, and becoming (somewhat) slower to work with.

Why we use varchar Max?

String values that vary significantly in length and are no longer than 8,000 bytes should be stored in a VARCHAR column. If you have huge strings (over 8,000 bytes), then VARCHAR(MAX) should be used. In order to store VARCHAR columns, the length information along with the data is stored.

Does varchar max waste space?

Even using varchar(max) shouldn't have any impact on storage.

What size should I use for varchar?

The size of the maximum size (m) parameter of a VARCHAR column can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store character strings that are shorter, but not longer, than the m value that you specify.


2 Answers

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.

like image 173
Abe Miessler Avatar answered Sep 16 '22 21:09

Abe Miessler


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).

like image 24
Garrett Vlieger Avatar answered Sep 18 '22 21:09

Garrett Vlieger