473,394 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

will I burn in hell for using all VARCHAR(MAX)?

so I keep optimizing my fields down to the minimum character length
necessary i.e., varchar(15), then I find out a month later its gotta
get bigger, then a few months later, bigger again, etc. Nowadays on
sql server 2005 and on, how bad is it really to use varchar(max)? Is
there really a big performance or storage hit or is it negligible?

-Mike

Mar 19 '07 #1
6 12860
mike wrote:
so I keep optimizing my fields down to the minimum character length
necessary i.e., varchar(15), then I find out a month later its gotta
get bigger, then a few months later, bigger again, etc. Nowadays on
sql server 2005 and on, how bad is it really to use varchar(max)? Is
there really a big performance or storage hit or is it negligible?

-Mike
Don't be lazy. There are several reasons for not doing this in the
comments for this blog article:
http://sqljunkies.com/WebLog/simons/...rchar_max.aspx
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Mar 19 '07 #2

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:ug**************@TK2MSFTNGP03.phx.gbl...
mike wrote:
so I keep optimizing my fields down to the minimum character length
necessary i.e., varchar(15), then I find out a month later its gotta
get bigger, then a few months later, bigger again, etc. Nowadays on
sql server 2005 and on, how bad is it really to use varchar(max)? Is
there really a big performance or storage hit or is it negligible?

-Mike

Don't be lazy. There are several reasons for not doing this in the
comments for this blog article:
http://sqljunkies.com/WebLog/simons/...rchar_max.aspx
Hmm.. Interesting. I'm not sure some of the commenters were making a
distinction between varchar(somevalue) and varchar(max)? They seemed to be
comparing varchar with char. Others were concerned unexpected growth but if
there is a cause for that in the lifetime of a DB then the alternative is
errors downstream where data entered doesn't fit in the defined field.
Personally I'd rather see things slow down instead of break.

I agree it does seem a bit lazy though.
Mar 19 '07 #3
Anthony Jones wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:ug**************@TK2MSFTNGP03.phx.gbl...
>mike wrote:
>>so I keep optimizing my fields down to the minimum character length
necessary i.e., varchar(15), then I find out a month later its gotta
get bigger, then a few months later, bigger again, etc. Nowadays on
sql server 2005 and on, how bad is it really to use varchar(max)?
Is
there really a big performance or storage hit or is it negligible?

-Mike

Don't be lazy. There are several reasons for not doing this in the
comments for this blog article:
http://sqljunkies.com/WebLog/simons/...rchar_max.aspx
Hmm.. Interesting. I'm not sure some of the commenters were making a
distinction between varchar(somevalue) and varchar(max)?
I'm not sure what you were looking at. all the comments seemed to be on
target.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Mar 20 '07 #4

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
Anthony Jones wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:ug**************@TK2MSFTNGP03.phx.gbl...
mike wrote:
so I keep optimizing my fields down to the minimum character length
necessary i.e., varchar(15), then I find out a month later its gotta
get bigger, then a few months later, bigger again, etc. Nowadays on
sql server 2005 and on, how bad is it really to use varchar(max)?
Is
there really a big performance or storage hit or is it negligible?

-Mike

Don't be lazy. There are several reasons for not doing this in the
comments for this blog article:
http://sqljunkies.com/WebLog/simons/...rchar_max.aspx


Hmm.. Interesting. I'm not sure some of the commenters were making a
distinction between varchar(somevalue) and varchar(max)?

I'm not sure what you were looking at. all the comments seemed to be on
target.
The final comment by werner de jong seems to be entirely based on the
difference between char and varchar.

Also others references performance issues. I'm wondering where there is a
cost in performance between varchar(x) and varchar(max)?

Could be that I'm ignorant of some new details of 2005 internals. What is
the difference internally in the way these fields are handled.

Belsteak says 'unpredictable growth of the DB' and 'unwanted results in
column', what does that mean??

Anthony.
Mar 20 '07 #5
Anthony Jones wrote:
>>Hmm.. Interesting. I'm not sure some of the commenters were making a
distinction between varchar(somevalue) and varchar(max)?

I'm not sure what you were looking at. all the comments seemed to be
on
target.

The final comment by werner de jong seems to be entirely based on the
difference between char and varchar.
No, I believe he was talking about the impact of having many varchar columns
as opposed to a few. He is likely assuming that at least some of the
varchar(max) columns could be char, since the blog post he was replying to
made no reference to leaving existing char columns alone. Or using
varchar(max) to store other data types.
>
Also others references performance issues. I'm wondering where there
is a
cost in performance between varchar(x) and varchar(max)?
I think Adam Machanic's article covers this: "Remember that the query
optimizer uses column size as one of the many metrics for determining
optimal query plans. Given this table, the optimizer would have very few
options in that regard. "
>
Could be that I'm ignorant of some new details of 2005 internals.
What is
the difference internally in the way these fields are handled.
Again, from Adam's article:
The MAX data types, by default, use a hybrid of the TEXT/IMAGE overflow
behavior and the behavior of the normal (sized) VARCHAR/VARBINARY types. If
a column's data, plus the data in all of the other columns in the table, has
a total size of less than 8060 bytes, the data is stored in-row. If the data
exceeds 8060 bytes, the data in the MAX column will be stored off-row.
Belsteak says 'unpredictable growth of the DB' and 'unwanted results
in
column', what does that mean??
With varchar(max) one cannot be sure how much data is going to be stored in
it. Thus database growth cannot be predicted, making any forecasts of
database size meaningless. Also, every time a record's size exceeds a page
(8000 b), the record gets split. Every time the split occurs, performance
suffers, both for maintaining and for reading the data. Since we are not
controlling how much data gets stored, we cannot predict how many splits
will occur. "Unwanted results" I believe is referring to the lack of control
of data size removing one key validation method for preventing unwanted
data.

Many of these arguments are the same ones used to counter the old "why not
make every column a varchar(8000) column" proposal in the SQL2000 days.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Mar 20 '07 #6

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:O9**************@TK2MSFTNGP04.phx.gbl...
Anthony Jones wrote:
>Hmm.. Interesting. I'm not sure some of the commenters were making a
distinction between varchar(somevalue) and varchar(max)?

I'm not sure what you were looking at. all the comments seemed to be
on
target.
The final comment by werner de jong seems to be entirely based on the
difference between char and varchar.

No, I believe he was talking about the impact of having many varchar
columns
as opposed to a few. He is likely assuming that at least some of the
varchar(max) columns could be char, since the blog post he was replying to
made no reference to leaving existing char columns alone. Or using
varchar(max) to store other data types.
I see. I didn't read it that way. It seems to me the blogger is talking
about varchar(n) vs varchar(max).
>

Also others references performance issues. I'm wondering where there
is a
cost in performance between varchar(x) and varchar(max)?

I think Adam Machanic's article covers this: "Remember that the query
optimizer uses column size as one of the many metrics for determining
optimal query plans. Given this table, the optimizer would have very few
options in that regard. "
That's a good point. Statistics are not by default built for varchar(max)
fields but I believe they can be specified.
>

Could be that I'm ignorant of some new details of 2005 internals.
What is
the difference internally in the way these fields are handled.

Again, from Adam's article:
The MAX data types, by default, use a hybrid of the TEXT/IMAGE overflow
behavior and the behavior of the normal (sized) VARCHAR/VARBINARY types.
If
a column's data, plus the data in all of the other columns in the table,
has
a total size of less than 8060 bytes, the data is stored in-row. If the
data
exceeds 8060 bytes, the data in the MAX column will be stored off-row.
Cool. A pragmatic solution. I like.
>
Belsteak says 'unpredictable growth of the DB' and 'unwanted results
in
column', what does that mean??

With varchar(max) one cannot be sure how much data is going to be stored
in
it. Thus database growth cannot be predicted, making any forecasts of
database size meaningless.
Using varchar(max) doesn't mean you haven't got a reasonable idea of the
size of data going into the field. It means you don't want to limit the size
that might end up in there. I'm being devils adovacate here, for small
fields, titles, descriptions, names etc I wouldn't use varchar(max) either
(I agreed it is lazy) but for anything over say 512 I would have been
tempted but:-
Also, every time a record's size exceeds a page
(8000 b), the record gets split. Every time the split occurs, performance
suffers, both for maintaining and for reading the data. Since we are not
controlling how much data gets stored, we cannot predict how many splits
will occur.
Ouch. Yes that's a killer reason.
"Unwanted results" I believe is referring to the lack of control
of data size removing one key validation method for preventing unwanted
data.
Good point.
>
Many of these arguments are the same ones used to counter the old "why not
make every column a varchar(8000) column" proposal in the SQL2000 days.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


Mar 25 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: derochema | last post by:
I am trying to select many rows, but only the MAX rows of each distinct lead_seq. I don't want to actually select the MAX rows, just make it a condition. This is what I have: declare...
2
by: Robert | last post by:
When you use an aggregate function in a SELECT statement, you cannot specify any fields you want they way you usually can in a SELECT statement. Only fields that are part of the GROUP BY clause...
2
by: Mzgee | last post by:
Can i ask for a sample format using MAX ( sql query) in vb 6.0
4
by: shubhi | last post by:
I am using max in following way at various places but here i am getting error that single single-row subquery returns more than one row. I made correction in following query by taking max as...
1
by: Ripendra007 | last post by:
i have a table employee and i want to find the newly inserted record from employee table without using MAX And TOP ...it possible ? yes then How?
1
by: nico3334 | last post by:
I'm having trouble with a query using "MAX". In my query I'm trying to use 2 date columns as my criteria. Here is the format for each column: Date1: 01/01/2007 Date2: 200701 For my...
3
by: ncsthbell | last post by:
I am trying to run a query in access using the 'max' function. For example, I have many rows of data, one for each state and each state has a column for 'Miles'. I want to get the highest number of...
2
by: Bill E. | last post by:
SQL Server 2005 Simple scenario - We want to store answers to survey questions. Some questions require very short responses (one or two words) while others require long essay type responses. ...
10
by: netzorro | last post by:
Hi all, considering the new varchar(max) type I have changed some text columns in some tables. The problem is the "SQL Native Client 2005.90.1399.00" odbc driver does not return any value for...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.