Varchar: does the size matter?

Hello everyone,

I have a question related to Database DataType Varchar.

as far as I know, varchar is a variable size character chain, am I right?
so if I declare a variable as varchar(50) and I use only 10 of those characters, the length of the chain will be only 10 characters.

so is there a difference between declaring a variable varchar(max) versus varchar(10) ?

Gabriel_EspinozaAsked:
Who is Participating?
 
malikirfan28Commented:
Yes there will be no difference as SIZE is based on the value which this field keep and performance also does not matter on it.

But normally we design database in such a way that when someone want to see, he can make a guess that what value this fields can have. For example if one field is SEX which can have either M/F OR Male/Female and you make it varchar(8000) then offcource it will not be a good approach.

But as said there will be no difference in varchar(10) and varchar(8000)
0
 
knightEknightCommented:
varchar uses two bites to define the size of the data, and the rest for the data.  So any varchar will use at minimum 2 bytes.  A varchar(50) that contains a 10 character string will use 12 bytes  (10+2).
0
 
Asim NazirCommented:
Nice answere here:
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/4d9c6504-496e-45ba-a7a3-ed5bed731fcc

Text from same thread:
There are a few diferences between VARCHAR(1-8000) and VARCHAR(MAX).

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. By default sql server will try to accomodate the value 'in row' but if it could not, it will store the large values 'out of row'. When values are stored 'out of row' there will be slight processing overhead in reading the information. Here is a good reference: http://msdn2.microsoft.com/en-us/library/ms189087.aspx

I guess you cannot index a VARCHAR(MAX)/NVARCHAR(MAX) column.

 coming back to your question:

I dont think it is bad to use VARCHAR(MAX) is bad. If you are storing smaller piecs of data in a VARCHAR(MAX) column, it will be treated as normal. If you dont want to index the column, then you can definitely go with VARCHAR(MAX) option.

 But most people do not advise that. First of all, by having a VARCHAR(MAX) will confuse some one who looks at the data later on. For example, if you want to store a comment of 100 characters or address of 80 characters, why should you go for VARCHAR(MAX)? If you use Address VARCHAR(MAX), comments VARCHAR(MAX), Name VARCHAR(MAX), some one trying to read or write data on a later date will be confused. They will not know what is the expected size of the data and they will be compelled to use LARGE VALUE types always.

Again, these are not rules but conventions.

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Aneesh RetnakaranDatabase AdministratorCommented:
>so if I declare a variable as varchar(50) and I use only 10 of those characters, the length of the chain will be only 10 characters.


yes

the query optimizer treats varchar(max) and varchar(10) differently, it will create a better plan for those columns where the length is known. in case of varchar(max) the length os not known, so will have a poor plan. better specify the max lenght
0
 
malikirfan28Commented:
varchar(max) will create the maximum lenght of varchar supported in SQL (maximum lenght of varchar in bytes is 32,672)
whereas varchar(10) will create only 10 character length.

That's true that varchar will take only memory size based on its value for example if you define varchar(10) and set its value "ABC" then its length will be 3. But here you can't set more than 10 character value in it.

I hope I explain.
0
 
Gabriel_EspinozaAuthor Commented:
thank u all for the fast reply,

ok, there is one thing clear as water:
varchar(10) vs varchar(MAX) => I should not use Varchar(MAX) option if I want to index the field.

what if I use varchar(1000) instead of max? will this be less efficient for treating strings of 10 characters length?

I'm asking this cause I always try to get the varchar as small as I can, but today I woke up and I realized that it may not be necesary...
0
 
knightEknightCommented:
>> what if I use varchar(1000) instead of max? will this be less efficient for treating strings of 10 characters length?

Only if the actual data in the varchar(1000) is longer than the values in a varchar(10)  :)
But it is still better than using varchar(max)
0
 
malikirfan28Commented:
Yes you can use varchar(1000) and you can also create index on the field. And yes it will definitely be better than varchar(max)
0
 
Alpesh PatelAssistant ConsultantCommented:
there is no difference, becasue varchar use only those much of size it has data.
0
 
knightEknightCommented:
In other words, if the length of each string in you varchar(1000) column is always 10 or less, it will perform the same as if the column were defined as varchar(10).  Note that for strings of length 10, 12 bytes are used to store each string, whether it is varchar(10) or varchar(1000).
0
 
malikirfan28Commented:
Yes.

You can also create field with varchar(8000) which SQL support and it will also be same as varchar(10)
0
 
Gabriel_EspinozaAuthor Commented:
omg, I've been wasting my time by searching for the smaller length a string could get for nothing!!!


only to confirm, we can say that:

DECLARING A VARCHAR(8000) VS A VARCHAR(10) FOR STORING STRINGS OF LENGTH 10 WILL BE EQUALLY EFFICIENT IN SIZE AND PERFORMANCE.

note that it says size AND performance.

0
 
knightEknightCommented:
yep! :)

But if you are storing strings of length 3 or shorter, better to use char(3) instead of varchar(3), because char(3) will use less space and generally perform better than varchar(3).  fyi...
0
 
Gabriel_EspinozaAuthor Commented:
thank you all for the fast replies and the help.

I don't think I will be using varchar(8000) for everything, this is more a Proffessional inquiry.
From now on, I will not be so worried about been so accurate on defining varchar fields.
0
 
Gabriel_EspinozaAuthor Commented:
thank u all ^^
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.