Keys

Quivelus wrote:

SETCURRENTKEY("Field1","Field2","Field3");SETRANGE("Field1",value1);SETRANGE("Field2");  // Does this line matter?SETRANGE("Field3",value3);The question is wether the line with range on Field2 does matter?

No, it doesn't. With the next FIND statement the query is sent and all "active" filters are combined to one WHERE clause, it should look like this:

SELECT * FROM MyRecord WHERE (Field1 = Value1) AND (Field3 = Value3) ORDER BY Field1,Field2,Field3

Quivelus wrote:

2) Supose you have a key defined consisting of Field1, Field2 and Field3 (in that order!). With this key you define a SQLIndex (and you maintain an SQLIndex) Field3, Field2 and Field1 (in this order). In code it is probably best to write SETCURRENTKEY("Field1","Field2","Field3");SETRANGE("Field1",value1);SETRANGE("Field2",value2);SETRANGE("Field3",value3);But is this automatically converted to the correct SQLIndex? Because the first field to 'filter' on there is Field3. How is this done by Navision?

First: there is nothing "converted". The problem is the general understanding of keys and indexes in SQL, which is totally different than with "native". The SETCURRENTKEY defines Index and Sorting in "native"; as this is an ISAM database it is crucial to filter in the sequence of these key/index fields.With SQL the SETCURRENTKEY only defines only the ORDER BY clause of a statement. The Index is picked by the SQL Server itself! (Except if you use some stupid Index Hinting, but that's a different story.)When choosing the right Index (= "an internal B-Tree structure to find records") SQL Server primarily regards the WHERE clause, secondly the sorting (ORDER BY).In your example the statement would look like this:

SELECT * FROM MyRecord WHERE (Field1 = Value1) AND (Field2 = Value2) AND (Field3 = Value3) ORDER BY Field1,Field2,Field3

The Index is built like this: Field3, Field2, Field1So, now trouble COULD arise: the index includes all fields from the WHERE clause which is good. But the sorting is quite the opposite way; thus SQL Server has to spent some work to re-sort the resultset after retrieving the records with the Index. Now it depends on the other available indexes and their selectivity - especially the Clustered Index - if SQL Server indeed will pick this Index, or another one - or starts scanning the full table (very bad).The higher the difference between Index and Sorting, the higher the probaility to cause Index Scans. That's why Index Hinting is stupid: You force SQL Server to use an Index which may not be the best one which could result in Index Scanning. Scanning takes longer than Seeking and brings higher potentials for blocking conflicts ...Hope this could help a little.Best regards,Jörg