Tim has just pointed out to me that if I have a case-insensitive key, then
I MUST have a query that forces case-insensitivity.

ie the following query is NOT optimised.

SELECT id,filenumber FROM file
WHERE upper(filenumber)='96004AAA011'

nor is this

SELECT id,filenumber FROM file
WHERE filenumber='96004AAA011'


This one is...

SELECT id,filenumber FROM file
WHERE upper(filenumber)=upper('96004AAA011')

This does not really make sense to me, but now that I know the rules, I can
now optimise dozens of my queries that I just assumed were optimised. I just
thought I would pass along this tip as I am sure that many people would not
cotton onto this rather bizarre feature.

The reason it does not make sense to me is that, by definition, a case insensitvve
key should mean that it does not matter what the case of the key is, it is
treated the same. ie an index key of 'AbcD' should be the same as 'aBCD',
etc. ie, it isa case-insensitive!

So I should be able to write a query like this

SELECT id,filenumber FROM file
WHERE  filenumber='96004aaa011'

and it should be optimised.
and in addition, this following query should return the same results and
also be optimised

SELECT id,filenumber FROM file
WHERE  filenumber='96004AAA011'

"Kerry Neighbour" <kerry@dojitraders.com> wrote in message
news:639022ca21a58c93b3e226dee4a@elevatesoft.com...
> Tim has just pointed out to me that if I have a case-insensitive key, then
> I MUST have a query that forces case-insensitivity.
>
>
> The reason it does not make sense to me is that, by definition, a case
> insensitvve key should mean that it does not matter what the case of the
> key is, it is treated the same. ie an index key of 'AbcD' should be the
> same as 'aBCD', etc. ie, it isa case-insensitive!
> So I should be able to write a query like this
>
> SELECT id,filenumber FROM file
> WHERE  filenumber='96004aaa011'
>
> and it should be optimised.
> and in addition, this following query should return the same results and
> also be optimised
>
> SELECT id,filenumber FROM file
> WHERE  filenumber='96004AAA011'
>

There has been several discisions about this and Tim explained in detail why
this is. But in short : it's by the book (SQL book that is)

Also just ask yourself how <WHERE  filenumber='96004aaa011'> with
case-insensitive index can be executed case-sensitive ! Because that is wat
is writen in the sql and that is what I want

Kerry Neighbour wrote:
....
> This one is...
>
> SELECT id,filenumber FROM file
> WHERE upper(filenumber)=upper('96004AAA011')
>
> This does not really make sense to me,

It does make sense. Big time. It's not bizarre, it's logical. It does not
matter what indexes are defined, the result of a query has always be the
same (except maybe the order if there is no ORDER BY). So, just because
someone defined a case insensitive order, the engine should never give back
a different result set. Indexes are tools to get faster results, not
different results.

Ralf

Kerry,

<< This does not really make sense to me, but now that I know the rules, I
can now optimise dozens of my queries that I just assumed were optimised. I
just thought I would pass along this tip as I am sure that many people would
not cotton onto this rather bizarre feature. >>

See this thread for the "why" of the whole thing:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=5&msg=57627&page=1#msg57627

--
Tim Young
Elevate Software
www.elevatesoft.com