Re: non numeric primary key

In article <3881EE57.CD29B093_at_arcavia.com>,   Kyle Lahnakoski <kyle_at_arcavia.com> wrote:
>
>
> Fernando wrote:
> > I've been told that choosing a non numeric field as a
 primary
> > key would hurt performance, is this true? O:-)
>
> Yes. In most DB implementations the number of bits used to store an
> number is smaller than used to store a string (Space). Second, the
> algorithm used to compare strings is less efficient, per byte, than
 it
> is for numbers (Speed).
>
> There could be implementations that are specifically suited for
> comparing short strings (4-8 chars) just as fast as numbers, but I
 would
> imagine they are rare.
> --
The most common rdbms in the World is Oracle and Oracle stores all numbers in an internal platform independent format, scientific notation, so no it is not necessarily more efficient to use a number instead of a character column. Most of the time when people refer to numbers as being more efficient than character fields they expect the number to be stored as a machine binary, but I have friends who work on databases that support tables with row counts heading to the tens of billions. A 32 bit integer does not cut it.

More importantly the key to a table should be the column(s) that support the business requirements. End users have access to a lot of adhoc tools and they like to query on business fields. Things like order numbers, part numbers, receiver numbers, and purchase order numbers, all of which may have non-numeric characters in them.

Giving every table a numeric key is an academic concept that has no real business justification. In the real world systems need to be understandable to the end-user, maintainable, and perform with reasonable efficiency.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.