[RC5] packing fields

bwilson at fers.com bwilson at fers.com
Thu Jan 11 11:33:23 EST 2001

Hi, Tom.

The answer to your question is yes and no.  }:8)

Yes, SQL does pack numeric fields.  numeric(20, 0) (20 digits of detail, 0
digits after the decimal) occupies 10 bytes.  This is two digits per byte,
in essentially the same arrangement as IBM's packed decimal format.  For
example, the number 1234567890 would be stored as (hex)

00 00 00 00   00 12 34  56
78 90

(Not sure if my endian-ness is correct, but that's not the point)

"int" fields are stored in a purely binary format.  A four-byte integer
holds 2^32 distinct values. (-2^16 through +2^16 - 1)

No, there is no option for bit-packing as you're describing.  numeric(19,
0) still occupies 10 bytes, and there is no option to "share" that
half-occupied byte with another field.  A lot of this derives from SQL's
goal of making data retrieval as straightforward as possible.

For what it's worth, we don't define a "record length", or really have any
control of how SQL stores the data, except by choosing the datatype which
best fits our data requirements and storage limitations.  We define a
field, SQL allocates an appropriate space.  We ask for the field, SQL
gives it to us.  Beyond that it's invisible.

Though it's technically possible for us to define a variable-length binary
field and store our data in a highly packed format, we would sacrifice a
huge amount of speed when manipulating that data, and would sacrifice a
lot of other SQL capabilities (indexing, sorting).
Bruce Wilson, Manager, FERS Business Services
bwilson at fers.com, 312.245.1750, http://www.fers.com/
PGP KeyID: 5430B995, http://www.lasthome.net/~bwilson/
"A difference that makes no difference is no difference." --Spock

tomdv at datatx.com
Sent by: owner-rc5 at lists.distributed.net
2001-01-10 15:47
Please respond to rc5

        To:     rc5 at lists.distributed.net
        Subject:        Re: [RC5] Stats suggestion

Sorry to pop in on the discussion. Don't you have a possibility of packing
the fields in the database-definition? Don't shoot me just yet but IBM
AS/400 does this. When you use packed fields (only numeric, obviously) it
uses even less disk space. I even think it goes beyond that and when not
all the numbers are used, it even compresses the data more, eg the value
123 uses less disk space than 1234, with the same record length of 20.
Just a thought,

To unsubscribe, send 'unsubscribe rc5' to majordomo at lists.distributed.net
rc5-digest subscribers replace rc5 with rc5-digest

To unsubscribe, send 'unsubscribe rc5' to majordomo at lists.distributed.net
rc5-digest subscribers replace rc5 with rc5-digest

More information about the rc5 mailing list