Re: Strings vs. Numeric in Comparisons

From: "Steve Romankiw" <sromankiw@execrisk.com> > >As a rule of thumb, I generally define store numeric data as CHAR unless >this value is going to be used within calculations. Cool idea! Why stop at numerics? Why not store everything in CHARs? Stop wasting time with all those other datatypes Informix provides. Make it a lot easier to remember your database design, too. >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. Well, it is a performance issue too. An INT can store +/-2,000,000,000 (more or less) in 4 bytes. A CHAR would have to be a CHAR(10), and would therefore be less efficient in terms of the number of index pages required to manage a table's keys. Plus the table would be bigger. Double whammy -- more pages required to read the same number of rows. And if you were ordering by the column, a triple whammy. At a guess, I'd say the optimiser would prefer and perform better with INTs. But that *is* just a guess... >So, > >SELECT name FROM employee WHERE id_char = '100';>vs. >SELECT name FROM employee WHERE id_numeric = 100; Call me old-fashioned, but I'd go with the latter. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com