Vchar vs Char

[rc]

Hi

I have a SQL database with approx., 90m rows. Within the table
there several fields that are varchar 4. On a test server with approx.
200k rows I converted those to char4.

It showed that the database size shrunk by approx. 20 %, why I don't
understand.

My question is would I get a performance improvement for select
queries against those fields when they are char4 as opposed to varchar
4 ?

Thanks

[Hugo Kornelis]

Re: Vchar vs Char

On Fri, 08 Oct 2004 08:37:42 +0100, rc wrote:
[color=blue]
>I have a SQL database with approx., 90m rows. Within the table
>there several fields that are varchar 4. On a test server with approx.
>200k rows I converted those to char4.
>
>It showed that the database size shrunk by approx. 20 %, why I don't
>understand.[/color]

Hi rc,

The space required for char(4) is 4 bytes. The space required for
varchar(4) is 2 bytes for the length + the actual number of bytes used (0,
1, 2, 3, or 4). If most values are NULL, empty string or one byte long,
the varchar(4) should take less space. If most values are 3 or 4 bytes,
the char(4) will win.

[color=blue]
>My question is would I get a performance improvement for select
>queries against those fields when they are char4 as opposed to varchar
>4 ?[/color]

Probably. The main win will be that more data rows fit in a page, so less
page reads are needed to perform a query.

The overhead of finding start and end position in the row for a varchar is
CPU-based; I don't expect that you'll note a difference because of this
(the CPU spends most of his time waiting for the I/O subsystem anyway).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)