glebn
asked on
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.
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.
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
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.