Gabriel_Espinoza
asked on
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) ?
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) ?
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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...
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...
>> 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)
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)
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)
there is no difference, becasue varchar use only those much of size it has data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes.
You can also create field with varchar(8000) which SQL support and it will also be same as varchar(10)
You can also create field with varchar(8000) which SQL support and it will also be same as varchar(10)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
thank u all ^^