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


Column size overhead

Posted on 2004-10-31
Medium Priority
Last Modified: 2008-03-17
I have inherited a database that I am wondering about optimizing the column sizes. There are many varchar columns that are overly generous in their sizes. For example, varchar(100) where the data is routinely 20 characters or less and probably never over 30 characters wide.

Similarly, there are other fields that are way too generous for the forseeable data.

My question is: how much of a performance penalty are we paying for column sizes that are overly generous?
Question by:jasimon9

Assisted Solution

chipple earned 200 total points
ID: 12460860
Regarding the varchar type, since the number only represents the maximum size of the data (and not the actual size the data takes in the database), I don't think a field with a too large maximum can be affecting performance.

However, since the database system has a maximum record size, you risk reaching that maximum if more columns were to be added to the table. So it's always better to keep fields to a reasonable size.

Good luck.
LVL 11

Expert Comment

ID: 12461469

You can get the size of the various Datatypes by looking at the table definition in the EM. But there are some exceptions to this and the type you listed belongs to them.

The VAR... Datatypes will use a the lenght of the Data they really hold. So a varchar (8000) holding "Hello" will be 5 bytes wide
If you have a char(8000) holding "Hello" it would use 8000 bytes.

The "external" Datatypes like Text will use 16 Byte in the table to store a pointer to the real data thats not stored in the tablerow.

A Bit Datatype will use 1 Bit rounded up to the next Byte counting all bits... so 7 Bits will use 1 Byte and 9 will use 2 Bytes

Hope this helps

For more information check "Fieldlenght" under BOL

Accepted Solution

jahlife earned 300 total points
ID: 12513176
Good comments above.

But assuming MSSQL.

No performance penalty in SQL 2000 and above.  

The usage of variable length data types "used to" cause deferred updates, page splits and consolidations possible also, which could result in performance hits.


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

571 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