At 09:47 AM 3/29/00 -0700, you wrote:

>From: "David Berg" <DaveBerg@...>
>
>An alternative to declaring case insensitivity in the order by clause is to
>declare it as an attribute of the field. This has a number of advantages:
>
>(1) Indexes that include the field are case insensitive. This makes a BIG
>difference in performance when doing case insensitive sorts or queries.
>

Case insensitive indexes are obviously required [their implementation
is essentially trivial and has no performance implications].

>(2) Order by clauses are automatically case insensitive (and can use
>indexes).
>

A case insensitive equality plus a case insensitive index is an easy
case optimize. I'm not sure you always want a case insensitive ordering.
If this is true, then you need the modifier on ORDER anyway, and
having differing defaults for its absence on different fields could
be quite confusing. There is some merit to being able to look at
a select statement and know what it does...

>(3) Comparisons (<,>,=) are case insensitive (perhaps this should be an
>option).
>

We need some additional operators. Definitely.

>(4) Unique constraints are case insensitive. So I can't have "Interbase"
>and "INTERBASE" both in a unique index.
>

I'm not very comfortable with the combination of case insensitivity
and unique contraints. They seem a little unnatural. We could
invent some rules, but it strikes me as unwise.


The difficult case is when somebody wants to join a case
insensitive field in one table to a case sensitive field
in another (this would be a very common operation during
the transition period or during data importation). What
are the rules? If you say a comparison between case sensitive
and case insensitive values is case insensitive then the
system couldn't optimize the join with the case sensitive
index. If you reverse the rule, then all comparisons with
string constants (necessarily case sensitive) don't work.

A good rule: When caught between a rock and a hard place,
go somewhere else.

Jim Starkey