Re: SQL Humor

On 18 Aug 2005 14:17:54 -0700, Mikito Harakiri wrote:

(snip quoteback)
>Now, we are talking! Verifying the foreign key constraint requires a
>unique index scan. If the index grows in size by factor of two, the
>number of levels might go up by 1, but typically would stay the same.
>In short, verifying a foreign key constraint would be 2 or 3 logical
>reads, in either case.
>
>Normally, upper levels of index are cached. I guess for country codes,
>the whole index is cached, so you are correct about country code PK
>index taking twice the space. Let's see 200 countries multipled by 2
>bytes. 800 bytes versus 400 bytes!

Hi Mikito,

Ah, cynicism - I love it.

I chose these examples because they are well known by everyone. My bad; I should have chosen more convincing (though less well-known) examples.

Many companies use a short mnemonic code for their customers. The use of a short mnemonic code for products is not uncommon either. Imagine a customers table with 10,000 customers, a products table with 5,000 products and an orders table with a few million rows. Now would you prefer char(6) or varchar(6) for CustomerCode? And char(5) or varchar(5) for ProductCode?

Another example: ticker symbols. Take a look at http://finance.yahoo.com To be able to generate all the graphs they offer, they have to have quite a few rows of historic quotes in their DB. How would you rate the overhead of char(5) vs varchar(5) for ticker symbol in a row that only has three columns: ticker, date/time and quote.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)