Char Field to VarChar Field in VFP9

[vazagothic]

Hello all,
last week I've started working with VFP9 (after working with 6 & 8) and I am really happy with the product.

I've decided to change the structure of one table, a table which was created a long time ago and has pretty bad structure.

This is the structure of the table:

Field  Field Name      Type                Width    Dec   Index   Collate Nulls    Next    Step
    1  ITEM_ID         Numeric                10            Asc   Machine    No
    2  STATE_ID        Numeric                10            Asc   Machine    No
    [red]3  ITEM_TYPE       Character              17                             No[/red]
    4  DATEIN          Date                    8                             No
    5  DUEDATE         Date                    8            Asc   Machine    No
    [red]6  PERSONRPT       Character              30                             No[/red]
    7  PROB_DESC       Memo                    4                             No
    8  PRIORITY_ID     Numeric                10            Asc   Machine    No
    9  CLOSED          Date                    8                             No
   10  USER_ID         Numeric                10            Asc   Machine    No
   11  MODULE_ID       Numeric                10            Asc   Machine    No
   12  PRODUCT_ID      Numeric                10            Asc   Machine    No
   13  TEST_SCRIP      Numeric                10            Asc   Machine    No
   [red]14  KEYWORD1        Character              15                             No
   15  KEYWORD2        Character              15                             No
   16  KEYWORD3        Character              15                             No[/red]
   17  HRS_ESTIMATE..  Numeric                 7      2                      No
   18  VISIBILITY      Character              10                             No
   [red]19  EMAILTO         Character             125                             No[/red]
   20  ADDINFO_ID      Numeric                 3                             No
   21  IMPACT          Numeric                 3                             No
   22  SCOPE           Numeric                 3                             No
** Total **                                  342

I decided to convert the existing Character fields (marked with [red]Red[/red]) into new VarChar fields, which hold more flexibility.

Theoretically it should shrink the size of the table by more than 30% (the table is 3.5MB big, there are over 10'000 records in the table, but only 1'300 of them have a value in the [red]emailto[/red] field)

To my surprise, the table didn't shrink at all, to a contrary - it even grew bigger for about 10KB).

I thought, "well - maybe the VarChar field now keeps all the additional spaces" - so I tried the following:

REPLACE ALL emailto WITH ALLTRIM(emailto)

It DIDN't help. I tried copying the content of the table to another one - it didn't help either.

Then I did a small experiment and created two small tables with the same content, but one of them kept the data stored in a CHARACTER field, the other one in VARCHAR field.

Well ..

The CHARACTER Table:

CREATE TABLE a (cn [red]C(254)[/red])
FOR I=1 TO 5000
   INSERT INTO a (cn) VALUES ("123456789012")
ENDFOR

Size: 1'275'329 bytes

and the VarChar table:

CREATE TABLE b (cn [red]VarChar(254)[/red])
FOR I=1 TO 5000
   INSERT INTO b (cn) VALUES ("123456789012")
ENDFOR

Size: 1'280'361 bytes

What the heck ???
I THOUGHT that VarChar was supposed to take LESS space than a CHARACTER field, since it doesn't store the empty spaces at the end.

WAS I SO WRONG ??

Sorry for the long message, but I just cannot imagine VarChar being SO USELESS!

According to VFP9 help:
To include alphanumeric text in fields without including additional padding with spaces or truncating trailing spaces, use the Varchar type. Alphanumeric text can contain letters, numbers, spaces, symbols, and punctuation. The Varchar type also provides a convenient method for mapping SQL Server Varchar types to corresponding Visual FoxPro data types.

 

[Mike Lewis]

Vazagothic,

As you have found, varchars are not really varchars at all. They still occupy a fixed amount of space in the table.

But that does not mean that varchars are useless. On the contrary, if you are working with a back-end database like SQL Server, they are a very welcome addition.

Before VFP 9.0, when you send updates from a remote view to the server, the field in the server will be padded with spaces, even if it is defined as a varchar on the server. In VFP 9.0, by making the field a varchar within the remote view, this is not the case. This fact alone makes varchars a very useful feature.

I appreciate that your aim is to reduce padding within your DBF files, but unfortunately this is not what varchars are designed to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[rgbean]

Just to add to Mike's information, VFP's varchar fields still take a fixed size in the table, so that VFP's speed could be maintained! All you have to do is look at Access - that implemented true varchar fields - and you see how much access speed is slowed down. With a fixed length record you can go to record 234,567 (either directly or by index) simply by multiplying the record length by the record number and adding the header length, and you can be there in a single read. With variable length records (what you get with variable length fields), you either have to have links from one record to the next, or you need to maintain a secondary table with the offsets to the record number. This second method is very close to how FoxPro handles memo fields. This of course brings up the problem of what to do what an updated field gets longer than it was. (Move the data, and invalidate the old, use a more sophisticated "garbage collection" or force the user to PACK MEMO - ugh!)

Rick