Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

varchar ./. char

3 posts in General Discussion Last posting was on 2003-07-21 12:55:01.0Z
Gerhard Posted on 2003-07-21 10:04:03.0Z
From: Gerhard
Date: Mon, 21 Jul 2003 06:04:03 -0400
Newsgroups: ianywhere.public.general
Subject: varchar ./. char
Message-ID: <18BE24CB7008D81400374DA285256D6A.00374DC985256D6A@webforums>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:1411
Article PK: 3642

Beside the additional byte are there any other
performance, storage, etc. penalties (trade-offs)
which I would have to consider when I choose
varchar instead of char?

TIA

Gerhard


Michael Gould Posted on 2003-07-21 10:48:27.0Z
From: "Michael Gould" <mgould@omnicc.com>
References: <18BE24CB7008D81400374DA285256D6A.00374DC985256D6A@webforums>
Subject: Re: varchar ./. char
Date: Mon, 21 Jul 2003 06:48:27 -0400
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#iEBSb3TDHA.346@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: adsl-19-231-30.jax.bellsouth.net 68.19.231.30
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1412
Article PK: 3643

Gerhard,

It is my understanding that internally ASA stores these pretty much the
same.

Michael Gould

<Gerhard> wrote in message
news:18BE24CB7008D81400374DA285256D6A.00374DC985256D6A@webforums...
>
> Beside the additional byte are there any other
> performance, storage, etc. penalties (trade-offs)
> which I would have to consider when I choose
> varchar instead of char?
>
> TIA
>
> Gerhard
>


Breck Carter [TeamSybase] Posted on 2003-07-21 12:55:01.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Subject: Re: varchar ./. char
Date: Mon, 21 Jul 2003 08:55:01 -0400
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <0ionhv01r10vs3bo5to36jcla14g2tcutf@4ax.com>
References: <18BE24CB7008D81400374DA285256D6A.00374DC985256D6A@webforums>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: nnc010.canpar.ca 204.225.91.186
Lines: 155
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1415
Article PK: 3648

Here is a section from the forthcoming SQL Anywhere 9 Developers
Guide...

=====
1.7 A String Is A String: BINARY, CHARACTER, LONG

All character and binary columns are stored as varying length
character strings regardless of how they are declared. The maximum
length specifies a limit on the byte size of the data portion of the
string, with a default of 1 byte. The LONG VARCHAR and LONG BINARY
types have an implied maximum length of 2GB.

<string_type> ::= <char_type> [ "(" <maximum_length> ")" ]

| LONG BINARY

| LONG VARCHAR

<char_type> ::= BINARY
| CHAR [ VARYING ]
| CHARACTER [ VARYING ]
| VARBINARY
| VARCHAR

<maximum_length> ::= integer literal in the range 1 to 32767

Tip: All these data types, including LONG VARCHAR and LONG BINARY, may
be used for local and global variables in stored procedures and other
SQL scripts, as well as for columns in tables.

Storage requirements and execution performance depends on the current
length of each column value rather than the maximum length. Long
strings are split and require more overhead than short strings,
whereas short strings are stored efficiently even if they are declared
as LONG VARCHAR. Here's how it works: String values up to 254 bytes in
length are always stored together with the other columns in the row.
When the length grows to 255 bytes or larger the value is partitioned
into two or more pieces; the first piece is 254 bytes long and remains
where it was while the other pieces are placed on different pages in
an area called the "blob arena". The blob arena is a group of pages in
the database file where long binary and character values are stored.
These pages are kept separate so that a query or sequential scan which
doesn't need to look at the long values won't have to retrieve all
these pages.

From a SQL programming point of view, a string is a string in SQL
Anywhere 9 and you don't have to worry about the declared data type.
For example, if you think all company names will fit into 30
characters but you are concerned about exceptions, there is no
performance penalty for using CHARACTER ( 100 ) or even 1000.
Similarly, a description column that will usually require only a few
hundred characters can be declared as LONG VARCHAR to handle those
special cases; your database won't grow in size until you actually
store very long values.

Exactly the same data may be stored in both CHARACTER and BINARY
columns. In particular, the zero byte ( hexadecimal 00 ) may be stored
in a CHARACTER column and it is treated as data, not a string
terminator.

Tip: In some programming environments the zero byte string terminator
is called "null". This is not the same as the database NULL value
implemented by SQL Anywhere 9; database NULLs require special handling
when they are used in applications.

There are a few exceptions to the assumption "a string is a string".
First, sorting and comparisons involving BINARY columns always use the
actual binary values whereas CHARACTER columns are sorted and compared
according to the database collation sequence and case sensitivity. For
example, in a case insensitive database (the default) the CHARACTER
values 'a' and 'A' are treated as being equal, whereas the BINARY 'a'
is treated as being less than the BINARY 'A' when they are compared or
sorted.

Tip: Use the CAST function when you need to perform case-sensitive
comparisons in a case insensitive database; e.g., IF CAST ( char1 AS
BINARY ) = CAST ( char2 AS BINARY ). This also works in the WHERE and
ORDER BY clauses, and can be used when you need to ignore the database
collation sequence.

econd, a few functions only work on the first 255 bytes of the
character string arguments: SOUNDEX, SIMILAR, and all the date and
time functions ignore anything past 255.

Third, a conversion from string to numeric will also ignore any data
past 255 bytes.

Fourth, an attempt to assign a long string value to a column declared
with a shorter maximum length will result in right truncation. This
truncation will happen silently when only spaces are being truncated
by an INSERT or UPDATE command, or when non-spaces are truncated but
the STRING_RTRUNCATION option is still set to the default 'OFF'. To
generate an error message when non-spaces are truncated you must set
STRING_RTRUNCATION to 'ON'. Note that trailing spaces are significant,
and are never truncated unless they won't fit in the declared maximum
length.

Tip: The LTRIM, RTRIM and TRIM functions can be used to get rid of
leading and trailing spaces.

Fifth, some application development tools generate different code and
user interface elements depending on the maximum string length of a
column. In some cases a column declared as CHAR may be treated as a
fixed-length string even though SQL Anywhere 9 does not implement it
that way.

Finally, there are some performance implications to declaring a string
column with a maximum length far larger than you need. The declared
width of columns in an index is used to determine if a compressed
B-tree index can be used instead of a hash B-tree index. Subquery and
function caching may be turned off in cases where the total declared
maximum length of the columns and arguments is very large. Also, the
query optimizer may be able to get better information from the column
statistics for columns with a declared maximum size less than 8 bytes.
Some of these topics will be discussed in Chapter 12, Tuning.

Otherwise, a string is still a string, and you can happily store and
copy and assign values with different declared string types without
any problems.
=====

Breck
On Mon, 21 Jul 2003 06:04:03 -0400, Gerhard wrote:

>
>Beside the additional byte are there any other
>performance, storage, etc. penalties (trade-offs)
>which I would have to consider when I choose
>varchar instead of char?
>
>TIA
>
>Gerhard

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
http://www.risingroad.com