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.

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

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

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

This requires deciding case insensitivity up front, but if that can be done
the performance implications are substantial. In particular, consider
trying to do a case insensitive sub-select using a case sensitive index
(interbase,Interbase,INterbase,...iNterbase... they're all in different
parts of the index).

-----Original Message-----
From: Jim Starkey [mailto:jas@...]
Sent: Wednesday, March 29, 2000 9:22 AM
To: IB-Architect@onelist.com
Subject: [IB-Architect] Syntax for case insensitive sort


From: Jim Starkey <jas@...>

The current SQL "order by" clause is

{ <expr> | <position> } [ ASC | DESC ]

We could make it

{ <expr> | <position> } [ ASC | DESC ] [CASE SENSITIVE | CASE INSENSITIVE]

Obviously the default would "CASE SENSITIVE".

I'm not at all keen on two word "keywords", but CASEINSENSITIVE is too
ughly to consider. Just sensitive makes the whole things a little
too touchy feely for my taste, though INSENSITIVE is a pretty good
description for computer code. In any case (ho ho), once we open
the gates on collation options many more will try to follow, so perhaps
a common qualifier CASE is justified. That also leaves the unadorned
keyword SENSITIVE for the day when the database system knows to put
the poetically inclined first and fiftyish iconoclastic programmers
last.

Jim Starkey

------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954347030/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com