Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Table Design - VARCHAR vs. CHAR 2

Status
Not open for further replies.
Oct 16, 2002
3
AU
Does anyone have any advise on the advantages / disadvantages of either field type. When does a VARCHAR become more efficient than a CHAR? ie Is there an optimum length of CHAR when VARCHAR should be considered?

What about with regard to substring searches? Is it more efficient to perform a search on a 200 character field or on a 200 varchar field?

Any advice most welcome.
 
The short answer is, it all depends!

If there is little variation in the column lengths, the stick with CHAR.

If there's lots of difference in lengths, use VARCHAR for maximum lengths over 30 say.

On uncompressed tables, updates of VARCHAR-containing rows can lead to rows being moved to other pages, because rows have increased in size. (Compressed tables will have variable length rows any way). VARCHAR columns are traditionally put at the end of rows.

VARCHAR fields need more host program processing as the length field has to be calculated.

Use of VARCHAR can save disk space and consequently I/O can be reduced.

Regards

Mick

 
We just changed columns in DB2 UDB 8 from Char to Varchar to get around extra blanks returned from SQL. We were experiencing issues with a WebSphere developed application taking longer than anticipated to populate pages.

We found that the amount of data being transferred was excessive becuase of the blank 'padding'. Yes, we could have applied some 'trim' functions but, that still would have produced data transmission volume issues between the database server and the JSP.

After changing to Varchar, performance improved significantly.

And, we also found that with DB2 UDB 8 running on a midrange server at least, it is possible to search on Varchar fields and to perform LIKE operations.

For what it's worth...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top