Re: Question about VARCHAR Vs. CHAR fields

As has been said before. The issue of any varchar vrs a char boils down to how hard it is to get to the variable portion of the row. With fixed length objects, sequential scan filtering is much easier (i.e. require fewer cpu cycles) than having to calculate where the variable portion is. There are several strageties used to calculate variable sized objects, but all solutions do require some form of calculation and calculations do require cpu cycles. Some DBMS use null terminated strings, but then that means you have to always scan to find the variable lengthed object. Some will split the row into fixed sized objects and then create an array of offsets for the variable length objects. The variable length objects may be stored in a variable portion of the row, or outside of the row in a LOB type of object., or possibably in a secondary row. In that case, the varchar still requires additional space because you have to have some form of a pointer to the physical location of the varchar. The cost may be hidden, but it is still there. Even using a null terminated string for a var char requires an additional space for the NULL. So that solution is going to use the same space as the IDS varchar, except instead of a column size, there is the column null character. And that solution is probably the worst to navigate because each character in the column must be examined just to get to the end of the column. "rkusenet" <rkusenet@hotmail.com> wrote in message news:3j2s8sFn8t3jU1@individual.net... > "DA Morgan" <damorgan@psoug.org> wrote > > > Is it true that in Informix VARCHAR takes more space than CHAR? > > Not at all. Just one byte extra at the beginning to record the length. > > > In Oracle the waste of space and CPU comes with working with CHAR and > > it has been almost completely abandoned. > > same is true with informix, except that upto a length of char(15) (some say even 20) > the performance gain in char is worth the space wasted. so I would always recommend > a char field upto char(15). > The difference between char and varchar becomes stark in delete-and-load tables. > That is tables which are periodically cleaned and loaded again. A table with fixed-length > columns (char, int, date etc) loads much faster than with variable length table. I think > it is something to do with the engine calculating before inserting on which page the > row will fit.

ahum how about when the engine considers a page to be full. this can be horrible with varchars. Superboer.

Only if the varchar column needs to be expanded. "Superboer" <superboer7@planet.nl> wrote in message news:1120719957.232581.305570@f14g2000cwb.googlegroups.com... > ahum how about when the engine considers a page to be full. this can be > horrible with varchars. > > Superboer. >