Microsoft Dynamics NAV-SQL Challenge: The Curse of the Cursor

Consultants and users alike are discussing "issues with cursors in NAV on SQL" on the web, even several Microsoft/NAV teams blog about it. But what the heck is such a "cursor" thingy, and what's the problem with it?

Simplified, an SQL cursor is a temporary declared SQL object - similar to a variable - which is used to store a result-set (= multiple) of records. Such a "cursor" could be used to browse through the records, e.g. within a loop.

Keep in mind that our "goode olde" NAV was originally designed  for its own proprietary platform - the native C/SIDE Server. This "oldtimer" works on "Index Sequential Access Methods" (ISAM), and this is still shown in the NAV code processing:

1. Define a Key (SETCURRENTKEY);
2. Apply a Filter (SETRANGE & Co.);
3. Retrieve the Result-Set (FINDSET);
4. Loop through the records.

Yet, the C/AL programming language does not provide really "relational" commands, like JOIN or CROSS APPLY etc. with SQL Server, hence we're still stuck with the old algorithms (mostly).

Thus, to transfer the old ISAM algorithms to SQL site it's necessary to use such "cursors".

There are various types and variants of "cursors": static, dynamic, fast-forward, read only, for update, etc.. Each type has different abilities in dealing with the result-set (e.g. a read-only cursor cannot modify records) and consumption of system-resources, like CPU, RAM or "...