String vs Numeric Type

[Victor Mehta]

would it matter if I decided to make a primary key a string of numbers
vs actual numbers ? would it make any difference to DB2 in terms of
efficiency ? why would you choose numeric over a string for a key that
does not need to be in numeric format ?

thanks
--
reply to newsgroupsurfer ATyahooDOTcom

[Ian]

Victor Mehta wrote:

would it matter if I decided to make a primary key a string of numbers
vs actual numbers ? would it make any difference to DB2 in terms of
efficiency ? why would you choose numeric over a string for a key that
does not need to be in numeric format ?

Probably the most significant difference at a physical database level is
the fact that INTEGERs require 4 bytes of storage, while you would need
CHAR(10) to store the same range of values.

However, this should never be decided at a physical database level.
Your data model will (should) always determine the proper domain for
your attributes.

[Serge Rielau]

Ian wrote:

Victor Mehta wrote:

would it matter if I decided to make a primary key a string of numbers
vs actual numbers ? would it make any difference to DB2 in terms of
efficiency ? why would you choose numeric over a string for a key that
does not need to be in numeric format ?

Probably the most significant difference at a physical database level is
the fact that INTEGERs require 4 bytes of storage, while you would need
CHAR(10) to store the same range of values.

However, this should never be decided at a physical database level.
Your data model will (should) always determine the proper domain for
your attributes.

Indeed! I've just been butting heads with a customer where a "device ID"
which is an "encoding" of "bit ranges" was implemented as INTEGER.
Next thing I hear is "Why doesn't DB2 support UNSIGNED INTEGER because
we loose the 0x8000000 bit."
After some patient explanation and pointing to little and big endian in
a mixed environment they are inching towards CHAR(4) FOR BIT DATA (aka
BINARY) now.
Data types are no afterthought... .

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Brian Tkatch]

Ultimately, all data is stored as bits, so the data type INDEXed should
be irrelevant. Personally, i try to use INTEGER as my PRIMARY KEYs,
because i like to refer to TABLEs as objects, and the individual
records as instances of those objects. Having a COLUMN in most every
TABLE called "Id" and having it always be an INTEGER is very convenient
and consistant. Plus, if another TABLE is to REFERENCE this TABLE, a
long character string is not needed, just a (relatively) small INTEGER.

There are other reasons i like it. I find that when text is used for an
identifier, the text may change over time. A non-descript INTEGER
shouldn't and therefore makes an excellent PRIMARY KEY. I'll then make
the character field UNIQUE instead.

So, i simply find the use of an INTEGER to be better fitted.

A string can be used just as well.

B.

[Knut Stolze]

Brian Tkatch wrote:

Ultimately, all data is stored as bits, so the data type INDEXed should
be irrelevant.

It depends what is important to you. From a conceptual point you are right.
But practically, there are some differences that might be important.
Storing an integer as char(10) requires 10 bytes. That wastes space in the
index and makes comparison operations slower (byte-wise instead of a single
processor instruction). So the internal representation of the data does
matter.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Jonathan Leffler]

Knut Stolze wrote:

Brian Tkatch wrote:

Ultimately, all data is stored as bits, so the data type INDEXed should
be irrelevant.

It depends what is important to you. From a conceptual point you are right.
But practically, there are some differences that might be important.
Storing an integer as char(10) requires 10 bytes. That wastes space in the
index and makes comparison operations slower (byte-wise instead of a single
processor instruction). So the internal representation of the data does
matter.

And comparing integers gives funny results if the strings are left
justified rather than right justified...

--
Jonathan Leffler #include <disclaimer.h >
Email: jl******@earthl ink.net, jl******@us.ibm .com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

[Brian Tkatch]

Ah, i see i misread the original question for numric versus
alpha-numeric.

I might as well ask though....

Storing an integer as char(10) requires 10 bytes. That wastes space in the index and
makes comparison operations slower (byte-wise instead of a single processor instruction).

Isn't there something about compacting INDEXes? Or that when most of
the first part of the INDEX values are the same, it really doesn't
matter much when traversing the INDEX?

That is, being numbers are relatively the same:

0001
0002
0003
0004
0005
0006

Here the first three digits do not need to be searched at all since
they are always the same.

B.

[Ian]

Brian Tkatch wrote:

Ultimately, all data is stored as bits, so the data type INDEXed should
be irrelevant. Personally, i try to use INTEGER as my PRIMARY KEYs,
because i like to refer to TABLEs as objects, and the individual
records as instances of those objects. Having a COLUMN in most every
TABLE called "Id" and having it always be an INTEGER is very convenient
and consistant. Plus, if another TABLE is to REFERENCE this TABLE, a
long character string is not needed, just a (relatively) small INTEGER.

Oy. Data modelers are rolling in their graves. A generic "ID" column
is really, really confusing. At least give it a useful name, like
CUSTOMER_ID or ORDER_ID. What do you do when you need do use the "ID"
column from the CUSTOMER table in the ORDERS table (which already has
an "ID" column as the primary key?)

[Phil Sherman]

Jonathan Leffler wrote:

Knut Stolze wrote:

Brian Tkatch wrote:

Ultimately, all data is stored as bits, so the data type INDEXed should
be irrelevant.

It depends what is important to you. From a conceptual point you are
right. But practically, there are some differences that might be
important. Storing an integer as char(10) requires 10 bytes. That
wastes space in the
index and makes comparison operations slower (byte-wise instead of a
single
processor instruction). So the internal representation of the data does
matter.

And comparing integers gives funny results if the strings are left
justified rather than right justified...

Funny results when comparing integers is a minor issue when compared to
the potential problems in data entry. Many years ago, I worked on an
application that was storing (key value) integers in a character field
and discovered that there were a number of unique entries in the
database for key "1". Between left, right, center justification and the
inclusion or lack of leading zeros, it was impossible to locate the
correct data for this key.

Phil Sherman

[Brian Tkatch]

Actually, it drives me batty when people do that. To me, nearly every
TABLE should have three standard COLUMNs with the *exact* same names,
Id, Name, Description. That way, each TABLE is an object, and each
record is an instantiation of that object. To do it otherwise, to me,
is to completely misunderstand what a database is.

If there is a COLUMN in the customer TABLE called Id, it *by defintion*
is the customer's id. Calling it "customer_i d" is redundant.

When a TABLE REFERENCES another TABLE, the _TABLE name_ should be used,
*not* the COLUMN name. Thus, in the orders TABLE, the REFERECEing
COLUMN name should be "customer" not "customer_i d".

B.