Please help on a question about Informix varchars

Hi all: Any help on this would be appreciated. If I have a table that has 2 fields defined: table foo problem varchar(1024) nulls allowed Question one: 1. If problem is 'null', does this mean that 0 physical bytes are stored for that column? 2. If problem has 50 bytes of text stored in it, does this mean that 50 physical bytes are stored for that column? We may have thousands of "foo" rows and I want to use "varchar" instead of "char" for the sole purpose of saving space... If you could reply to theronk@sprynet.com as well as to the whole newsgroup, I'd very much appreciate it. Thanks so much in advance... Regards, Theron K

> If I have a table that has 2 fields defined: > > table foo > problem varchar(1024) nulls allowed > First of all, the maximum length for a varchar is 255 characters (unless this is an I-Universal Server thing, which I don't have experience with yet). > 1. If problem is 'null', does this mean that 0 physical bytes are stored > for that column? If you define the column with a max length only, such as problem varchar(255), then the only overhead to the rowid is one byte to track varchar related information. So your overhead per row (indexes aside) is five bytes, four for the rowid and one for special varchar handling (per varchar column). Inserting a NULL in this case reserves zero characters, so zero bytes are stored for that column (except for the one byte overhead, of course). Will these rows be updated? Are they updated frequently from NULL at insert to some value? If this is the case, then you should declare a minimum value for the varchar column, such as varchar(20,255). This example reserves space for 20 characters, and allows a maximum of 255. In this scenario, if a row is inserted with NULL in the column, 20 characters would be reserved. If an udpate is done and a value 15 characters in length is placed in the column, there is no overhead to update the row. If you didn't specify a minimum, then OnLine has to do some additional work (potentially moving the row to another page) to perform the update. Another thing: When there are varchar columns in your table definition, during an insert, OnLine will search for room on a page for the maximum size of the row, including the maximum size of your varchar field. If you have several varchar columns defined in a table with the maximum of size of 255 characters, this can actually reduce the number of rows inserted per page, because it reserves space on the page for a row of maximum length to help accomodate updates to rows on that page. Just be careful converting every char to a varchar of maximum length. It may actually cost you vs. save you space. OnLine works much better with char columns vs. varchar. If you need a size of 1024, then you can't use varchar anyway. You will have to resort to multiple char columns or a TEXT column to store character data of this size in. Varchars can save space, but it can also introduce overhead when updating these types of rows. If you insert it and never update it, or seldom update the value, then you can realize space savings with minimal overhead. If you do update these rows frequently, then select a minimum size that would accomodate the majority of your updates, again eliminating the overhead that may otherwise be introduced. > > 2. If problem has 50 bytes of text stored in it, does this mean that 50 > physical bytes are stored for that column? > > We may have thousands of "foo" rows and I want to use "varchar" instead of > "char" for the sole purpose of saving space... > > If you could reply to theronk@sprynet.com as well as to the whole newsgroup, > I'd very much appreciate it. Thanks so much in advance... > > Regards, > Theron K > >