Varchar vs Char in 1st field of composite clustered index

[Miss Livvy]

Would it be OK to use varchar(5) instead of char(5) as the first field of a
composite clustered index?

My gut tells me that varchar would be a bad idea, but I am not finding much
information on this topic on this when I Google it.
Currently the field is Char(4), and there is a need to increase it to hold 5
characters.

TIA

[Hugo Kornelis]

On Tue, 08 Feb 2005 03:17:31 GMT, Miss Livvy wrote:

Would it be OK to use varchar(5) instead of char(5) as the first field of a
composite clustered index?

My gut tells me that varchar would be a bad idea, but I am not finding much
information on this topic on this when I Google it.
Currently the field is Char(4), and there is a need to increase it to hold 5
characters.

Hi Miss Livvy,

It depends. What is the average length of the data? Does the column allow
NULLS? If so, how many of the rows will have NULL in this column?

Each varchar columns takes 2 additional bytes of storage to store the
actual length. If most values in this column will be 4 or 5 characters,
the storage size will increase to 6 or 7 bytes - you'll be better served
with using CHAR(5). If many values are 1 or 2 bytes, you'll save some
space by usign VARCHAR(5). Even more for empty strings (0 bytes + 2 bytes
for length) and NULLS (also 0 bytes + 2 for length -unused, in this case,
but still reserved).

Saving bytes, both in the leaf (data) pages of the clustered index and in
the root and intermediate pages (where only the indexed values are stored)
will result in more rows fitting in each page, thus less page reads and
better performance. Sure, the varying character of the column might
introduce some extra overhead for the processor - but with the difference
in speed between CPU and disk subsystem, this is irrelevant. I have yet to
witness a database application that leaves the disk susbsystem idle while
waiting for the CPU to finish it's computations. In my experience, the
bottleneck is always disk speed.

My advice: check the average number of bytes for this column, including
the 2 bytes overhead for length (+ 2 extra if this is the ONLY varying
length column in the table!). The pick the data type that takes the
smallest number of bytes.

Best, Hugo
--

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

[Miss Livvy]

Hugo,
This is very helpful. Thanks.

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:lf********************************@4ax.com...

On Tue, 08 Feb 2005 03:17:31 GMT, Miss Livvy wrote:

Would it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?

My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.
Currently the field is Char(4), and there is a need to increase it to hold 5characters.

Hi Miss Livvy,

It depends. What is the average length of the data? Does the column allow
NULLS? If so, how many of the rows will have NULL in this column?

Each varchar columns takes 2 additional bytes of storage to store the
actual length. If most values in this column will be 4 or 5 characters,
the storage size will increase to 6 or 7 bytes - you'll be better served
with using CHAR(5). If many values are 1 or 2 bytes, you'll save some
space by usign VARCHAR(5). Even more for empty strings (0 bytes + 2 bytes
for length) and NULLS (also 0 bytes + 2 for length -unused, in this case,
but still reserved).

Saving bytes, both in the leaf (data) pages of the clustered index and in
the root and intermediate pages (where only the indexed values are stored)
will result in more rows fitting in each page, thus less page reads and
better performance. Sure, the varying character of the column might
introduce some extra overhead for the processor - but with the difference
in speed between CPU and disk subsystem, this is irrelevant. I have yet to
witness a database application that leaves the disk susbsystem idle while
waiting for the CPU to finish it's computations. In my experience, the
bottleneck is always disk speed.

My advice: check the average number of bytes for this column, including
the 2 bytes overhead for length (+ 2 extra if this is the ONLY varying
length column in the table!). The pick the data type that takes the
smallest number of bytes.

Best, Hugo
--

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