Column size overhead

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?
Who is Participating?
jahlifeConnect With a Mentor Commented:
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.

chippleConnect With a Mentor Commented:
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.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.