How does the SETCURRENTKEY() C/AL function in Navision work?

Question

I have the following questions:

1. What does SETCURRENTKEY actually do?
2. What is the benefit of SETCURRENTKEY?
3. Why would I use SETCURRENTKEY?
4. When would I use SETCURRENTKEY?
5. What is the advantage of using an index and how do I tie this analogously to the example of an old sorting system of a library?
6. What type of database querying efficiency problems does this function solve?

I have been searching all over the internet and the 'IT Pro Developer Help' internal Navision documentation for this poorly documented function and I cannot find a right answer to my questions.

The only thing I know is that SETCURRENTKEY sets the current key for a record variable and it sorts the recordset based on it. When SETCURRENTKEY is used with only a few keys, it can improve query performance. I have no idea what is actually happening when a database uses an index versus not using an index.

Someone told me this is how SETCURRENTKEY works:

It is like the old sorting card system in a library: without SETCURRENTKEY you would have to go through each shelf and manually filter out for the book you want. You would find a mix of random books and you would have to say: "No, not this one. Yes, this one". With SETCURRENTKEY you can have an index analogous to the old system where you would just go to a book or music CD based on its 'Author' or 'Artist' etc.

That's all fine, but I still can't properly answer my questions.

Answer 1

1. With SETCURRENTKEY you declare the key (table index, which can consist of many fields) to be used when querying database with FINDSET/FINDFIRST/FINDLAST statements, and the order of records you will receive while iterating the recordset with NEXT statement.
2. Performance. The database server uses the selected key (table index) to retrieve the record set. You are always better off explicitly stating SETCURRENTKEY in your code, as it makes you think along about you database structure and indices required.
3. Performance, and so that you know ahead the order of records you will receive when iterating through a recordset.
4. When to use:

The typical use is this:

RecordVar.SETCURRENTKEY(...)
RecordVar.SETRANGE(Field, ...)
RecordVar.SETFILTER(Field, ...)
RecordVar.SETRANGE(Field, ...)
...
IF RecordVar.FINDSET THEN REPEAT
  // do something with records
UNTIL RecordVar.NEXT = 0;

SETCURRENTKEY is declarative, and comes into effect only when FINDSET is executed. At the moment FINDSET is executed, the database will be queried on the table represented by RecordVar, using the filters declared by SETRANGE/SETFILTER, and the key/index declared by SETCURRENTKEY.

For 5. and 6. and generally, I would truly reccomend you to familiarize yourself with basic database index theory. This is what it is, pretty well explained by yourself using the library/book analogy.