At 04:46 AM 1/16/2003 +0000, darryl_caillouet <darryl.caillouet@...>
wrote:

>For the sake of argument, let me ask you FB gurus a question. If I
>use your suggestion to get case-insensitive indexing, I get duplicate
>data in my primary table AND an index on the duplicate data.

Not exactly - the upper case version of the column doesn't
duplicate the proper case version, it projects the values
into a simpler alphabet.

>My question is, does the primary key on the Emp_Index table
>physically sort the data in that order and not create an additional
>index.

No, it creates an index and does not store the data sorted.
Firebird doesn't store data in indexes.

>If I wanted to port this to another database, I would just port the
>primary tables. I wouldn't port the *_Index tables or the triggers.

If you add an indexing column to your main table, your port
just eliminates that field, its index, and the triggers that
maintain the field, and add a case-insensitive index. In
general, the transformation is easier with a single table.

SELECT Employee.*
FROM Employee
WHERE UpperLastName = 'CAILLOUET'

becomes

SELECT Employee.*
FROM Employee
WHERE LastName = 'CAILLOUET'

The problem comes from the Employee.* which will return
your ugly upper case data. But everyone knows that the
beauty of relational databases is the independence of
program logic from the physical layout of the database -
adding a field to a table doesn't break applications
unless you use the asterisk wild card. Since no professional
programmer would ever use that in anything but a sample
program... Sorry. That's one of my crusades against
SQL....

Regards,

Ann
www.ibphoenix.com
We have answers.