Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using large field size? If I change the limit to 30 char for this field for instance, will there be a performance gain or an efficiency with size? There would about 50 fields that could be candidates for shrinkage.

Thanks for your suggestions.

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:06

This question came from our site for professional and enthusiast programmers.

    
For char, the space is always used in the database, but for varchar, while the penalty will be less, the need to have larger space set aside during operations they you really need may also still make it a little less efficient. I wouldn't worry about varchar columns unless they are very large - like always using varchar(max) or varchar(1000). –  Cade Roux Aug 11 '11 at 12:47
    
You should be mindful of going over the size of one page (8k) since it will impact performance. Check out this post: stackoverflow.com/questions/2518922/… –  Madison Aug 11 '11 at 12:49
    
Given the low cost of hard drives, I'd not worry about efficiency of storage these days. As JNK says, there is an impact on indexing for very large fields - that's definitely worth bearing in mind. The pain of changing an application because you allocated too little space is far greater than the cost of a few extra bytes in your database table. –  Neville K Aug 11 '11 at 12:51
    
I think ignoring storage because it is cheap is a bad idea. Every byte on disk needs to be fetched and processed, and the slowest part of almost every SQL Server installation is the disk storage. Less bytes = faster queries. –  JNK Aug 11 '11 at 12:54
    
True - but not so as you'd notice in most real world situations I've encountered. On the other hand, re-writing an application because you've been too stingy with column widths causes real, measurable pain - I've got the scars to prove it. If you use VARCHAR, you only retrieve the data entered, and if the business domain includes addresses that are 100 characters long, restricting that width for performance reasons seems a poor trade-off to me. –  Neville K Aug 11 '11 at 12:59
show 5 more comments

3 Answers 3

up vote 14 down vote accepted

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.


Some caveats to bear in mind, though:

  • There is a 2 byte overhead per row for variable length fields (per field). If you have a very short field it may make more sense to use a CHAR. Varchar(2) for instance actually uses between 2-4 bytes per row, while CHAR(2) always uses 2.
  • Very long fields cannot be indexed. The maximum length for all fields in an index key set is 900 bytes.
  • If you allow more data than you expect, you will eventually get unexpected results. If you allow 100 characters for a street name, at some point other data is likely to get into that field without you being aware of it (for instance the entire address). If you had it appropriately sized, you would likely get an error on insert instead.
  • Allowing very wide rows could lead to page splits and fragmentation. If you have a row longer than 8k it will need to be split onto multiple data pages. A lot of these can really hurt performance. Narrower in general is more efficient.
share|improve this answer
1  
You could add caveats in shortening as well to this answer e.g. make sure that column is at least large enough: address varchar(30) cannot cope with Bolderwood Arboretum Ornamental Drive or Northeast Kentucky Industrial Parkway. –  Aleksi Yrttiaho Aug 11 '11 at 12:56
    
@Aleksi - very true. I think those are more obvious, though, which is why OP is using wide fields to begin with. –  JNK Aug 11 '11 at 12:57
    
"at some point other data is likely to get into that field without you being aware of it" An interesting point. I've seen plenty of systems where users took any field that was not applicable to the present record as a general-purpose comment field. –  Jay Aug 11 '11 at 17:11
2  
There can be a penalty –  Martin Smith Aug 12 '11 at 22:46
add comment

If you mean, "Is there a penalty for declaring the field size bigger than any values that are actually stored in it?", then as long as it is declared varchar, the answer is no. Every SQL DB engine that I know of stores only the number of characters actually given in the data (plus a length value). So if you define the field as varchar(100) but only store 10 characters in it, then it will only take up 10 characters on disk (plus 2 bytes or so for the length). When in doubt, I routinely make my varchar fields ridiculously large.

If you mean, "Is there a penalty for storing long character fields," the answer is yes. Disk space today is cheap, but it's not free, so you don't want to waste it for no reason. Probably more important, it takes time to read data off the disk, so the longer your data fields are, the slower the program becomes. If the field is indexed, this can really slow down your retrievals, as every read is going to have to compare the key value against this big long field.

Bear in mind that if you give the user a big data entry field, they will use it, sooner or later.

All that said, I'd err on the side of too big rather than too small. Disk space is cheap enough that you don't want to force users to invent abbreviations on the fly because they can't fit the real data into the available field. The system I'm working on today has a product description field that is too small for many of the real names of our products, so users have to abbreviate. And of course every user abbreviates differently, so we have twenty different ways to say the same thing.

share|improve this answer
add comment

Field length checking is something you get 'for free', meaning you don't have to use a CHECK constraint to do the same. And you don't want oversized data values when, for example, you have to upload your data to another database that has limited the same data element to 35 characters in line with the international standard address.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.