Hi,

I am using Pervasive SQL v10 with the latest patch on a Windows 2003 Server.

I had a table with CHAR columns that I changed to VARCHAR.

ALTER TableName  ( MODIFY COLUMN "Field1" VARCHAR(x) NOT NULL);
 

Because the data on the table was not converted automatically, I had to manually update the content by executing:

UPDATE TableName SET Field1 = LTRIM(RTRIM(Field1))

The table has aroung 450.000 records, and the original size was 210Mb, but the strange thing is that when I run the update sql to trim the content of a field, instead of getting a smaller or same size table, the size almost doubled (400Mb).

What is going on with the Trim function? Is this normal? Is it possible to fix this??

Thanks. 

 This is the way that the database handles updates -- its called shadow paging, and it is documented in the manuals. 

In a nutshell, the SQL query runs in ONE transaction to guarantee atomicity.  This means that one MKDE transaction is created underneath it.  When an MKDE transaction is used to change data, then ALL changes must be held until they can be fully committed.  In your query, you are changing EVERY record in the file, which requires a change to EVERY data page in the file. 

Due to shadow paging, you need the amount of FREE SPACE in the file equivalent to the number of pages being changed in the transaction.  Your file probably has 20-100 free space pages, but you just implemented a transaction to update ALL pages, so a HUGE chunk of free space is needed.  Where does it come from?  The file grows!

The good news is that the file will NOT likely grow again for a long time.

A few options:

1. Rebuild the file AFTER the mass update.  This will shrink it back down to its smallest possible size.  This is best if you don't expect the file to grow that much.
2. Leave it alone. The file will eventually re-use that mass of free space pages.
3. Create a different process that doesn't use a big transaction.  Either write a Btrieve program to do the same thing, or write a stored procedure that does a positioned UPDATE statement on each individual record.  Since each record is in its own tiny transaction, the database doesn't grow nearly as much.  A further benefit -- an SP with Position Update is usually MANY TIMES FASTER!
   

Thanks a lot!

Now I finally understand. 

Very complete answer.