Here's a knowledge base article for someone--especially myself!

I have a field which acts as a psuedo identifier in a table (I
inherited the table structure or I would've done this differently).
It's a string called DocNum that usually has numbers, but can be
appended with a letter.

In order to make sure it really can be used as an identifier, I put a
unique constraint on the field using EDB Manager.  It automatically
created an index on the field with COLLATE ANSI_CI ASC.

Now it doesn't bother me too much that the automatically created index
is case insensitive.  However, it caused replication updates to the
table to operate much slower than expected because of a WHERE clause in
a trigger that assumed a case SENSITIVE index (see the "missing
updates" thread in the general group).  If I'd been a little smarter, I
would've caught that, but it made me wonder about what kind of control
there is on the automatically created index.

So I went back to EDB Manager, deleted the constraint, created an index
on DocNum with COLLATE ANSI (case sensitive) and then re-created the
constraint.  The case INsensitive index was re-created as well.

So, in typical programmer fashion, I read the manual *AFTER*
discovering this to learn that this is what is really supposed to
happen because the collation of the constraint is taken from the
underlying field definition.

What?  I have ANSI_CI on this DocNum field?  OK, so deleted the
constraint, deleted the extra index I had created, modified the table
to remove case sensitivity on the field, re-created the constraint, and
EUREKA! I now have my case-sensitive index.

I suppose it's not a terribly big deal whether this whole thing is case
sensitive or not--there's not that many records to affect the speed
based on that alone (34,000 records in one table, 49,000 in another).
But everything that deals with this field needs to be on the same
wavelength--or rather, case sensitivity.

It's definitely something to keep in mind!

--
David Cornelius
CorneliusConcepts.com