Re: CHAR() v. VARCHAR()

Rob, Sorry, but I must differ on your difference. According to my Informix SQL Tutorial (version 7.10): When the CHARACTER VARYING(m,r), VARCHAR(m,r), or NVARCHAR(m,r) data type is used, the rows of a table have varying lengths instead of fixed lengths. This has mixed effects on the speed of database operations. Also, according to my Administrator's Guide: OnLine also supports the VARCHAR data type, which results in rows of varying length. As a result, rows do not conform to a single format. Now, it's true that these are descriptions of OnLine. Standard engine doesn't support VARCHAR so there's nothing to say there. As far as C-ISAM goes, I thought it used only the standard "C" language data types. I've never used it though so I bow to your greater knowledge there. Your description of TEXT and BYTE are right on the money. Each row contains a 56-byte description of the blob which includes a pointer to the space holding the blob (either a dbspace, blobspace or an optical platter). --Craig (craig@chemistry.utah.edu) Rob McAllister wrote: > > CRAIG@CHEMISTRY.CHEM.UTAH.EDU wrote: > <snip> > > > So, varchars make much better use of you disk space-wise. There is, > >of course, a price to pay. When you add varchars to a table, the rows > >are no longer of a fixed length. If your select does a sequential > >scan of the table, it will now take longer because it will need to > >calculate the starting location of each row. For example, before > >the engine can examine record #2, it must figure out the length of > >record #1. Etc. Also, record updates become more difficult. For example, > >imagine a record with a varchar of 10 bytes. If you update the record > >so that the varchar is now 20 bytes, the record will most likely have > >to be moved to another spot on the disk where it can gain the 10 extra > >bytes it needs. > > I beg to differ on only one point, the data record is not really variable length > in that offset need to be calculated. The VARCHAR actually stores a 4-byte > pointer in the data record for each occurance. This points to a section (file) > reseveved for the index. So, there is always a 4 byte overhead for each VARCHAR > field and the extra I/O to fetch the variable length data from a different page > in the index space. This is how C-ISAM does it, and I believe this still holds > true, even in the ON-LINE product. TEXT and BYTE are different in that this > variable length data is stored external to both the data and index spaces (see > blob space). >