[RC5] packing fields

Jim C. Nasby jim at nasby.net
Sat Jan 13 15:51:47 EST 2001


Just to expand on that, Sybase does allow for bit fields, and it does
pack those. Also, based on your description, you might be reffering to a
varchar, which does size itself to the amount of data you stick in the
field. That sizing does come at a cost though, as you have to track the
size of the field on a row-by-row basis.

bwilson at fers.com wrote:
> 
> 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
>         cc:
>         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,
> Tom
> 
> --
> 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

-- 
Jim C. Nasby (aka Decibel!)                                  /^\ 
jim at nasby.net                                               /___\
Freelance lighting designer and database developer         /  |  \
Member: Triangle Fraternity, Sports Car Club of America   /___|___\

Give your computer some brain candy! www.distributed.net Team #1828
Get paid to surf!! http://www.enteract.com/~nasby/alladvantage.html
--
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