Roy,

> 2. Since you've got me worried I just tried
> "select * from contacts where _Surname COLLATE DAN = 'smith'"
> and I get a sensitive result set even though the column and index are declared as ANSI_CI

Yes, but the filter is certainly not optimized. Have you examined the
execution Plan of this query? I suspect it's using a row scan to filter.

Anyway, what I had in mind was ORDER BY clause and JOINs. If collations
don't match existing indexes the queries aren't optimized
result-sets/cursors aren't sensitive.

--
Fernando Dias
[Team Elevate]

Fernando

>Anyway, what I had in mind was ORDER BY clause and JOINs. If collations
>don't match existing indexes the queries aren't optimized
>result-sets/cursors aren't sensitive.

Ahh. Sorry, my telepathy course isn't till next week <vbg>

Roy Lambert

Roy,

>> Anyway, what I had in mind was ORDER BY clause and JOINs. If collations
>> don't match existing indexes the queries aren't optimized
>> result-sets/cursors aren't sensitive.
>
> Ahh. Sorry, my telepathy course isn't till next week <vbg>

Telepathy is completely useless; if my written English is poor, imagine
how confusing can be my thoughts in English

WHERE conditions can contain almost anything you want except correlated
sub-queries and still produce sensitive result sets provided that all
other conditions for sensitive result-sets are met. However the filter
can still be unoptimized (I mean, use row-scans).
I'm sorry if I wasn't completely clear before.

--
Fernando Dias
[Team Elevate]

Fernando

>Telepathy is completely useless; if my written English is poor, imagine
>how confusing can be my thoughts in English

Its a bloody sight better than my [fill in the language of your choice].

Roy Lambert

Fons,

<< With SELECT statements it can be a sensitive or an insensitive result
set. One of the differences between them two is that the latter uses a
temporay table and is therefore performance wise slower. Just like you
stated, but the question was related to keys. Reading the manual I couldn't
find anything about this regarding keys. The collation determines the
sensitivity and thus whether the query is optimized or not, but this doesn't
mean it uses a temp file. At least, the manual doesn't mention this at all.
>>

100% correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fernando,

<< Collation can influence whether it is a sensitive result-set/cursor or
not, and thus also optimisation,  because you can only get a sensitive
result-set/cursor if you specify a collation that does match an existing
index, otherwise you will always get an insensitive cursor and also
unoptimized queries. >>

Not quite correct.  The question of whether a query is optimized or not is
separate from whether a query generates a sensitive result set or not.  The
rules about sensitivity are here:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=17

--
Tim Young
Elevate Software
www.elevatesoft.com

James,

<< I am using GUIDs as my primary keys in tables. For performance reasons &
best/recommended practices with ElevateDB, does anyone know if they be case
sensitive or case insensitive? >>

Case-sensitive is faster when using the default collations: ANSI/UNI.  With
any other collations (Windows-based), it is actually faster to use
case-insensitive collations (at least according to Microsoft).

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Not quite correct.  The question of whether a query is optimized or
not is separate from whether a query generates a sensitive result set or
not.>>

Yes, you are right (obviously - I wrongly mixed the two when they
are independent things.

What I had in mind was that collation *can* influence if the result set
is sensitive or not (when used in ORDER BY) AND *can* also influence if
a query (or filter) is optimized or not, however one thing has nothing
to do with the other.

--
Fernando Dias
[Team Elevate]

Tim,

> 100% correct.

Yippie!

Roy got me worried though.

Fons

Tim & Fons


I'm going to take issue with the 100% rating:


"The collation determines the sensitivity and thus whether the query is optimized or not, but this doesn't mean it uses a temp file."

From the manual reference posted above by Tim the only rule that I can see that links sensitivity and collation is

5) There is no ORDER BY clause in the SELECT statement, or there is an ORDER BY clause that minimally matches the columns, and the collations defined for the columns, in an existing index in the source table.

So collation only has an impact on sensitivity when its involved in the ORDER BY statement. Plus I do not believe sensitivity and optimisation are linked in the way the statement implies. Its possible for a query to be insensitive and optimised or sensitive and unoptimised.


Roy Lambert