What are the downsides of using varchar(max) vs. varchar(n)?

I'm creating an application in which the vast majority of values for a text column will be under 1000 characters. However, a small number of values may have rather large values (~15,000). The simplest thing to do would be to define my column using varchar(max).

Now my question. Is their a downside to using varchar(max) vs. varchar(1000)? If not, why not always use varchar(max) when there is any doubt about the size needed for a column? Again, are their downsides either on the storage required or time needed to process queries, build indexes, etc.

My application is a .NET 2 ASP app using SQL Server 2005.
LVL 4
glebnAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
1. Could you explain in a little more detail what the potential negative implications of storing the data outside the rest of the row's page?

before sql 2005, the table design would have had to be changed, by splitting the table into 2 tables, and eventually also with the value to be split into different rows (if you wanted to avoid the TEXT data type).

the downside is the same: sql server has to read another data block from disk to get the records' data.
so, this requests proper code from the developer:
a/ do not use SELECT * to fetch the table's data, unless you REALLY REALLY need all the columns data, to avoid that sql server has to read that/those additional data blocks

b/ eventually split the table still into 2 tables, to avoid that this "out-of-the-page" data is fetched "by error" too easily. this will be transparent to the application if data access is done via stored procedures, for example


2. Could you also answer the question "Why not always use varchar(max) when uncertain of max data size?"
the designer should always set a certain fixed size for anything that is "name" in any form.
for a field like "comments/remarks", it shall be varchar(max), but again, that might be worth a design change having eventually a short_comment (varchar(200), and a dedicated long_comments table (which might even be shared among the application) ...

reason: see 1)a/ ... performance.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as your real value will go up to 15K, you will HAVE to use VARCHAR(MAX), as VARCHAR(n) can only defined up to VARCHAR(8000)

the difference, anyhow, is that as from a certain size, which depends on the data size and the table columns total size, the varchar data of the column will be stored outside the rest of the rows's page.
0
 
glebnAuthor Commented:
angelIII,

Thanks for the quick response!

Actually, I don't have to set it to varchar(max) as I have a few workarounds I could use to deal with the few large values while keeping the column size at varchar(1000). However, using varchar(max) is certainly easiest. I just want to be clear on the potential downsides of using varchar(max).

Just to be clear, I understand you to be saying that the difference between varchar(n) (where n is below a certain size) and varchar(max) is that when using varchar(max)  "the varchar data of the column will be stored outside the rest of the rows's page".

Two follow up questions:

1. Could you explain in a little more detail what the potential negative implications of storing the data outside the rest of the row's page?

2. Could you also answer the question "Why not always use varchar(max) when uncertain of max data size?"

Thanks!
0
 
glebnAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.