Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
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: (Mark Gibson)
Message-ID: <>
Subject: Re: Size of null char field
MIME-Version: 1.0
Content-Type: multipart/mixed;boundary=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 =
a distant page even on another disk with pointers keeping things =
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 =
to determine how blank spaces are treated:

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

=B7Preceding blanks are not affected.

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

=B7The empty string ("") is treated as a single space. In char and nchar =
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 =
>byte for each space in a character field.  It also stores one byte if =
>field is null.  In other words, if you have a field that is char(3) =
>vs char(3) not null, the first uses 4 bytes and the second uses 3 bytes =
>if the field is completely filled.  However, if the char(3) null only =
>a value of "I", than only two bytes are used.
>I am doing this off the top of my head.  Check and=20
>look in the Reference Guide for exact storage requirements under "Data=20
>Lee Falkenhagen
>Certified Sybase Professional
>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
>>  > sybperl returns trailing spaces to fill out the column to the=20
>>  > width for not null fields. Yet it returns only the actual string =
>>  > null fields. Does that imply that sybase uses less disk space for=20
>>  > fields?
>> As I believe others have reported, Sybase stores nullable char  =
>> as varchars, which is the reason why they don't get padded with ' ' =
>> the actual maximum width. I'm not 100% sure if varchar fields use =
>> space than char fields, but I think so (at least if they are not
>> completely filled...)
>> Michael
>> --=20
>> Michael Peppler         -||-  Data Migrations Inc.
>>    -||-
>> Int. Sybase User Group  -||-

  Dennis R. Sherman              Triangle Research Libraries Network       Univ. of North Carolina - Chapel Hill