Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Varchar: does the size matter?

Posted on 2011-04-26
15
Medium Priority
?
855 Views
Last Modified: 2012-05-11
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) ?

0
Comment
Question by:Gabriel_Espinoza
  • 4
  • 4
  • 4
  • +3
15 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35466777
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
 
LVL 10

Assisted Solution

by:Asim Nazir
Asim Nazir earned 400 total points
ID: 35466778
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 35466781
>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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 4

Expert Comment

by:malikirfan28
ID: 35466785
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
 

Author Comment

by:Gabriel_Espinoza
ID: 35467005
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35467026
>> 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
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35467044
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35467047
there is no difference, becasue varchar use only those much of size it has data.
0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 600 total points
ID: 35467052
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
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35467066
Yes.

You can also create field with varchar(8000) which SQL support and it will also be same as varchar(10)
0
 

Assisted Solution

by:Gabriel_Espinoza
Gabriel_Espinoza earned 0 total points
ID: 35467122
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35467162
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
 
LVL 4

Accepted Solution

by:
malikirfan28 earned 600 total points
ID: 35467223
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
 

Author Comment

by:Gabriel_Espinoza
ID: 35467294
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
 

Author Closing Comment

by:Gabriel_Espinoza
ID: 35499727
thank u all ^^
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 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