Strings vs. Numeric in Comparisons
As a rule of thumb, I generally define store numeric data as CHAR unless
this value is going to be used within calculations.
When it comes to performing SELECTs, does the optimizer prefer one over the
other? In others words, should numeric data be defined as INTEGER and
characters defined CHAR. Perhaps this is more a of data modeling question
than a performance.
So,
SELECT name FROM employee WHERE id_char = '100';vs.
SELECT name FROM employee WHERE id_numeric = 100;
SteveR
Storing numeric data as integers is much more efficient for a couple of
reasons. First, Integers only take 4 bytes. Second, it's much faster
to do comparisions on ints than chars because a string comparison has to
match each 4 byte character one at a time.
In article <8jg963$12i$1@news.xmission.com>,
"Steve Romankiw" <sromankiw@execrisk.com> wrote:
>
> As a rule of thumb, I generally define store numeric data as CHAR
unless
> this value is going to be used within calculations.
>
> When it comes to performing SELECTs, does the optimizer prefer one
over the
> other? In others words, should numeric data be defined as INTEGER and
> characters defined CHAR. Perhaps this is more a of data modeling
question
> than a performance.
>
> So,
>
> SELECT name FROM employee WHERE id_char = '100';> vs.
> SELECT name FROM employee WHERE id_numeric = 100;>
> SteveR
>
>
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
Check out Kevin Fennimore's article entitled "Schema Optimization" located at
www.iiug.org/~waiug/iugnew53.htm
He discusses int vs char columns.
Steve Romankiw wrote:
> As a rule of thumb, I generally define store numeric data as CHAR unless
> this value is going to be used within calculations.
>
> When it comes to performing SELECTs, does the optimizer prefer one over the
> other? In others words, should numeric data be defined as INTEGER and
> characters defined CHAR. Perhaps this is more a of data modeling question
> than a performance.
>
> So,
>
> SELECT name FROM employee WHERE id_char = '100';> vs.
> SELECT name FROM employee WHERE id_numeric = 100;>
> SteveR