12

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.

Improve this question
10
  • 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, 2011 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, 2011 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 Kuyt
    Aug 11, 2011 at 12:51
  • 3
    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, 2011 at 12:54
  • 1
    If the 100MB causes 20% less data to fit in a 512MB disk controller cache, it will absolutely matter (voice of experience).
    – Eric J.
    Jan 25, 2012 at 19:06

4 Answers 4

Reset to default
16

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.
Improve this answer
4
  • 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, 2011 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, 2011 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, 2011 at 17:11
  • 2
    There can be a penalty
    – Martin Smith
    Aug 12, 2011 at 22:46
2

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.

Improve this answer
2

Anybody claiming that there is no penalty for declaring a field size bigger than what is actually going to be stored in the table is incorrect. The actual size of the data (plus that 2 byte overhead) is what actually gets stored, but it's the column definition that is used to determine the estimate as far as the execution plan goes. So, while declaring a varchar(1000) to store a 10 character value will only eat up 12 characters of disk space, the execution plan estimates will be much less efficient and negative skew the results, for both how much memory to grant the operation and whether or not the operation can be performed solely in memory or whether it'll require tempdb drive space as well. You may make your column varchar(1000), but the engine doesn't know that all of your stored values are really less than varchar(10), so the engine estimates defensively and allocates work space based on an average approximation of what could be in there.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.