PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Dennis R dot Sherman" <dennis_sherman at unc dot edu>
Subject: (Fwd) Re: Size of null char field
Date: Apr 29 1998 5:15PM

bounced by incorrect forwarding setup...

------- Forwarded Message Follows -------
Date: Wed, 29 Apr 1998 11:47:39 -0400
From: gibson@cbil.humgen.upenn.edu (Mark Gibson)
Message-ID: <199804291547.LAA18768@andromache.humgen.upenn.edu.>
To: SYBPERL-L@trln.lib.unc.edu
Subject: Re: Size of null char field
MIME-Version: 1.0
Content-Type: multipart/mixed;boundary=3d46_5fab-18a1_1e45-2266_95d


--3d46_5fab-18a1_1e45-2266_95d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-MD5: gMZjYVOUlzPvxWyBqPa10g==
X-Sun-Data-Type: text

Another reason to use char non nulls is to ensure in-place updates that=20
dont fragment the data - (an update that is bigger in size than the=20
previous data has to find another place to insert itself which could be =
on=20
a distant page even on another disk with pointers keeping things =
together)=20
a little tidbit from sybase optimization class - does anyone have=20
experience avoiding fragmentation in this way and has it paid off - im=20
wondering if its really worth it=20


Lee is correct :
 This is an except from Sybooks :

"Length and Storage Size=20

This example illustrates how the column's datatype and null type =
interact=20
to determine how blank spaces are treated:

=B7Only char not null and nchar not null columns are padded to the full =
width=20
of the column; char null columns are treated like varchar and nchar null =
=20
columns are treated like nvarchar.=20

=B7Preceding blanks are not affected.

=B7Trailing blanks are truncated except for char and nchar not null =
columns.=20

=B7The empty string ("") is treated as a single space. In char and nchar =
not=20
null columns, the result is a column-length field of spaces."=20


- Michael Brown=20
  Brown Computer Consulting
  ex-SQL Solutions/Sybase Co. Consultant

At 01:08 PM 4/28/98 -0700, Lee Falkenhagen wrote:
>Sybase stores null fields as varchars.  That means, if you define a=20
>column as char(3) null, it still stores it as varchar.  Sybase uses one =
=20
>byte for each space in a character field.  It also stores one byte if =
the=20
>field is null.  In other words, if you have a field that is char(3) =
null=20
>vs char(3) not null, the first uses 4 bytes and the second uses 3 bytes =
=20
>if the field is completely filled.  However, if the char(3) null only =
has=20
>a value of "I", than only two bytes are used.
>
>I am doing this off the top of my head.  Check sybooks.sybase.com and=20
>look in the Reference Guide for exact storage requirements under "Data=20
>Types".
>
>Lee Falkenhagen
>Certified Sybase Professional
>falkenl@hotmail.com
>
>On Tue, 28 Apr 1998, Michael Peppler wrote:
>
>> Mick Ghazey writes:
>>  > How big is a char field that allows nulls? The reason I ask is=20
because
>>  > sybperl returns trailing spaces to fill out the column to the=20
declared
>>  > width for not null fields. Yet it returns only the actual string =
for
>>  > null fields. Does that imply that sybase uses less disk space for=20
null
>>  > fields?
>>=20
>> As I believe others have reported, Sybase stores nullable char  =
fields
>> as varchars, which is the reason why they don't get padded with ' ' =
to=20
>> the actual maximum width. I'm not 100% sure if varchar fields use =
less=20
>> space than char fields, but I think so (at least if they are not
>> completely filled...)
>>=20
>> Michael
>> --=20
>> Michael Peppler         -||-  Data Migrations Inc.
>> mpeppler@datamig.com    -||-  http://www.mbay.net/~mpeppler
>> Int. Sybase User Group  -||-  http://www.isug.com
>>=20
>>=20
>
>
--3d46_5fab-18a1_1e45-2266_95d--



--
  Dennis R. Sherman              Triangle Research Libraries Network
  dennis_sherman@unc.edu       Univ. of North Carolina - Chapel Hill
               http://www.unc.edu/~sherman/