microsoft.public.sqlserver.server

Author

Char Vs. Varchar

Scott Wright

2002-06-22, 8:31 pm

I am trying to determine the appropriate data type for a column. Here is my dilemma:
I have character data with a max width of 26 characters and an average width of 9 characters. The column will be used frequently in joins. there will be a non-clustered index on this column. I understand that char data is much more efficient than varchar for joining, however, I'm wondering if the grater row density that would be achieved by making the column varchar would make up for the less efficient join? The varchar data would occupy significantly less space, so if the performance is similar I would prefer to go with the varchar.

Is there any rule of thumb as to when, if ever, it is better to go with varchar?

--
Scott Wright

Bob Pfeiff [MS]

2002-06-22, 8:31 pm

I think your description of the data for this column (max 26 characters, avg 9 characters) by itself is a good example of when to use a varchar, but there are other considerations; like how often the data in this column will be modified, will it change such that it may be more than 26 characters, and how wide the table is overall.

There is a good discussion of the pros and cons surrounding this choice in "Inside Microsoft SQL Server 2000" from MSPress.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

Andrew J. Kelly

2002-06-22, 8:31 pm

If there is that much variance then I suggest you go with the varchar. I'm not sure if a char is more efficient than a varchar for joining and the varchar is more efficient overall.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

"Scott Wright" <nospam@ihatespam.org> wrote in message news:#hdUmkIECHA.1436@tkmsftngp04...
I am trying to determine the appropriate data type for a column. Here is my dilemma:
I have character data with a max width of 26 characters and an average width of 9 characters. The column will be used frequently in joins. there will be a non-clustered index on this column. I understand that char data is much more efficient than varchar for joining, however, I'm wondering if the grater row density that would be achieved by making the column varchar would make up for the less efficient join? The varchar data would occupy significantly less space, so if the performance is similar I would prefer to go with the varchar.

Is there any rule of thumb as to when, if ever, it is better to go with varchar?

--
Scott Wright

Gert-Jan Strik

2002-06-22, 8:31 pm

In your case, I would suggest using varchar, unless the column changes
frequently in its size. In that case I would opt for the char solution,
because then in place key updates are always possible (and do not
require index page splits).

I haven't done any performance test on this (yet), but the varchar is
more space efficient, because a varchar only requires the actual size of
the string plus 2 bytes. So an average of 9 characters plus 2 bytes is
far less than the full 26 bytes.

Hope this helps,
Gert-Jan

> Scott Wright wrote:
>
> I am trying to determine the appropriate data type for a column. Here
> is my dilemma:
> I have character data with a max width of 26 characters and an average
> width of 9 characters. The column will be used frequently in joins.
> there will be a non-clustered index on this column. I understand that
> char data is much more efficient than varchar for joining, however,
> I'm wondering if the grater row density that would be achieved by
> making the column varchar would make up for the less efficient
> join? The varchar data would occupy significantly less space, so if
> the performance is similar I would prefer to go with the varchar.
>
> Is there any rule of thumb as to when, if ever, it is better to go
> with varchar?
>
>
> --
> Scott Wright
>
>

Vikrant V Dalwale [MS]

2002-06-22, 8:31 pm

Good discussion folks. Please let me know if I can be of further
assistance.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.© 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------
>From: "Scott Wright" <nospam@ihatespam.org>
>Subject: Char Vs. Varchar
>Date: Mon, 10 Jun 2002 10:15:18 -0400
>Lines: 69
>MIME-Version: 1.0
>Content-Type: multipart/alternative;
> boundary="----=_NextPart_000_000A_01C21067.B8BCEBE0"
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
>Message-ID: <#hdUmkIECHA.1436@tkmsftngp04>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: wrkstn43.prgx.com 208.205.29.43
>Path: cpmsftngxa08!tkmsftngp01!tkmsf
tngp04
>Xref: cpmsftngxa08 microsoft.public.sqlserver.server:213885
>X-Tomcat-NG: microsoft.public.sqlserver.server
>
>I am trying to determine the appropriate data type for a column. Here is
my dilemma:
>I have character data with a max width of 26 characters and an average
width of 9 characters. The column will be used frequently in joins. there
will be a non-clustered index on this column. I understand that char data
is much more efficient than varchar for joining, however, I'm wondering if
the grater row density that would be achieved by making the column varchar
would make up for the less efficient join? The varchar data would occupy
significantly less space, so if the performance is similar I would prefer
to go with the varchar.
>Is there any rule of thumb as to when, if ever, it is better to go with
varchar?
>--
>Scott Wright
>

Sponsored Links