As a general rule, are there performance benefits in using Numeric columns
as indexes over Char columns? If so, are there any sources which quantify
this?

I'm thinking, for example, of where one may have a Char(6) Customer Code
which is used as the primary key, what performance increase, if any, might
be gained by adding a Numeric internal serial no. column as the primary key
instead?

TIA
Mike Bannon

Hi

The addition of a surrogate key is usually used to reduce the width of a key
rather and therefore improve performance in terms less I/O (and less storage
especially if FKs are referencing it and they are frequently used in joins!)
Therefore the difference between 4 byte number and 6 byte character string
is probably not great.

There are also alot of other things that can improve index performance such
as using different filegroups on separate discs etc.

You may want to check out:

http://www.sql-server-performance.com/default.asp

John


[quoted text, click to view]

Mike Bannon (mikeb@dataformation.co.uk) writes:
[quoted text, click to view]

There are two issues here.

One is key length. If you replace your char(6) column with an integer
column, you gain two bytes per key value, and your index will be smaller,
means that for scans the index will be more effective. If you instead
use a numeric datatype, you may get the opposite effect, if you choose
a scale and precision that requires more than six bytes.

The other issue is collation. Character data is compared to under the
rules of a collation. The collation defines for instance whether lower-
case and uppercase are different letters, whether V and W sorts separately
etc. Obviously this is more complex than simple bit comparisons for an
integer value.

In the end, though, the gains you can make here may not be very significant.
For instance, if you need to keep the char(6) value, because this is a
key that is exposed to users, you may find that to retrieve the value,
you need to join to the base table to get the value. You may also find
that you need to index this column as well.

So I would suggest that firstmost look at your business requirements,
and what is best to simplify development. The gains in performance may
not be worth the extra hassle.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

One other issue to bear in mind if you use the char(6) customer code
as the primary key. It becomes extremely difficult to change customer
code at a later date (changing primary keys and all that).

The standard response that most business analysts will say is that
customer codes never change. However, in most large scale, real world
applications this is rarely the case. If you go with the internal
serial number, you can change the customer code at a later date.

Another issue to bear in mind is that customer codes have a nasty
habit of changing length/format.

Based on the above and the other advantages mentioned in the other
posts, my advice is to use a numeric internal serial number. In saying
that, I was at a Microsoft Report Services demo the other day, and the
guy was suggesting using GUIDs as primary keys rather than identity
columns. The advantage here is that the ID is allocated by the client
NOT the server. Personally, I dont like this approach, but does have

[quoted text, click to view]

Thanks John, Erland and M. Man

The situation is that one of our clients has had an external consultant in
to look at interfacing to our database. He's complained that he can't get
decent performance out of his interface and "it's because the database is
using alpha keys instead of ISNs". Within our system the performance is
fine, but I needed to get some objective view on this issue before
responding. You've given me that, thanks.

Mike

Hi Mike

I am not sure what you mean interfacing!!! You say that internally
everything is fine... therefore I can't see a reason for him to be any
different??? Maybe you should take a peak at profiling his work or QA-ing
his code????? Without knowledge of what his is trying to do and what he is
actually doing you can not give a definitive answer.

John



[quoted text, click to view]