Char or varchar for a primary key?

[D.]

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.

[D.]

>

Should I define these primary keys as char[5] or varchar[5]?

Sorry, I intended char[10] or varchar [10]

[Erland Sommarskog]

D. ([e-mail]) writes:

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...

char(10) would make sense if key values are almost always 10 characters
long, but if the distribution varies with, say, 5 as the average varchar
would be better.

What sort of strings do you expect? If the values will be digits and upper-
case characters, you way want to consider a binary collation for the column,
at least if your default collation is a Windows collation.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

"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

[D.]

>

What sort of strings do you expect? If the values will be digits and
upper-
case characters, you way want to consider a binary collation for the
column,
at least if your default collation is a Windows collation.

It sounds good!
Yes, my keys will be only uppercase and digits (some other symbols are
allowed, like dot and hyphen)

How do I set binary collation on a single column?
Do you think that this will improve performance on lookups?
Do you think that having a single column with a different collation will not
decrease performance?

Davide.

[D.]

>

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).

Usually the key is fully filled, so I think I'll try to change varchar to
char.

Davide.

[Erland Sommarskog]

D. ([e-mail]) writes:

How do I set binary collation on a single column?

With the COLLATE clause:
CREATE TABLE mytable (
col char(10) COLLATE Latin1_General_ BIN2 NOT NULL,
...

Do you think that this will improve performance on lookups?

Yes, since comparison is a straight byte-comparison you gain some cycles,
particularly if your default collation is a Windows collation. It's
diffiuclt to say exactly how much you will gain, because there is a lot
of if depends. For a simple lookup, it's may be only 5-10%. For an
operation as "col LIKE '%str%' it may be drastic as a factor of seven.

If your default collation is an SQL collation (one there the name starts
with SQL), the gain is likely to be so small, that it's not worth the
pain. Note that this only applies if you use char/varchar. For
nchar/nvarchar there is no difference between SQL and Windows collations.

Do you think that having a single column with a different collation will
not decrease performance?

It will not, but there will be more hassle with programming. And it
would not be the only column with that collation, if there are other
tables with foreign keys to this table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Without more specs, I would go with CHAR(n) and a CHECK() constraint
that uses a regular expression to validate it.