| Author |
Topic |
|
lalbatros
Yak Posting Veteran
69 Posts |
 Posted - 2011-07-26 : 03:44:37
|
| Hello,Most often the strings I use have a variable length.Therefore I use the varchar(n) type.However, I also tend to use a low value n.I typically estimate the maximum size based on a reasonnable sample of my data.Does that really make sense?Would I lose performance if I used a varchar(255) instead of a varchar(8) for a field?(assuming this field is frequently used in joins)Today I had a "String or binary data would be truncated" problem with an INSERT query. It would be solved easily by increasing the size to varchar(255) for all my fields.Would that have no bad side-effect?Thanks,Michel |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2011-07-26 : 05:00:28
|
| Assigning size for your variable is purely based on your business requirement. It does not matter of performance, But its matter of memory.To avoid string / binary truncated error, always use the same size which you used in your table. For example if you are using Address Varchar(255) , Use the same size in your program. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-26 : 07:27:24
|
| I usually tend to give more characters than needed, but I suspect many experts and purists would not condone that practice. My rationale is that, it does not take up any more space, so why risk the possibility that you will get truncation errors like the one you experienced.The flip side of the argument is that if you give only the length that the business requirements call for, any overflow would help you catch errors before they become ingrained. Also, there is the matter of maximum index size and warnings related to that. (the total width of columns used in an index cannot exceed 900(?) characters)Now, I have a question for you: why did you pick 255 (and not 256?). I do the same - i.e., use 255 or 511, instead of 256 or 512. (and I have my secret reasons for doing that). |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-26 : 07:53:41
|
Some people say: It is a relict from oldschool programming and I have forgotten why we do that.Some people say: 255 was the maximum length in MS SQL Server 7 varchar().Some people say: 255 is the maximum length in other Servers like MySQL so we do it for compatibility.Some people say: 255 is the maximum value to store in 1 (size-)byte, so the length of a varchar(256) will waste another byte.I say: That's interesting 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
lalbatros
Yak Posting Veteran
69 Posts |
Posted - 2011-07-26 : 08:01:41
|
| sunitabeck,My reason for the length of 255, is that I spied what occurs when running a "SELECT INTO" procedure based on an Exel table (OPENROWSET). In that case, the system automatically choses the length 255. It is also justified because is 11111111 in binary format, meaning that the full size of the length byte for the varchar type is used.This brings me to another question: my guess is that the varchar(n) dimension doesn't imply more space consumption. Space consumption would be determined only by the actual data (and maybe some technical factor like for hash-tables). Therefore, I would then see the dimension more like a user-oriented feature: telling what is allowed.Do you think this is more or true?Thanks,Michel |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-26 : 08:40:39
|
quote:
Originally posted by webfred
....Some people say: 255 is the maximum value to store in 1 (size-)byte, so the length of a varchar(256) will waste another byte.I say: That's interesting ...
This is sort of my unspoken/unacknowledged reason. I don't like to live on the edge. I do the same thing with decimal data type too. For example, DECIMAL data type storage is 5 bytes for precision 1-9 and 9 bytes for precision 10-19 etc. So if I would declare decimals as DECIMAL(8,n) or DECIMAL(18,n), just out of fear that, if I step too close, I may fall of the edge. ( I admit, it is irrational fear  ) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-26 : 09:39:28
|
| It doesn't matter if you specify varchar(255) or varchar(256), SQL Server stores the length of varchar values as 2 bytes not matter how long they are.One consideration is if you have 4 characters or fewer. Because of the varchar overhead, it's usually not worth the potential savings and char should be used instead. You can always RTRIM if you need to concatenate. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-07-26 : 10:21:50
|
| From a business POV, you have a field that is a VARCHAR(20) (20 being a random number for the sake of argument). You are importing data and you have specified to the client (or the client has specified) that the field can not be longer than 20 characters. You set your field to 255 anyway as you want an easy life. The client then screws up their export and sends you the following 'CODESHOULDBETWENTY oh look lets post some carp here'. Your system has just taken that in instead of setting it to an error and skipping that line or truncating the field, this then shows up on the front end and all hell breaks loose. Who gets it in the neck? |
|
|
|
|
about the n in varchar (n): I never asked before!