RE: Database character fields

First off Variable length fields have 2 or 4 byte header. ( I cannot
remember which) This is an integer that describes the length of the
data. Second the field has a fixed portion and an overflow area. The
fixed portion should be large enough to hold all the data in the
majority of the fields. So if your average field usage is 50 bytes, the
fixed portion should be at least 50 bytes. Second a variable length
field has a hidden pointer to the overflow area. Now this is another 16
bytes.

Now if the length of data is larger than the fixed portion, the DB
manager has to read the pointer and perform an secondary I/O to retrieve
the overflow data.

Think about what you are using the field for and how variable the data
really is before defining the field. Storage is cheap in today's
standards and I/O is performed in blocks. It may be easier and better
performing to read all those blanks.

Of course I am talking about DB2 and do not have a lot of knowledge on
how MSSQL stores their data.


Chris Bipes
Director of Information Services
CrossCheck, Inc.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Cunningham
Sent: Thursday, August 14, 2008 7:01 AM
To: 'Midrange Systems Technical Discussion'
Subject: Database character fields

Not looking to start a war over which is better, just looking to update
my knowledge.

When we create character fields in DB2/400 we usually setup fixed
length, no null support fields. If the character fields is long (e.g.
100+ bytes) and we suspect the data will vary greatly in actually used
length, we will make it a variable length field. The only time we use
null fields is for data we are importing in from other systems where the
file can have null values. In those cases the file with null field
support is usually a work file used for the import and they we move the
data into non-null fields in the production files. In other databases
(like MS SQL) the standard looks like it is just the opposite. All
character fields are variable length with null support unless you take
extra steps to not do that. My training (and it has been some years)
said that variable length fields are good for saving storage space but
bad for overhead. That the database had to do extra work to manage the
variable length, tracking the actually number of bytes in use and manage
the ov!
erflow areas when the data in the fields changed from 10 characters to
1,000 and back to 10. Is it still true that variable length fields are
less efficient and if so why do other databases have that as the
default? Or is this something specific to the implementation of the
database? Is DB2/400 move efficient with fixed length but MS SQL more
efficient with variable length?