?
Solved

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

Posted on 2009-04-28
4
Medium Priority
?
614 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:glebn
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24254368
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
 
LVL 4

Author Comment

by:glebn
ID: 24254666
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24254750
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
 
LVL 4

Author Closing Comment

by:glebn
ID: 31575638
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question