[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

649 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