Re: CHAR() v. VARCHAR()

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). -------------------------------------------------------- Rob McAllister rmcall@on-ramp.ior.com Spokane, WA "On all good programmers a little core must dump"