Why Not Use Varchar(Max)

Why not use varchar(max)?

My answer to this, isn't about the usage of Max, as much as it is about the reason for VARCHAR(max) vs TEXT.

In my book; first of all, Unless you can be absolutely certain that you'll never encode anything but english text and people won't refer to names of foreign locations, then you should use NVARCHAR or NTEXT.

Secondly, it's what the fields allow you to do.

TEXT is hard to update in comparison to VARCHAR, but you get the advantage of Full Text Indexing and lots of clever things.

On the other hand, VARCHAR(MAX) has some ambiguity, if the size of the cell is < 8000 chars, it will be treated as Row data. If it's greater, it will be treated as a LOB for storage purposes.
Because you can't know this without querying RBAR, this may have optimization strategies for places where you need to be sure about your data and how many reads it costs.

Otherwise, if your usage is relatively mundane and you don't expect to have problems with the size of data (IE you're using .Net and therefore don't have to be concerned about the size of your string/char* objects) then using VARCHAR(max) is fine.

Is varchar(MAX) always preferable?

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.

sql varchar(max) vs varchar(fix)

MSDN

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

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is 8000. If the length is greater than 8000, you have to use the MAX specifier as the length. If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):

The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

UPDATE :-
I found a link which I would like to share:-

Here

There is not much performance difference between Varchar[(n)] and Varchar(Max). Varchar[(n)] provides better performance results compared to Varchar(Max). If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then using this Varchar[(n)] data type provides better performance compared to Varchar(Max).Example: When I ran the below script by changing the variable @FirstName type to Varchar(Max) then for 1 million assignments it is consistently taking double time than when we used data type as Varchar(50) for variable @FirstName.

DECLARE @FirstName VARCHAR(50), @COUNT INT=0, @StartTime DATETIME = GETDATE()
WHILE(@COUNT < 1000000)
BEGIN
SELECT @FirstName = 'Suraj', @COUNT = @COUNT +1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 

why varchar(max) is not storing data more than 8000 charaters

You are truncating the data to 8000 bytes before inserting it. To create a value longer than 8000 characters you must use varchar(max) data type, whereas the type of 'a' is just varchar.

create table test(name varchar(max))
insert into test
values(replicate(CAST('a' AS varchar(MAX)), 100000));

select len(name) from test;

Are there any disadvantages to always using nvarchar(MAX)?

Same question was asked on MSDN Forums:

• Varchar(max) vs Varchar(255)

From the original post (much more information there):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

Are there disadvantages to using VARCHAR(MAX) in a table?

Sounds to me like you plan to use the varchar(MAX) data type for its intended purpose.

When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.

For further reading, check out Books Online: char and varchar

Is Varchar(Max) take much space than Varchar(N) if text length is fixed?

Varchar(MAX) & Varchar(n) reserve fix space for same text store in Column.
I have two table tblMaxData & tblNData and both table have single column with VARCGAR(MAX) & VARCHAR(N). Column have 'Hello' Value in Both Table. After then i was checked space , both column has same occupied Space.

Using varchar(MAX) vs TEXT on SQL Server

The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

If you are searching large amounts of text and performance is key then you should use a Full Text Index.

LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

---

Related Topics