We are looking at changing our database structure, which predominantly
uses char to use varchar, if this is deemed appropriate.

char stores 'extra' data to the tune of the trailing spaces in the
fields, is this correct?

varchar stored 'extra' data to the tune of the length of the data in
the field?

So, there is presumably one breakpoint where the likely number of
trailing spaces is > 4/8 bytes (for the storage of the length?)

i.e. char(5) best left to itself, and char(255) best converted to
varchar(255)?

Are there other performance/storage considerations?

Is this liable to be worth the effort?

Regards,

Iain

Hi Iain

You are close.But, trailing spaces have really nothing to do with it.
Consider only the actual data the column will hold.

CHAR columns do not have to have trailing spaces to use a lot of space. A
CHAR can have 'abc' and nothing else, but if it is stored in a CHAR(100)
column, SQL Server will still use the whole width. A VARCHAR will only use
the space for the data it contains, which might actually include some
trailing spaces, depending on what you inserted.

The 'extra space' needed for VARCHAR is two extra bytes per row for each
VARCHAR column. Since the length of the column is different in each row,
each row has to keep track of the length. So it is worse that worthless to
create a VARCHAR(1) or VARCHAR(2).

Usually, if the maximum size is 10 bytes or less, VARCHAR is not worth the
trouble because in addition to the two extra bytes, there is extra CPU time
to figure out how long each column is.

If the maximum length is larger, you really need to know your data. How many
characters is each column likely to hold? The rule of thumb I use is that if
80% of the rows will use 80% or more of the maximum length, I make the
column CHAR. So if you defined a column to be VARCHAR(100), but if most of
your data is 80-100 bytes, I'd go with CHAR.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Iain Sharp" <iains@pciltd.co.uk> wrote in message
news:ni3lc4pm76a87771k3uia7b2464jh3uv8t@4ax.com...
>
> We are looking at changing our database structure, which predominantly
> uses char to use varchar, if this is deemed appropriate.
>
> char stores 'extra' data to the tune of the trailing spaces in the
> fields, is this correct?
>
> varchar stored 'extra' data to the tune of the length of the data in
> the field?
>
> So, there is presumably one breakpoint where the likely number of
> trailing spaces is > 4/8 bytes (for the storage of the length?)
>
> i.e. char(5) best left to itself, and char(255) best converted to
> varchar(255)?
>
> Are there other performance/storage considerations?
>
> Is this liable to be worth the effort?
>
> Regards,
>
> Iain

Iain Sharp wrote:
>
> We are looking at changing our database structure, which predominantly
> uses char to use varchar, if this is deemed appropriate.
>
> char stores 'extra' data to the tune of the trailing spaces in the
> fields, is this correct?
>
> varchar stored 'extra' data to the tune of the length of the data in
> the field?
>
> So, there is presumably one breakpoint where the likely number of
> trailing spaces is > 4/8 bytes (for the storage of the length?)
>
> i.e. char(5) best left to itself, and char(255) best converted to
> varchar(255)?
>
> Are there other performance/storage considerations?
>
> Is this liable to be worth the effort?
>
> Regards,
>
> Iain

More rows on a page will increase performance, so yes, it could very
well be worth the effort. This also applies to index pages.

Another consideration is the behavior with respect to Inserts, Update
and Deletes. Let's take Updates. If a CHAR's value is changed, it can be
replaced in place. If a VARCHAR's short value is replaced with a larger
value, then the row might no longer fit on the page, causing a page
split, and with that potentially worse performance. This will also
happen to the column's index (when applicable).

If you have few Updates of the CHAR columns, then I would not worry
about that.

--
Gert-Jan
SQL Server MVP

Thanks for both these responses. Time to go check out existing
database usage for the columns concerned.

Iain

On Sat, 13 Sep 2008 13:32:20 +0200, Gert-Jan Strik
<sorry@toomuchspamalready.nl> wrote:

>Iain Sharp wrote:
>>
>> We are looking at changing our database structure, which predominantly
>> uses char to use varchar, if this is deemed appropriate.
>>
>> char stores 'extra' data to the tune of the trailing spaces in the
>> fields, is this correct?
>>
>> varchar stored 'extra' data to the tune of the length of the data in
>> the field?
>>
>> So, there is presumably one breakpoint where the likely number of
>> trailing spaces is > 4/8 bytes (for the storage of the length?)
>>
>> i.e. char(5) best left to itself, and char(255) best converted to
>> varchar(255)?
>>
>> Are there other performance/storage considerations?
>>
>> Is this liable to be worth the effort?
>>
>> Regards,
>>
>> Iain
>
>More rows on a page will increase performance, so yes, it could very
>well be worth the effort. This also applies to index pages.
>
>Another consideration is the behavior with respect to Inserts, Update
>and Deletes. Let's take Updates. If a CHAR's value is changed, it can be
>replaced in place. If a VARCHAR's short value is replaced with a larger
>value, then the row might no longer fit on the page, causing a page
>split, and with that potentially worse performance. This will also
>happen to the column's index (when applicable).
>
>If you have few Updates of the CHAR columns, then I would not worry
>about that.