Posted by Gert-Jan Strik on 05/28/07 12:38

"D." wrote:
>
> Hi,
> I'm planning the structure of a SqlServer 2005 database for a new
> application.
> The requirement is that primary keys must be "natural"; i.e. in the table
> Customers the primary key will be a max. 10 characters string (but the
> string may be filled i.e. with only 5 charachters).
>
> Should I define these primary keys as char[5] or varchar[5]?
> I'm interested in your opinion in particular about performace issue, because
> there will be tables with millions of records...
>
> Thanks,
> Davide.

Assuming you will not change existing primary key values often (or
ever), the performance between CHAR and VARCHAR comes down to the space
requirements.

VARCHAR claims two bytes for the string length, plus the actual number
of characters in the string. So if the average primary key length
exceeds 8, you are better off with CHAR(10), otherwise you could use
VARCHAR(10).

Because of some other considerations (the 'first' VARCHAR column will
cost an additional 5 bytes per row), when in doubt, I would choose CHAR
over VARCHAR. In your case, I would choose VARCHAR(10) if the average
length is 6 or smaller. Otherwise I would choose CHAR(10).

Gert-Jan

[Back to original message]